When I have this formula: =sum($B$1:$D$1) in cell A1 and when I insert a column before column B, the formula is changed to: =sum($C$1:$E$1). Isn’t absolute addressing meant to prevent those references from changing?
No.
The difference between relative and absolute addressing is this: when you use relative addressing, you say “I refer to a cell X columns left and Y columns above current one (with formula), wherever current one is”. When you use absolute addressing, you say “I refer to that specific cell”. And the specific cell is actually known not by address, but “personally” - the formula binds to a specific cell, and if that cell gets shifted by some user operations, it still keeps its identity - so all absolute references to it hold.
So what you are saying is there is no difference between relative and absolute addresses!? I’m expecting absolute addressing to be like GPS coordinates: B1 is always B1. It doesn’t matter that I shifted the content of B1 to C1, I want my formula to use whatever is currently at B1.
No, there is huge difference in absolute vs relative. Absolute is like always targeting a specific person, which face you know - so if that person moves to another city, you still can know that’s that person, and not another. Relative is like “whoever lives next door”.
When you talk about GPS coordinates, you have a wrong analogy. It’s not “address characters” that are absolute, it’s the cells themselves, their identity…
Clearly relative and absolute addressing isn’t doing what I want but my GPS coordinate analogy will. How do I implement that?
+1 for INDIRECT, I am just not allowed to vote for comments yet