Why do cell references sometimes NOT update when I drag a column to a new location?

[d0rian]

It's standard Excel functionality that when you use the mouse to drag a cell (or range of cells, or even an entire column), any formulas that refer to the dragged cell/range/column will change so that formulas don't break. This works without problems 99% of the time.

HOWEVER, I've found several instances where, for some reason, when I drag a column to a new location, the formulas that referred to cells in that column DON'T adjust, and still point to the old, now-empty column. Such that when I then delete the old/empty column (where the source range used to be), all of the formulas break (with all of the cell references that didn't properly adjust being replaced by #REF errors).

I want to know why that happens, since moving sources ranges/columns around is something I do a lot. This seems to be particularly problematic when there were a LOT of formulas that were dependent on the range I was moving...other than that, I haven't noticed any pattern as to when this happens (though I've only noticed it when I was moving entire columns, rather than individual cells or smaller ranges).

Is this a known bug, or is there some kind of limitation to Excel's formula-updating functionality I'm not aware of?

 

[glfiedler]

One thing to be aware of is "absolute" addresses. If any of the ranges or cells in your formulas have references such as $A3 or $D$4 (note the $) the $ part of the address will remain the same, that is it is not relative.

 

[d0rian]

One thing to be aware of is "absolute" addresses. If any of the ranges or cells in your formulas have references such as $A3 or $D$4 (note the $) the $ part of the address will remain the same, that is it is not relative.

No, perhaps I didn't describe the problem correctly. I know how absolute / '$'-symbol works. But when you drag an entire column (or a cell or range of cells), those absolute references DO update to reflect the new column position. E.g., if

A1: apple
B1: =$A1

> If you drag column A and drop it so that it's now Column D then the formula in B1 will update to =$D1.

I'm not talking about copy-pasting formulas that use the $-anchor. I'm talking about dragging entire columns/ranges, whereupon the formulas DO update. Except as I wrote in my OP, every so often, doing this breaks all sorts of stuff because for some reason the formulas don't update to take account of the source range's new position. And I'm trying to figure out why, because there seems to be no rhyme or reason to it.

 

[glfiedler]

Sorry. I thought my answer was a bit basic but you never know the posters skill level. I have used Excel for 30 years as a engineer and never "dragged" a column. I am not even sure how you do that! If I select the column heading and drag all I am doing is dragging the column range. I am always open to learning new things in Excel (and this forum is a great place for that) but how do I "drag" the column?

 

[d0rian]

Highlight the column (by clicking on the letter B above the B-column for example), then move your cursor down so that it aligns with one edge of the selected column (say, the edge between cells A1 and B1), and then drag. Helpful when you want to re-order columns.