worksheet references

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of worksheets names in a column and I want to reference those worksheets but by using the contents of the cells that contain the worksheet names.

Ex:
The value of A2 = Sheet1
I want the value of A3 to be 'Sheet1'!A3, but by referencing cell A2. In essence, I want A2 to 'point' to Sheet1.

Any thoughts?
Luke
 
Hi Luke!

Try:
=INDIRECT("'"&A2&"'!A3")

Note the punctuation carefully

It's " ' " [without the spaces]
And " ' ! [without the spaces]

You could get away with:
=INDIRECT(A2&"!A3")

But it will all end in tears and a #REF! when someone changes the
sheetname in A2 and gives a name 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.
Luke said:
I have a list of worksheets names in a column and I want to
Click to expand...
reference those worksheets but by using the contents of the cells that
contain the worksheet names.
Ex:
The value of A2 = Sheet1
I want the value of A3 to be 'Sheet1'!A3, but by referencing cell
Click to expand...
A2. In essence, I want A2 to 'point' to Sheet1.
 
=INDIRECT("'"&A2&"'!A3")

HTH
Jason
Atlanta, GA
-----Original Message-----
I have a list of worksheets names in a column and I want
Click to expand...
to reference those worksheets but by using the contents of
the cells that contain the worksheet names.
Ex:
The value of A2 = Sheet1
I want the value of A3 to be 'Sheet1'!A3, but by
Click to expand...
referencing cell A2. In essence, I want A2 to 'point' to
Sheet1.
 
Back
Top