Reference cells from different worksheets with worksheet name in a cell

[davidr]

I am trying to create an Excel formula similar to "=Sheet3!B4" but would like the worksheet to be an absolute cell reference and the cell reference to be a relative reference.
I have tried "=INDIRECT($A$1&"!"&"B4")" where A1 contains "Sheet3" but when copied to the right one cell I still reference cell B4 on Sheet3 instead of C4 on Sheet3.
Can anyone help?
TIA

[Rutgers_Excels]

Try this...


=INDIRECT($A$1&ADDRESS(ROWS($E$5:G5),COLUMNS($E$5:G5)))

This will allow your range to change when you copy accross or down.