Try INDIRECT

Put the cell ref in C1: D10

With B2:B51 containing the sheetnames,

Put in C2: =INDIRECT("'"&$B2&"'!"&C$1)

Copy C2 down to C51

If you want to extend the set up to cover other cell refs besides "D10" in
C1, simply input the desired cell refs in D1, E1, F1 etc, select C2:C51 and
fill across to F51, etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Pester" wrote in message
...
I am trying to write a formula where I do a lookup of anything ("same cell
ie
D10) in every sheet. I want to have the NAME of the 50 sheets in column B,
from B2 to B51. And in column C, create a formula for each respective cell
where I look for the value of D10 from that sheet where I do the lookup.
So
create one formula in C2, and drag down to C51.

So if in Cell B3 I have the name January, and in B4 February.....in cell
C3,
create formula that looks the name of the sheet written in cell B3, and
then
goes a looks for the value of ie cell D10 into the sheet called January.
And
in the Cell C4 the formula looks for the value of cell D10 in the sheet
name
from cel B4, so goes to February. etc etc