Dynamic cell reference

A

Ant

I have the following formula:

='Sheet1'!A1

Is it possible to enhance the formula so that the tab name( Sheet1) becomes
dynamic? ie. Can I link the name 'Sheet1' to a cell which changes? eg If the
formula in say, A2 returns various sheet names, can I enhance the formula to
reflect this? So, if A2 shows Sheet2, can my formula above reflect this?
Something like:

='A2'!A1

which is actually now:

='Sheet2'!A1
 
Z

Zack Barresse

Hi Ant,

Yes, check out the INDIRECT function. Ex:

=INDIRECT"'"&A2&"'!A1")

Note that the cell reference is hardcoded, but the sheet will change. The '
signs are included if you have any spaces in your sheet name (else an error
will arise).

HTH
 
Top