File Calculation

[tj]

I have a 40+MB file that is full of complex functions including lookups, if
statements, and array functions. Under the tools, options menu, calculation
is set to automatic. There are no circular reference errors that I can find.
But the file continuously indicates that it needs to be calculated. If I
press the F9 key, it calculates, but still indicates (at the bottom left-hand
side) that it needs to be calculated. I'm afraid that it may not be
calculating correctly.

In addition, the "trace dependents" function indicates that no cells are
referenced by the active cell when that is not the case. These two problems
appear to be related, but I cannot figure out if there is an easy fix or the
best way to troubleshoot.

Please help.

 

[Peo Sjoblom]

Do you have any volatile functions like INDIRECT, OFFSET, NOW(), TODAY()
etc?

 

[tj]

Yes, I have the following:
=SUMPRODUCT((SUMIF(INDIRECT("'"&Tab_Names&"'!"&ADDRESS(ROW('S1'!$B$11),COLUMN('S1'!$B$11))),"include",INDIRECT("'"&Tab_Names&"'!"&ADDRESS(ROW('S1'!G196),COLUMN('S1'!G196))))))

This calculation is used to sum data from tabs S1 to S25 only if I've said
to include such cells in the total.

However, I just removed that to see if it helps, and I still have the problem.

I can't find any other volatile formulas.

 

[Charles Williams]

The workbook probably has too many dependencies for Excel to track them
see http://www.decisionmodels.com/calcsecretsf.htm for details

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[tj]

Thanks. This is helpful.

The workbook probably has too many dependencies for Excel to track them
see http://www.decisionmodels.com/calcsecretsf.htm for details

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com