Excel chart won't update, based on calculated cells

Question

I have an Excel document (2007) with a chart (Clustered Column) that gets its Data Series from cells containing calculated values

The calculated values never change directly, but only as a result of other cells in the sheet changing

When I change other cells in the sheet, the Data Series cells are recalculated, and show new values - but the Chart based on this Data Series refuses to update automatically

I can get the Chart to update by saving/closing, or toggling one of the settings (such as reversing x/y axis and then putting it back), or by re-selecting the Data Series

Every solution I have found online doesn't work

- I have Calculation set to automatic
- Ctrl+Alt+F9 updates everything fine, EXCEPT the chart
- I have recreated the chart several times, and on different computers
- I have tried VBA scripts like:
Application.Calculate
Application.CalculateFull
Application.CalculateFullRebuild
ActiveWorkbook.RefreshAll
DoEvents

None of these update or refresh the chart

I do notice that if I type over my Data Series, actual numbers instead of calculations, it will update the chart - it's as if Excel doesn't want to recognize changes in the calculations

Has anyone experienced this before or know what I might do to fix the problem? Thank you

Answer 1

I have the same issue as the original writer. And I also tried already all the things he did and none of them worked. Also I now checked the calculations options and that was automatic just as it's supposed to be. Everything else works and calculates fine but this one chart does not update when the data changes (and the data calculates with a special funcion that separates different colored numbers from another set of data).

I found a temporary solution and made a macro (and a button for that) which first updates the data similarly to Ctrl+Alt+F9 and then switches the rows of my chart to columns and then again columns to rows. Strange, but works.

I used this to switch them:

ActiveChart.PlotBy = xlColumns
ActiveChart.PlotBy = xlRows

Of course I would prefer to find out the real way to fix it but this has also worked so far...

Answer 2

Not sure about 2007 but there are setting that turn automatic calculations off. Look in the calculations options and see if it is set to manual.

Answer 3

Check if the ForceFullCalculation property in the ThisWorkbook properties is set to True. I've noticed that the charts don't autoupdate anymore if the aforementioned property is set to True, I guess it's a side-effect. If you set it back to False, everything goes back to normal.