Use text in a cell to reference a sheet

  • Thread starter Thread starter noahisaac
  • Start date Start date
N

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

Use the INDIRECT worksheet function

=INDIRECT(D3 & "!A1")

Gord Dibben Excel MVP
 
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.
 
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
 
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.
 
Back
Top