Excel - adding Stocks to multiple worksheets

Hi Guys. I have a s/sheet with multiple worksheets, with the 'Summary' containing a list of all stocks (vertically in Columns) grouped by industry . I have other worksheets tracking data about the stock (horizontally in Rows). However, I'd like to dynamically be able to Add a new stock to the Summary sheet and it to automatically add to the other worksheets.

I've thought about using Ranges etc but having no luck. Also, I may try my luck at FORMS but this would require considerable effort.

Thanks in advance. Here's a brief example ;

 

Not sure if this is what you were looking for. Hope it helps.

 

Thanks for your reply. Unfortunately that doesnt work - well, it does, but its purely referencing the 'Summary' sheet.

Ideally, I'd like some form of Vb code so that if I inserted a new stock on 'Summary' it automatically updates in the other two worksheets? And its not restricted to the vertical/horizontal basis - I will change so that all s/sheets have the stocks listed vertically in Columns.

Thanks again.

 

Here's some "range" code that should help...

Sub RowRange()

Dim r As Range
Dim c As Range

Set r = ThisWorkbook.Sheets("November").Rows(3)

For Each c In r.Cells
If WorksheetFunction.IsText(c.Value) Then
MsgBox "Location " & c.Address(False, False) & " contains... " & c.Value
End If
Next c

End Sub

The caveat is adding or deleting range items will negate the range variable. In these cases, the range variable must be re-inited to avoid undefined behavior. However, this does not apply if you merely change the contents of range items.

Good luck,
Osorico

 

Probably a stupid question, but why not try a database?
A relational database can do this easily.

 

Can a relational database be updated through DDE?

 

Yes, DDE works also in Access according to Microsoft. But i never tried it out.

 

Yes - thought about that. However, its a very informal s/sheet and if anything, the numbers running off the back of the tickers are the main priority - ie, stats/charts etc.

Thanks.

 

Doesnt sound all that hard to do.

After 2 minutes of brainstorming, i suggest this.

Either programatically, or by manually executing a VBA routine, scan the entire summary sheet, look in your other pages as each symbol is scanned for the existance of that symbol.

If not found, then add it.


I'll code it for you for $500.