Excel - adding Stocks to multiple worksheets

S

sargie

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 ;
 

Attachments

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
 
Quote from sargie:

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 ;
More...

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?

Quote from spike500:

Probably a stupid question, but why not try a database?
A relational database can do this easily.
More...
 
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.
 
You must log in or register to reply here.
Back
Top