Sheet names in a formula

  • Thread starter Thread starter Enda D
  • Start date Start date
E

Enda D

Hi,
I wonder can you help me.

I currently have several sheets in a workbook say called
A - Z.
I then have a summary sheet that I would like to be able
to see a certain cell value for each sheet.

For example I would like to see in one cell the sheet name
(say Sheet A) and in the cell next to it I would like to
see the value of Cell A2 from that sheet.

Summary Sheet consists of:

Sheet Total
A ='A'$A$2
B ='B'$A$2
C ='C'$A$2


Any ideas how to do this automatically instead of doing
each cell manually?

Thanks
 
Put your ABC's in column A, then Concatenate.........in cell B1 put

=A1&"!"&$A$2 and copy down............

Vaya con Dios,
Chuck, CABGx3
 
Hi Enda,

the function you need is INDIRECT(), take a look in the help files for more
info.

Assuming that your list of sheet names begins in A1:

=INDIRECT("'"&A1&"'!$A$2",1)


Steve D.
 
Summary Sheet consists of:
Sheet Total
A ='A'$A$2
B ='B'$A$2
C ='C'$A$2
Click to expand...

So A2:A? = sheet name as text ; in B2,

=INDIRECT(A2&"!A2")

& drag down.

HTH,
Andy
 
Back
Top