Using Cell Contents as a worksheet reference

[Hollywoood]

Hi all,

I know that if I want to reference another worksheet I use syntax such as... ='Sheet1'!J7

What Id like to do is simply reference a cell to obtain the worksheet name, in this case "Sheet1".

Lets assume I have the alternate worksheet (same workbook) name, same as the example, "Sheet1", in cell A1, I cant simply use "='A1'!J7 to get the same result as ='Sheet1'!J7

Is there anyway to achieve this result?

Thanks in Advance!

 

[HalfAce]

You want to use the Indirect function for that.
In the cell you want to return the value of J7, (from whatever sheet is named in cell A1) you can use this:

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

Hope it helps.

 

[CWatts]

You can use INDIRECT but a lot of these will slow down your workbook.

For example, if cell A2 had a value of: Sheet1

=INDIRECT("'"&A2&"'!A4")

would be the same effect as ='Sheet1'!A4

 

[Hollywoood]

Thanks guys, that works. i'll have to debate whether any reduction in performance warrants the extra bit of ease.