Workbook not auto calculating (2002)

[tmg9671]

I have a workbook that contains about 80 individual sheets, most of which are linked to each other. Whenever I change a value in a cell, the other sheets that read that cell do not change unless I double-click the cell (or hit F2) as if I were modifying the formula. After I hit enter, the cell values then change. Why won't these cells auto-calculate? (The auto-calculate option is checked under Tools>Options.) Are there too many pages or is it just an fluke? Thanks.

-Troy

[HansV]

Perhaps Excel is low on resources and turns off automatic calculation. You could test this by creating a copy of the workbook and removing half of the worksheets. Save the copy, then quit and restart Excel. Does the problem persist?

Also: what kind of formulas do you have? User-defined functions do not always update automatically.

[tmg9671]

I will try the test, but the problem is sporadic, so the testing may not be conclusive. This may lean toward the low resources explanation. The formulas I use are Excel formulas (SUMIF, LOOKUP) or simply one cell reading the value of a cell on another sheet. Is there a way to boost Excel's resources? Thanks.

[HansV]

You may find something useful on the DecisionModels site: Excel Pages - Optimising Speed.

[tmg9671]

Very helpful! Thank you!

[zeddy]

Just another thought..

I believe Excel2002 allows you to set the calc mode for each sheet separately as a sheet property.
The property is EnableCalculation and can be set to True or False

zeddy