Excel adding column makes formulas incorrect

Question

Dear community,

I had set up an Excel file, encountered a problem and simplified it as follows:

As you can see the value and formula for D7 are incorrect, it should reference E6-D6, instead it was changed to:

=IFS(D$3>Last_date_month;0;TRUE;E6-C6) 

See here for sample file: https://1drv.ms/x/s!AtNwspiFU1pNj5MHSGgOIRWiUq54CA?e=TWWaJc

The reason for the mistake is that I added column B. I expected that all references would be adjusted accordingly, but this does not seem to be the case. As I don't understand Excel's behavior in this situation and want to avoid adjusting every formula after adding columns, I wonder why this could have happened or how Excel works in this situation.

Many thanks for your help!

Answer 1

Sorry, no idea.

Answer 2

Thanks for your answer. I am trying to understand this particular example so that I know how excel updates references before I add a column and can maybe adjust how I add the column to avoid that I have to rework the process. Are there any rules / specific reasons why it would not have worked in this specific case?

Many thanks!

Answer 3

Excel automatically adjusts references when you insert or delete rows/columns. But in general, the formula should still work.

Answer 4

Of course, I am aware of these problems as I just created this small sample for the forum, but I am rather interested in the general principle: When does it occur that the referenced columns change when adding a column?

Thanks for your help!

Answer 5

Last_date_month refers to another workbook, and the formula in D7 is not consistent with that in C7. I suggest creating the formulas again from scratch.