Biff...you legend...worked a treat.
Thanks
--
Thanks
Phil


"Biff" wrote:

Green, Fred Peter

That's the actual sheet name?

Try it like this:

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

Biff

"Phil" wrote in message
...
Ragdyer
Thanks, your assumptions and suggestions worked fine, however my sheets
(and
the list in sheet one) have names with spaces and commas eg 'Green, Fred
Peter'. These spaces and commas seem to cause a problem and then the
INDIRECT
function does not work. Any thoughts?
--
Thanks
Phil


"Ragdyer" wrote:

Assume that you're looking to return the *same* cell from all your
sheets,
say D1.
Assume your sheet names are in ColumnA, from A1 down.

Try this in B1, and copy down as needed:

=INDIRECT(A1&"!D1")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phil" wrote in message
...
Hi, I would like to have data returned to sheet one from a cell in
subsequent
worksheets by comparing a cell in sheet one to the name of the
worksheets
(they match exactly). This will validate that the data has come from
the
correct worksheet to the corresponding cell in sheet one. The list in
sheet
one is extensive and hence extensive amount of worksheets, so being
able
to
copy the formula down the column in sheet one will save me time.
EG listing in sheet one is Black, Brown, Green and there are sheets
called
Black, Brown and Green (listing and worksheet names are in the same
order). I
want to compare the list in sheet one to the name of the sheets and
returna
value from a cell in those sheets to a cell in sheet one.
Can anyone help?
--
Thanks
Phil