Sequence and indirect formula

moossienl

New Member
Joined
Jan 10, 2010
Messages
4
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
This all means a lot of volatility, thus longer re-calcs...

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

Forum statistics

Threads
1,082,632
Messages
5,366,674
Members
400,912
Latest member
Click22

Some videos you may like

This Week's Hot Topics

Top