Excel 2003
I need to reference the value of a cell in a different worksheet and I want
the name of the referenced worksheet to be determined by the text content of
another cell. How do I format the worksheet reference to use the contents of
the other cell as the worksheet name?

For example, on Worksheet1 cell A1 contains the name of the worksheet I want
to reference, which is Worksheet2. The cell I want to reference in
Worksheet2 is B2. If I entered the worksheet name directly I would use
'Worksheet2'!B2. But what do I use in place of 'Worksheet2'! that is a
reference to the contents of cell A1?

If A1 contains:
Sheet3
then the formula:

=INDIRECT(A1 & "!A1")

will product the same result as :
=Sheet3!A1

This means you can change the sheet reference by changing a cell rather than
changing the formula.
--
Gary''s Student - gsnu200845


"KLE" wrote:

Excel 2003
I need to reference the value of a cell in a different worksheet and I want
the name of the referenced worksheet to be determined by the text content of
another cell. How do I format the worksheet reference to use the contents of
the other cell as the worksheet name?

For example, on Worksheet1 cell A1 contains the name of the worksheet I want
to reference, which is Worksheet2. The cell I want to reference in
Worksheet2 is B2. If I entered the worksheet name directly I would use
'Worksheet2'!B2. But what do I use in place of 'Worksheet2'! that is a
reference to the contents of cell A1?

