Excel references changing

[FinanceinTexas]

Has anyone ever had an issue where dragging and dropping, or even cutting and pasting a cell that contains a precedent to another cell doesn't cause the dependent cells to automatically update?

eg:
Cell A1: contains the value 5
Cell B1: contains a formula referencing cell A1, something like "=A1*2"

In most contexts and files, if I were to either cut and paste the cell A1 and move it anywhere else on this tab (or even on a different tab), the reference in B1 would automatically update to reference where this cell has been moved to.

However in some workbooks this is not happening. No clue why.

 

[Joe4]

You can "anchor" the range value by placing a "$" in front of the row or column reference you want to anchor, i.e.
$A1
A$1
$A$1

Then when you copy/paste or drag, the "locked" references will not change.

 

[FinanceinTexas]

You can "anchor" the range value by placing a "$" in front of the row or column reference you want to anchor, i.e.
$A1
A$1
$A$1

Then when you copy/paste or drag, the "locked" references will not change.

No this won't work.

Try my exact example on a blank excel:

Type "5" in cell A1.

Type "=A1*2" in cell A2.

then cut and paste the contents of A1 anywhere else. Regardless of anchoing status, the text in A2 will update. Or at least it should.

 

[Joe4]

OK, I see. I was looking at the wrong cell.

If you "cut and paste" it, it will change in the formula, as with "cut and paste", Excel sees it as you are moving the source cell.
However, if you were to "copy and paste it", it would NOT change the formula, as you have not moved the original source cell (you just copied it).

Is it possible that the person used "copy and paste", and then deleted the contents cell A1 AFTER they did that?

 

[FinanceinTexas]

OK, I see. I was looking at the wrong cell.

If you "cut and paste" it, it will change in the formula, as with "cut and paste", Excel sees it as you are moving the source cell.
However, if you were to "copy and paste it", it would NOT change the formula, as you have not moved the original source cell (you just copied it).

Is it possible that the person used "copy and paste", and then deleted the contents cell A1 AFTER they did that?

No, this is impossible. The person is me, i am doing it right now, in real time. No matter what I do, any dependent cells that reference precedents stay with their exact text exactly as is. Regardless of if I insert or delete columns, cut and paste or drag cells, etc. It makes any cleanup or consolidation of this workbook basically impossible.

It's a very large workbook that I unfortunately cannot share. But my normal solution would just be to start over on a new workbook and assume there is some setting on this one that has this being overridden. But again, it would be a lot of transferring stuff over. hundreds of tabs, etc.

 

[Joe4]

Was this workbook created in another program originally, like Lotus Notes?
Sometimes there are some old Notes settings that cause weird things like this to happen.

 

[Joe4]

Also, are the example formulas you posted representative of what your actual formulas that are having this issue look like?
If not, please post one of these formulas exactly as it appears. If you have formulas using the INDIRECT function, that would make sense that they would not change, as the range references are actually Text value and not Range references in those types of formulas.

 

[FinanceinTexas]

Also, are the example formulas you posted representative of what your actual formulas that are having this issue look like?
If not, please post one of these formulas exactly as it appears. If you have formulas using the INDIRECT function, that would make sense that they would not change, as the range references are actually Text value and not Range references in those types of formulas.

no they're formulas that, if i type them in a clean workbook, adjust as expected. I can even copy one of the tabs out as a new file and on this version moving the boxes around updates as expected.

There's some setting somewhere in the file that is messing things up. No macros are in the file or enabled, so it can't be that.

 

[Joe4]

There's some setting somewhere in the file that is messing things up. No macros are in the file or enabled, so it can't be that.

Did you see my question in post 6?

 

[FinanceinTexas]

No it's Excel. 2015 originally, I think.

Weirdly, in some cases I can open it fresh and I have one chance to drag and drop one precedent and the dependent updates accordingly. But then if I try a second time, it cases. And even if I try to literally drag the precedent right back to where it was, it breaks.