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