Workbook causes calculation errors

[Luke M]

I've never seen this error before, and unfortunately, my Google searches
keep bogging me down with the simple "Automatic/Manual" solution...

I have Workbook A, which contains a large amount of data/formulas (3000 rows
x 100 columns, several columns using SUMPRODUCT checks). The workbook is in
Shared mode, and due to amount of calculations it does, is left in manual
calc mode. Up until a few days ago, users could make changes to the data,
hit F9 to recalc, save, and move on. Then something happened...

Now, the workbook always displayed "Calculate" in the status bar, even after
doing a reclac or switching to automatic mode. Could I be at the 65536
dependency limit? File still seems to calculate okay, just displays message.
However, the problem is that whenever another file is opened within the same
instance of XL, it becomes "corrupt". If I open workbook B, open workbook A
and close it immediately (without doing anything in it) Workbook B is
permanently flagged as needed to calculate, and does a full calculation
everytime I hit F9. While formulas will recalc, charts do not display
correctly.

Remedies I've found to work are:
1) save, close Workbook B, and reopen (no longer displays calculate, works
fine).
2) Switch to automatic mode with Workbook B, then back to manual. Still
displays "calculate", but seems to work for the most part.

If anyone else has run across something similar or has suggestions, I'd be
eager to hear. Thanks!

 

[Charles Williams]

Once an Excel session has hit one of the dependency limits it stays in
that mode even if all the workbooks are closed, unitil the Excel
session is stopped and restarted.

See http://www.decisionmodels.com/calcsecretsf.htm for more details

regards
Charles Williams
Excel MVP
The Excel Calculation Site
http://www.DecisionModels.com

 

[Luke M]

Thanks, Charles, that seems to describe what's going on. Any thoughts as to
why the Charts in Workbook B are affected so (not updating)?

As your website says, there doesn't seem to be a good way to count
dependencies, and I'm not sure if I can reduce the formulas. I'll see what I
can do.

Again, thanks for the info.