Use text in a cell to reference a sheet

[noahisaac]

Hi -

I'm trying to reference the text of a cell to reference another sheet.
Example:

D3 is a text cell that has contents of "Sheet4"

Is it possible to use the text in that cell to reference another sheet
Example:

=D3!A1

(So the end result would be equivalent to "=Sheet4!A1" without havin
the sheet explicitly stated)

Of course, the example above does not work, so I tried various ways o
dereferencing the D3 cell to make it work - putting it in quotes
putting it in parentheses, using CELL(contents,D3), etc. I couln'
seem to come up with anything that would properly dereference it. I'
probably missing something really obvious. Any ideas?

TIA,
Noa

 

[Norman Harker]

Hi Noah!

Use:

=INDIRECT("'"&D3&"'!A1")

Note the punctuation carefully:

First quotes is " ' " (no spaces)
Second quotes is " ' !A1" (no spaces)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Gord Dibben]

Noah

Use the INDIRECT worksheet function

=INDIRECT(D3 & "!A1")

Gord Dibben Excel MVP

 

[Norman Harker]

Hi Gord!

Re: =INDIRECT(D3 & "!A1")

This will be OK with OPs example but will #REF if the sheet named in
D3 has spaces in it.

Murphy's Law demands that every will go OK for weeks until someone
wants to refer to a sheet with a space in it.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Gord Dibben]

Norman

Point taken.

I forget about allowing for spaces in names. I never use them. Prefer to slap
an underscore in when needed.

Figure "everybody" knows about them but in these groups we should strive to
accommodate the LCD.

Thanks

 

[Norman Harker]

Hi Gord!

Agreed. Also MyIncomeSheet is easy enough to read if the VB hasn't
flowed too much.

And look how difficult it is to decipher:

=INDIRECT("'"&D3&"'!A1")

Is it " ' ", ' " ", " " ' And is it " ' or ' "

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.