Forum Discussion
Excel calculation stopped working
There were over 900 formulas and most of them used INDIRECT() - which is a volatile function.
There also were 100+ conditional formatting rules.
I deleted the CF-rules.
I put the data in an Excel table.
I changed the formulas to use structured references.
Everything runs smooth.
The file contains two dashboards.
One in your style and one using an Excel table.
https://docs.google.com/spreadsheets/d/13JmhklJdYPWr44al3VgAt4P6ibjbCTQw/edit?usp=sharing&ouid=111524828079692110479&rtpof=true&sd=true
There were over 900 formulas and most of them used INDIRECT() - which is a volatile function.
There also were 100+ conditional formatting rules.
I deleted the CF-rules.
I put the data in an Excel table.
I changed the formulas to use structured references.
Everything runs smooth.
The file contains two dashboards.
One in your style and one using an Excel table.
- Thank you! I wrote the formulas a while ago and you're probably right - I inserted a column at one point. I do remember needing to do a 'backward' reference (D:C vs C:D) as that was the way to make the formula calculate correctly. I don't remember the specifics. I am surprised and still curious about the reason the formulas started to exhibit this strange behavior now. The formulas, as odd as they are, worked for several months, at least.
alexyaro Indeed, Detlef_Lewin 's solution is recommended. But, since you still wonder why your original formulas don't work, consider this.
All the criteria ranges in the formulas are quite odd. They span two columns as if you created them first to be something like C35:C296 and then inserted a column but leaving the second column reference hard-coded with "C". The result is a reference to D35:C296 which Excel then 'translates' to C35:D296. And you probably want this to be D35:D296.
I corrected all of these in your original file and all seems to work as intended. See attached.
Detlef_Lewin , I checked my Options - Iterative calculation was/is unchecked while the problem persists. I am referring to the old copy using INDIRECT functions.
- Got it - thank you very much!
- Just add new data under last row. The table grows. And no need to update formulas in the dashboard because of the structured references.
- Thank you - this is great. How can I dynamically extend the table to include new rows?
