Fix cell references when user trags reference cell

[tt333]

Hi Folks

My issue seems easy, but I dont get it; trying to explain with a basic example:

Formula in Cell A1 = A2
Cell A2 is dragged to Address B2 by the user; ---> now Formula in Cell A1 = B2 , which I don't want in this case. It should still refer to A2

How can this be done?

Cheers, T

 

[JimM]

You could try using the offset function

=OFFSET(A1,1,0)

 

[tt333]

You could try using the offset function

=OFFSET(A1,1,0)

Thank you Jim for the input.

My issue is, however, that I don't know where the user will drag it to. So the 1 and 0 in your offset function example are unknown.

I could imagine a planb solution using address?, but that's not flexible, eg. a row is inserted, and all is wrong

 

[JimM]

Did you try it?

It doesn't matter where they drag A2 to as the offset works by starting in cell A1, then looks 1 row down and 0 columns across ie it always looks at A2

 

[Fluff]

Another option, that's not volatile

=INDEX(A:A,ROW()+1)