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

d0rian

Board Regular
Joined
May 30, 2015
Messages
241
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

Board Regular
Joined
Apr 1, 2019
Messages
50
Office Version
365
Platform
Windows
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

Board Regular
Joined
May 30, 2015
Messages
241
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

Board Regular
Joined
Apr 1, 2019
Messages
50
Office Version
365
Platform
Windows
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

Board Regular
Joined
May 30, 2015
Messages
241
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.
 

Forum statistics

Threads
1,082,318
Messages
5,364,522
Members
400,804
Latest member
davileal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top