Dynamic cell reference
 

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

Dynamic cell reference
 

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

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.



"Ant" wrote in message
...
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