JMB have tried your suggestion, however the sheet names (and the list in
sheet one) have spaces and commas EG 'Green, Fred Peter' and it appears not
to like either. When I changed the sheet names and list to simple names like
'fred' you suggestion works great. Do you know if you macro suggestion will
overcome this issue?
--
Thanks
Phil


"JMB" wrote:

You could try

=INDIRECT(A11&"!A1")
which would return cell A1 from whatever sheet name appears in cell A11.
The down side is if your target cell (A1 on the "Green" worksheet, for
example) moves (cut/paste or additional lines are inserted/deleted on the
"Green" worksheet), the indirect formula does not change to reference the new
cell location - it will always refer to cell A1. Also, I believe it is a
volatile function - which can adversely affect recalculation speed if you
have a lot of them.


Or, you could use a macro to build the links. Say Sheet1 A11:A50 contains
the worksheet names and you want B11:B50 linked to cell A1 of the respective
worksheets identified in A11:A50,

Sub Test()
Dim rngCell As Range

For Each rngCell In Sheets("Sheet1").Range("B11:B50")
rngCell.Formula = "=" & rngCell(1, 0).Value & "!$A$1"
Next rngCell

End Sub


"Phil" wrote:

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