Calculation problem in Excel 2007

[Schizoid Man]

Hi,

I have a sheet that has hundreds of Index, Match, Offset functions. It is
not uncommon for the calculation order to break and the sheet to stall.
After some digging around I found this on the Microsoft web site:
http://support.microsoft.com/kb/919127.

However, I've managed to reproduce the same problem in Excel 2007. Has
anyone seen this in Excel 2007 too?

Thanks,
Schiz

 

[Charles Williams]

I have never been able to reproduce the problems mentioned in the KB
article, even on massive workbooks.

I would try Ctr/Alt/Shift/F9 once to rebuild the dependency tree to see if
that fixes the problems for subsequent F9 calculations.

Or you could try switching ForceFullCalculation on (requires VBA).

Charles
__________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Schizoid Man]

I have never been able to reproduce the problems mentioned in the KB
article, even on massive workbooks.

I would try Ctr/Alt/Shift/F9 once to rebuild the dependency tree to see if
that fixes the problems for subsequent F9 calculations.

Or you could try switching ForceFullCalculation on (requires VBA).

Charles

Hi Charles,

I tried rebuilding the dependency tree as you suggested, but no luck
unfortunately.

However using the Microsoft suggested fix of ActiveSheet.UsedRange.Calculate
did work. I would have emailed you the sheet, unfortunately it contains a
custom add-in without which it won't calculate. If I manage reproducing this
problem on a stock Excel example, I'll email the sheet across.

Thanks.