Indirect formula

[123rickfear]

Hi all,

I am currently using the formula =INDIRECT($C$2&"!A1") which works fine.

what I now need is for the box below to be =INDIRECT($C$2&"!A2") and so on, down until A330

is this possible without manually changing the A1 to A2, A3, A4 and so on?

Thanks

Rick

 

[FormR]

Re: Indirect formual help

Hi, in which cell are placing the first formula?

 

[123rickfear]

Re: Indirect formual help

solved using helper column and formula is now INDIRECT($C$2&"!A"&N6)

 

[FormR]

Re: Indirect formual help

solved using helper column

Hi, you don't specifically need a helper column, you can increment the row counter like this:

=INDIRECT($C$2&"!A"&ROWS(A$1:A1))

But for robustness it would be wise to make the A$1:A1 references match the cell where you first enter the formula.