Workbook causes calculation errors
in [Functions]
|
From: Luke M on 4 Jun 2010 11:04 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! -- Best Regards, Luke M
From: Charles Williams on 4 Jun 2010 11:58 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 >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!
From: Luke M on 4 Jun 2010 14:19 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. -- Best Regards, Luke M "Charles Williams" <Charles(a)DecisionModels.com> wrote in message news:4h8i06ll3qnra68j27ean47irg0rmc2fdk(a)4ax.com... > 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 > >>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!
|
Pages: 1 Prev: more details please Next: How to tell COUNTIF to not count a few characters |