Update Summary Sheet Automatically

[MarkT]

Hello all,

I am running Excel 2007. I have created a stock tracker spreadsheet that
has a number of sheets containing specific data on each individual stock. I
would like to have a summary sheet that takes certain data from these
individual sheets and summarizes them. Data that I would like to summarize
is name (E2), Symbol (K2), Buy (E7), Buy Date (E20), Shares (I20), Sell (S7)
Sell Date (W7) Result Price (S26) and Result Trade (W26). These individual
sheets are all identical and the same data is located in the above cells on
each individual sheet.

Is there a way that once either data is entered into these cells, or a
calculation is made into one of these cells that the same info is
automatically updated on the summary page?

The number of these individual sheets will vary as stocks are bought and
sold so the number of entries on the summary page will vary by the number of
sheets contained in the workbook.

I hope I have provided the needed information.

Thank you very much for your anticipated help in my dilemma!

 

[Don Guillett]

One way is to use INDIRECT such as shown from one of mine looking up a
symbol from Yahoo finance where the data sheet info is wiped out with each
web fetch.
=IF(ISNUMBER(D5),VLOOKUP($A5,INDIRECT("Data!B1:X1000"),5,FALSE),"")

 

[MarkT]

Hi Don,

The formula that you provided references two cells on the same sheet, are
you suggesting that I adjust the formula to reference the items on the other
sheets where the data is? If so, this solution would not work since the
number of sheets will vary as I buy and sell stocks to track.

I am trying to create a summary sheet, a one-sheet listing of various data
that comes from other individual stock sheets within the same workbook. The
number of sheets will change as stocks are sold. This spreadsheet will only
track stocks that I have sold, not tracking my current holdings, only past
holdings showing the performance stats.

Hope this info helps....

Thanks again for your help!

Mark

 

[Don Guillett]

if you have the sheet name in a2 and you want to get cell b2 from that
sheet, put this in b2. Change your list as desired or have a macro that
makes the list for you with one mouse click

=INDIRECT("'"&a2&"'!"&ADDRESS(2,ROW(A2)))

 

[MarkT]

What would the macro look like to create the list?

It appears that your formula is working, even though I am confused by it
(the row(a5) being used), regardless it seems to be doing the trick. If I
could get the list of sheets placed in column a, then I think this will solve
my problem.

Thanks again Don.

 

[Don Guillett]

Sub listsheets()'where 1 is the summary sheet
For i = 2 To Sheets.Count
Sheets("Summary").Cells(i, 1) = Sheets(i).Name
Next i
End Sub

 

[MarkT]

Don, thanks for your help; I do believe, with a few tweaks here and there,
that this will solve my problem!

Thanks again,

Mark

 

[Don Guillett]

Glad to help. Let me know if you need more assistance.