return same cell value from numerous sheets

  • Thread starter Thread starter Laurie
  • Start date Start date
L

Laurie

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!
 
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
 
Think you might try in the start cell:
=INDIRECT("'Sheet "&ROWS($1:1)&"'!C2")
Copy down 30 rows
 
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?
 
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
 
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?
Click to expand...

List the sheetnames in say, A2 down
Then you could use this in B2:
=INDIRECT("'"&A2&"'!C2")
Copy B2 down

---
 
Back
Top