Dragging Indirect Formula Down

[c0087]

In cell M3 i have the formula =INDIRECT("C"&M1) M1=the value 3, so my formula is basically calling for the value that is in cell C3.

How would I edit the formula, so that when I drag down, it will give me the values that are in cells C4, C5, etc.

 

[GuzmanServices]

It stands to reason that you would need to populate Cells M2, M3, etc. with sequential values (4, 5, etc.). Alternatively, you could just change the original INDIRECT formula to =INDIRECT(C3) and Copy it down.

 

[awoohaw]

I would not point to the value in M1. I would use a row counter, or if you know the number of rows the SEQUENCE function.

=INDIRECT("C"&(ROW(M3)))

or

=INDIRECT("C" & SEQUENCE(1000,1,3,1))

 

[Fluff]

How about

=INDEX(C:C,M$1+ROWS(M$3:M3)-1)

 

[awoohaw]

NOTE: If you are going to have lots of other calculations based on this INDIRECT function it will cause your worksheet to slow down more than normal.