Issue

This Content is from Stack Overflow. Question asked by Matt

this should be super basic but I can’t figure it out.

If I have a series of numbers in A1:A10. And in B1 i have [=A1], B2 [=A2] etc. Simple cell reference.

My problem is that when I select A1:A10 and move them down to A2:A11, the formulas in column B update to reflect the new location. What I want is the reference to be permanently on A1:A10.

I’ve tied =$A$1, but that too updates to $A$2 when I shift the cells.

How do I break the lock on the values?

Solution

It is possible to achieve this using the INDIRECT formula, like this:

B1: =INDIRECT("A1")
B2: =INDIRECT("A2")
...

The references are not updated because they are strings.

This Question was asked in StackOverflow by Matt and Answered by eventHandler It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.