Function across multiple worksheet
 

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

Function across multiple worksheet
 

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

Function across multiple worksheet
 

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

Function across multiple worksheet
 

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

Function across multiple worksheet
 

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

Function across multiple worksheet
 

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

Function across multiple worksheet
 

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

Function across multiple worksheet
 

You're welcome. Thanks for the feedback!

Biff

"Phil" wrote in message
...
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