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!

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:X10 00"),5,FALSE),"")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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!

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" wrote:

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:X10 00"),5,FALSE),"")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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!

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)))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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" wrote:

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:X10 00"),5,FALSE),"")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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!

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" wrote:

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)))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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" wrote:

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:X10 00"),5,FALSE),"")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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!

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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" wrote:

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)))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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" wrote:

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:X10 00"),5,FALSE),"")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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, 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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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" wrote:

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)))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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" wrote:

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:X10 00"),5,FALSE),"")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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!

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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" wrote:

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)))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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" wrote:

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:X10 00"),5,FALSE),"")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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!