return same cell value from numerous sheets
 

I have 30 sheets. I would like to have a summary sheet that will return the
same cell value from each sheet. For instance, I want my summary sheet to
return the values for:

Sheet 1 C2
Sheet 2 C2
Sheet 3 C2

Thanks!
--
Laurie

return same cell value from numerous sheets
 

If there is no space in the worksheet's name, then this....

=Sheet2!C2

If there is a space in the name, then this...

='Sheet 3'!C2

Rick


"Laurie" wrote in message
...
I have 30 sheets. I would like to have a summary sheet that will return
the
same cell value from each sheet. For instance, I want my summary sheet to
return the values for:

Sheet 1 C2
Sheet 2 C2
Sheet 3 C2

Thanks!
--
Laurie

return same cell value from numerous sheets
 

Think you might try in the start cell:
=INDIRECT("'Sheet "&ROWS($1:1)&"'!C2")
Copy down 30 rows
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Laurie" wrote:
I have 30 sheets. I would like to have a summary sheet that will return the
same cell value from each sheet. For instance, I want my summary sheet to
return the values for:

Sheet 1 C2
Sheet 2 C2
Sheet 3 C2

Thanks!
--
Laurie

return same cell value from numerous sheets
 

Thanks Rick, but I need to be able to copy this formula. I know how to
create individual interal links. I would like for Excel to link the same
cell in each worksheet automatically on my summary sheet. I don't want to
type in the sheet name or link each one individually.

If I have sheet ABB, ALL, BAR, and I want to return the values from C2 on
each sheet, how can I do this automatically?
--
Laurie


"Rick Rothstein (MVP - VB)" wrote:

If there is no space in the worksheet's name, then this....

=Sheet2!C2

If there is a space in the name, then this...

='Sheet 3'!C2

Rick


"Laurie" wrote in message
...
I have 30 sheets. I would like to have a summary sheet that will return
the
same cell value from each sheet. For instance, I want my summary sheet to
return the values for:

Sheet 1 C2
Sheet 2 C2
Sheet 3 C2

Thanks!
--
Laurie

return same cell value from numerous sheets
 

List the sheet names in your summary sheet, eg in column A. then in
column B you can enter this:

=INDIRECT("'"&A1&"'!C2")

Copy this down to cover your sheet names - the C2 will remain constant
as it is within quotes.

Hope this helps.

Pete

On Apr 11, 1:47*pm, Laurie wrote:
Thanks Rick, but I need to be able to copy this formula. *I know how to
create individual interal links. *I would like for Excel to link the same
cell in each worksheet automatically on my summary sheet. *I don't want to
type in the sheet name or link each one individually.

If I have sheet ABB, ALL, BAR, and I want to return the values from C2 on
each sheet, how can I do this automatically?
--
Laurie

"Rick Rothstein (MVP - VB)" wrote:



If there is no space in the worksheet's name, then this....

* * *=Sheet2!C2

If there is a space in the name, then this...

* * *='Sheet 3'!C2

Rick

"Laurie" wrote in message
...
I have 30 sheets. *I would like to have a summary sheet that will return
the
same cell value from each sheet. *For instance, I want my summary sheet to
return the values for:

Sheet 1 C2
Sheet 2 C2
Sheet 3 C2

Thanks!
--
Laurie- Hide quoted text -

- Show quoted text -

return same cell value from numerous sheets
 

If I have sheet ABB, ALL, BAR, and I want to return the values from C2 on
each sheet, how can I do this automatically?

List the sheetnames in say, A2 down
Then you could use this in B2:
=INDIRECT("'"&A2&"'!C2")
Copy B2 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---