How do I use the text content of a cell as a worksheet reference?

K

KLE

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?
 
G

Gary''s Student

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.
 
J

JLatham

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("filename",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.
 
I

Izo

JLatham said:
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("filename",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 said:
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.
Click to expand...
Click to expand...

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
 
J

JLatham

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("filename",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 said:
JLatham said:
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("filename",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 said:
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


:

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?
Click to expand...
Click to expand...

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
Click to expand...
 
I

Izo

JLatham said:
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("filename",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.
Click to expand...


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
 
J

JLatham

Question is what is in A37?
If it is a formula, need the formula and its result (what is displayed in
the cell).

When I said "if a cell range is appropriate" I meant at times such as within
a VLOOKUP() formula. As you've used it, I would expect an error: you're
trying to reference several cells within just a single cell and that won't
work.

Izo said:
JLatham said:
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("filename",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.
Click to expand...


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
Click to expand...
 
I

Izo

Hi,

A37 contains the text "33" which is the name of the worksheet I'm trying to
reference. So =INDIRECT(A37 & "!A5") is getting A5 from worksheet 33. I'd
like to be able to get a range in worksheet 33 and display it in my cell, if
that's even possible.

Thanks for your help,
Izo
 
J

JLatham

Sorry, you cannot display the values from multiple cells within a single
cell. It just doesn't work that way.

Test it yourself, instead of using the INDIRECT() in a cell, just (for
testing) enter the formula normally as:
='33'!A5
and then try something like
='33'!A5:A7
Second attempt will not give you the results you expect.

But =SUM(33!A5:A7) will work fine, since that's a legitimate place to use a
range reference.

You really need the entire indirect in one cell. A37 probably should look
more like
="Sheet1" & "!A5") so you can easily change the sheet name, or even
="Sheet1" & "!A5:A7" for special cases where a range is permitted, as in:
=sum(Indirect(A37))
would be the same as
=SUM('Sheet1'!A5:A7) which is entirely ok to use, but just plain
=Sheet1!A5:A7 is not going to give you the answer you want.
 
I

Izo

Hi,

Thanks very much, I think that will be ok. I will try some of the other
tricks you suggested a swell.

Kind regards,
Izo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I need a cell to be blank. 4
linking cells when embedding into Word documents 0
VBA Coding Help for Beginner 0
HyperLinkDumbIam 2
Formula for advance worksheet name to next worksheet name 2
Cell contents to refrenence cells 0
DBSUM function but with function as criterion, not a range 3
#REF! Error 5

Top