Why does Excel Calculate not complete?

[MoonMullen]

Why does Excel Calculate not complete? I have a workbook that has many worksheets, some graphs and many modules within it. When I set Calculation to manual and back to AutoCalculate the calculation process nevers quite completes entirely. It counts up to about 75% and then stops. What would cause this to happen? Can "#Ref" errors cause this to happen?
Also when I close the above workbook, with AutoCalculate turned on, the Calculate "warning" at the bottom of the screen does to turn off. I have a hidden workbook that contain numerous subprocedures and function open everytime I open up Excel and a "book.xlt" in the "xlstart" folder. Could Excel be setting these workbooks to manual without my knowledge? If I close Excel I get a prompt to save my hidden workbook, which I do, however when I re-open Excel that Calculate "warning" is NOT displayed below. Have I corrupted my first workbook somehow? Any and all ideas would be appreciated. I

 

[ilses]

As to manual calculation: this is application wide, i.e. all open workbooks plus any that you open before changing to automatic will be set to manual calculation. The book.xlt in Startup and hidden workbooks will also be affected.

If there are #REF errors, I suppose there must be cells flagged for recalculation that Excel finds it cannot recalculate?

To force full recalculation, which does a little more than simple (re)calculate, press Ctrl-Alt-F9. This will also recalculate custom worksheet functions. This key combination was not documented in Excel version before 2000, but should work (at least) in 97 too.

HTH

ilses

 

[MiggyD]

I thought that #REF were calcs that used the value/answer from a REFerenced cell, and if the cell had been moved/ deleted/ or become unattaineble and REFerencing was lost, then it would cause these types of problems.

Am I wrong on this?

--MiggyD It's better to have two heads to solve a problem from different angles than to have tunnel vision to a dead end.

 

[Hasit]

MiggyD, you are correct:

The #REF! error value occurs when a cell reference is not valid.

Possible causes are:
Deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas.

Running a macro that enters a function that returns #REF!. The function may refer to a cell or range of cells that is not valid.

Using a remote reference to an application that is not running or to a Dynamic Data Exchange (DDE) topic such as "system" that is not available.

 

[stingers]

I was having the same problem and found the following answer at MS. Seems to be the casue

http://support.microsoft.com/default.aspx?scid=kb;EN-US;243495

Just thought I would make an update in the event anyone else has the same problem