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