Incrementing rows with Indirect function

[FryGirl]

I have the following formula =DataSheet!A2, but need to change the sheet name based on a sheet name input in A1 of same sheet.

If I use this =INDIRECT("'" & $A$1 & "'!A2") it works fine for the first cell, but when I drag it down the reference remains A2.

Can somebody tell me what I am missing here?

 

[JoeMo]

Try:

=INDIRECT("'"&$A$1&"'!A"&ROW((A2)))

 

[T. Valko]

I have the following formula =DataSheet!A2, but need to change the sheet name based on a sheet name input in A1 of same sheet.

If I use this =INDIRECT("'" & $A$1 & "'!A2") it works fine for the first cell, but when I drag it down the reference remains A2.

Can somebody tell me what I am missing here?

One way...

=INDIRECT("'"&$A$1&"'!A"&ROWS(A$1:A2))

 

[FryGirl]

Thank you very much Biff & Joe. I see now where I was going wrong. I tried the Row/s method but I was failing on the syntax. Thanks again.

 

[T. Valko]

Thank you very much Biff & Joe. I see now where I was going wrong. I tried the Row/s method but I was failing on the syntax. Thanks again.

You're welcome. Thanks for the feedback!