How do I make an Excel formula that never changes, regardless of what happens to any referenced cells?
Posted on 2015-01-05
I am well aware of the difference between absolute and relative references. I've stumbled on something I can't figure out how to make Excel do though.
Let us say I have a value of 10 in A2.
Let us say in A1, I type the formula: =$A$2
A1 and A2 should therefore reflect the value 10.
The absolute reference for the column and row in the formula in A1 means that if I cut and paste A1 around, it's formula will not change. It will remain =$A$2.
What I want to figure out is how I get Excel to not change the formula in A1 if I move *A2* around. In other words, if I cut A2 to somewhere else, I still want A1 to reflect =$A$2, rather than the location I moved A2 to, which is precisely how Excel behaves.
What I'm after is keeping a formula absolute, in all cases, regardless of what I do with its targeted cell(s).
Is this possible?
Thanks in advance.