Excel copy down changing sheet number

[Guest]

Hi, i need to be able to copy down a formula that changes the sheet number
but not the cell reference. How can I do this?
E.g.
=Sheet2!A2
=Sheet3!A2
=Sheet4!A2

I thought about concatenating this together but don't know how to change to
a formula. Any other way of doing this?

If there is no way to copy surely there is a way to change a concatenation
into a formula -
="Sheet"&row()&"!A2"
but then what?

 

[Guest]

You've almost got it. Just enclose your formula inside the INDIRECT()
function.

=INDIRECT("Sheet"&row()&"!A2")

HTH,
Elkar