Looking up a sheet name

[mrengineer]

I'm trying to find a function that will look at a cell and find the worksheet
that has the same name as the text in that cell and then report back a cell
from that sheet ie. if cell A1 is text "worksheet 3" I want the value in cell
B1 to be 'worksheet 3'!$D$12 but if the text is "worksheet 7" I want the
value in cell B1 to be 'worksheet 7'!$D$12 I then want to be able to copy
this formula down a list of 30 or 40 cells. Does a function exist to do this
or is it a complicted nesting situation.

Thanks for the help
mrengineer

 

[N Harkawat]

=INDIRECT(A1&"!b1")
where A1 holds the sheet name and b1 is the cell that you want the data from

 

[Bob Phillips]

=INDIRECT("'"&B1&"'!$D$12")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

One way

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

make A1 absolute if you always want to refer to A1


=INDIRECT("'"&$A$1&"'!D12")

if you want D12 to change to D13, D14 etc when copied down you would need


=INDIRECT("'"&A1&"'!"&CELL("address",D12))