Why does "Calculation in Progress…" show that all previous sheets are being recalculated during the calculation of the current sheet?
As background,
-the workbook contains unique sheets that are not being created by the "Sheets" command.
-No formulas on any sheet refer to other sheets unless the reference is contained in an "Eval" formula. Even then, the references are only to the "Options" sheet.
-I am using Excel 2003 and an older version of Jet.
The issue, of course, is speed. Each individual sheet is calculated quickly, but the overall calculation time increases exponentially with each additional worksheet.
Is this an inherent Excel issue that must be tolerated?
Thanks,
Lloyd
Date
Votes
3 comments
-
Jet Reports Historic Posts I'm guessing that your sheets reference to others in workbook so as one gets calculated the other(s) get recalculated
ie sheet 1 ( non jet) references sheet 2 also nonjet that references sheet 3 ( Jet) everytime a value in sheet 3 changes excell will recalculate both 2 and 1 -
Jet Reports Historic Posts I agree, Lloyd, that sometimes the multiple worksheet calculations seem unnecessary. It would be nice to have some sort of option to (re)calculate the current Jet Report sheet only instead of it having to run through the entire process everytime you make a modification on just one of the worksheets. Perhaps submit this issue under the Suggestions & Feature Requests section?
Dan -
Jet Reports Historic Posts Do you have any volatile functions in your workbook? The most common ones are =TODAY() and =NOW(). These generally get refreshed by Excel every time that a cell points to them, so you might change a cell that points to the TODAY() function which updates the TODAY() function. Since the cell with the TODAY() function was updated, Excel will try to refresh all cells that point to this cell, which could be a good portion of the report.
If you have a TODAY() function in your report then you should wrap an NP("Eval") function it which will prevent this behavior. The cell would look like:
=NP("Eval","=TODAY()")
Don't forget to format this cell as a date.
Please sign in to leave a comment.