Sequence and indirect formula

[moossienl]

Hello,

I have a problem creating a sequence of an =INDIRECT formula.

Sheet2:

Formula in C2:

=INDIRECT("'"&$C$1&"'!A1") in "Sheet 2"

C1: name of sheet1

Sheet1:

Data in A1, A2 etc.

Problem:

I want to create a sequence of the formula in C2, A1 must change to A2, A3 etc

Like this

=INDIRECT("'"&$C$1&"'!A2")
=INDIRECT("'"&$C$1&"'!A3")

Using automatic sequences by dragging the cells dont give this result

My problem is that I have to make more than 1000 of this indirect formulas

So it will take a lot of time changing the A1 to A2 etc)

I searched on internet and this forum for a way to solve this. But did not find anything.

The last number has to be a variable, is there a way to indicate this, just like the $ is for the opposite?

Thx

moossienl

 

[VoG]

Try

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

 

[Aladin Akyurek]

This all means a lot of volatility, thus longer re-calcs...

=INDIRECT("'"&$C$1&"'!"&CELL("address",A1))

 

[moossienl]

Thx! it works