And just in case someone changes the name of Sheet3, you could put this in A1
where "Sheet3" had been before (as in Gary''s Student's solution):

=RIGHT(CELL("filename",Sheet3!A1),LEN(CELL("filena me",Sheet3!A1))-FIND("]",CELL("filename",Sheet3!A1)))

Initially you need the current name of the other sheet in this formula, but
if someone changes the name of that sheet, it will automatically be changed
in this formula, and so the cell will always display the current name of the
worksheet.

NOTE: it will not work until the file has been saved to disk.


"Gary''s Student" wrote:

If A1 contains:
Sheet3
then the formula:

=INDIRECT(A1 & "!A1")

will product the same result as :
=Sheet3!A1

This means you can change the sheet reference by changing a cell rather than
changing the formula.
--
Gary''s Student - gsnu200845


"KLE" wrote:

Excel 2003
I need to reference the value of a cell in a different worksheet and I want
the name of the referenced worksheet to be determined by the text content of
another cell. How do I format the worksheet reference to use the contents of
the other cell as the worksheet name?

For example, on Worksheet1 cell A1 contains the name of the worksheet I want
to reference, which is Worksheet2. The cell I want to reference in
Worksheet2 is B2. If I entered the worksheet name directly I would use
'Worksheet2'!B2. But what do I use in place of 'Worksheet2'! that is a
reference to the contents of cell A1?

"JLatham" wrote:

And just in case someone changes the name of Sheet3, you could put this in A1
where "Sheet3" had been before (as in Gary''s Student's solution):

=RIGHT(CELL("filename",Sheet3!A1),LEN(CELL("filena me",Sheet3!A1))-FIND("]",CELL("filename",Sheet3!A1)))

Initially you need the current name of the other sheet in this formula, but
if someone changes the name of that sheet, it will automatically be changed
in this formula, and so the cell will always display the current name of the
worksheet.

NOTE: it will not work until the file has been saved to disk.


"Gary''s Student" wrote:

If A1 contains:
Sheet3
then the formula:

=INDIRECT(A1 & "!A1")

will product the same result as :
=Sheet3!A1

This means you can change the sheet reference by changing a cell rather than
changing the formula.
--
Gary''s Student - gsnu200845


"KLE" wrote:

Excel 2003
I need to reference the value of a cell in a different worksheet and I want
the name of the referenced worksheet to be determined by the text content of
another cell. How do I format the worksheet reference to use the contents of
the other cell as the worksheet name?

For example, on Worksheet1 cell A1 contains the name of the worksheet I want
to reference, which is Worksheet2. The cell I want to reference in
Worksheet2 is B2. If I entered the worksheet name directly I would use
'Worksheet2'!B2. But what do I use in place of 'Worksheet2'! that is a
reference to the contents of cell A1?

Hi,

Is it possible to do this for a range of cells? I can't quite work out the
syntax, although it works for one cell. Even with a named Range I can't get
that to work. Any ideas?

Regards,
Izo

Is which/what possible for a range of cells? To use indirect? Or to get the
'dynamic' name of a worksheet?

The
RIGHT(CELL("filename",Sheet3!A1),LEN(CELL("filenam e",Sheet3!A1))-FIND("]",CELL("filename",Sheet3!A1)))
function that I gave you uses the CELL() function with the parameter
"filename" which tells Excel to provide the full name of the worksheet and it
includes everything in the path from the drive letter through all folders to
the filename and finally the worksheet name. The filename is enclosed within
[ ] and is immediately followed by the worksheet name of the cell you
specified. The actual returned value might look like
C:\Users\JLatham\Documents\[Analysis of jobs 10-31-08.xls]Sheet1
The RIGHT() part of it looks for the closing ] bracket and pulls off the
remainder of what it returned, which is the sheet name.

Now, it may seem dumb to ask for the name of the sheet since initially it is
part of the formula you entered, but the object here is to keep up with the
sheet name in case someone changes it, and this will do that - Excel will
automatically "change" the result if the sheet name is changed.

As far as the portion of Gary''s Student's formula that had the cell
reference in it: "!A1", if a cell range is appropriate where you're using
the reference then it should work just fine, such as in referencing a table
that might extend from A1 to D22, it could be "!A1:D22" or if you need
absolute references, then "!$A$1:$D$22"

Hope this helps.

"Izo" wrote:



"JLatham" wrote:

And just in case someone changes the name of Sheet3, you could put this in A1
where "Sheet3" had been before (as in Gary''s Student's solution):

=RIGHT(CELL("filename",Sheet3!A1),LEN(CELL("filena me",Sheet3!A1))-FIND("]",CELL("filename",Sheet3!A1)))

Initially you need the current name of the other sheet in this formula, but
if someone changes the name of that sheet, it will automatically be changed
in this formula, and so the cell will always display the current name of the
worksheet.

NOTE: it will not work until the file has been saved to disk.


"Gary''s Student" wrote:

If A1 contains:
Sheet3
then the formula:

=INDIRECT(A1 & "!A1")

will product the same result as :
=Sheet3!A1

This means you can change the sheet reference by changing a cell rather than
changing the formula.
--
Gary''s Student - gsnu200845


"KLE" wrote:

Excel 2003
I need to reference the value of a cell in a different worksheet and I want
the name of the referenced worksheet to be determined by the text content of
another cell. How do I format the worksheet reference to use the contents of
the other cell as the worksheet name?

For example, on Worksheet1 cell A1 contains the name of the worksheet I want
to reference, which is Worksheet2. The cell I want to reference in
Worksheet2 is B2. If I entered the worksheet name directly I would use
'Worksheet2'!B2. But what do I use in place of 'Worksheet2'! that is a
reference to the contents of cell A1?

Hi,

Is it possible to do this for a range of cells? I can't quite work out the
syntax, although it works for one cell. Even with a named Range I can't get
that to work. Any ideas?

Regards,
Izo

"JLatham" wrote:

Is which/what possible for a range of cells? To use indirect? Or to get the
'dynamic' name of a worksheet?

The
RIGHT(CELL("filename",Sheet3!A1),LEN(CELL("filenam e",Sheet3!A1))-FIND("]",CELL("filename",Sheet3!A1)))
function that I gave you uses the CELL() function with the parameter
"filename" which tells Excel to provide the full name of the worksheet and it
includes everything in the path from the drive letter through all folders to
the filename and finally the worksheet name. The filename is enclosed within
[ ] and is immediately followed by the worksheet name of the cell you
specified. The actual returned value might look like
C:\Users\JLatham\Documents\[Analysis of jobs 10-31-08.xls]Sheet1
The RIGHT() part of it looks for the closing ] bracket and pulls off the
remainder of what it returned, which is the sheet name.

Now, it may seem dumb to ask for the name of the sheet since initially it is
part of the formula you entered, but the object here is to keep up with the
sheet name in case someone changes it, and this will do that - Excel will
automatically "change" the result if the sheet name is changed.

As far as the portion of Gary''s Student's formula that had the cell
reference in it: "!A1", if a cell range is appropriate where you're using
the reference then it should work just fine, such as in referencing a table
that might extend from A1 to D22, it could be "!A1:D22" or if you need
absolute references, then "!$A$1:$D$22"

Hope this helps.



Hi,

Thanks for that. Yes, I was basically asking how to add ranges to that
INDIRECT statement basically. So if I do =INDIRECT(A37 & "!A5") that works,
but if I do =INDIRECT(A37 & "!A5:D22") I get a #VALUE! error and
=INDIRECT(A37 & "!$A$5:$D$22") gives me the same.

Regards,
Izo