Autocalculate and forced calc do not work in Excel 2003

[Skychick]

I am a systems analyst and one of my users has a random problem with spreadsheets where she enters a formula and it does not calculate. She is using Excel 2003. Here are the things I have already checked out:

Autocalculate IS turned on
The numbers ARE really numbers and not text
Editing the numbers and formulas and re-entering them did not help
The formulas are entered correctly and are simple such as sum and multiplication
There are no other workbooks or spreasheets open
Hand entered numbers into different cells with the same result
Tried copying and doing a paste special as values with same result
Tried forcing a calc with F9 with no results
The bottom of the screen says calculate so it knows that it needs to calculate, just won't do it.
Very small spreadsheet so no maximum number of formulas hit
Network support re-loaded Excel onto the machine twice already
Saving the spreadsheet and re-opening it usually solves the problem.
When spreasheet is sent to someone else, it opens fine with calculations completed.

If anyone knows of a hotfix that might exist for this issue or what could be causing it please let me know. Thanks

 

[Skychick]

Hi Jeff, the user finally had this issue happen again today and I was able to try some things with the spreadsheet.

My user had created the spreadsheet by running a query in Peoplesoft and then clicking the Excel button to send the results to Excel. Once there, she did a sort of the entire sheet. A Sort warning came up :The following sort key may not sort as expecte because it contains some numbers formatted as text what would you like to do" She chose the default option "Sort numbers and numbers stored as text separately". I belive this came up because she was sorting on account codes that were entered as text. No math is performed on these fields.

She proceeded to enter formulas in the sheet and they were working until she got partway through the sheet. All of a sudden, every formula she entered after that would not compute.

I tried doing a replace all: = with the = but it did not solve the problem. I tried F9, Shift F9, Ctrl F9 and even went into the calculation options and set calculation to manual and pressed F9, then set it back to automatic. Still, the word Calculate persisted on the bottom of the screen yet no attempts to force a calculate had any affect. In fact, between the two things I did do, one of them appeared to cause the cells that had calculated properly from the start to no longer show calculations.

I entered numbers and formulas from scratch, still nothing would add up. I even turned on formula auditing and it showed the predecessor but still did not calculate. Saving the sheet, closing it, and then re-opening it does solve the problem.

All service packs for Office and Excel 2003 are up to date. Any other ideas?

 

[SamS]

Just a thought as it has been a while since we had PS but is the excel file being created an Excel 5 and not 2003 format. Try resaving the file as an Excel 2003 version, I know that one particular solution (don't think it was PS though) was to Copy a zero and Paste Special/Add to the cells that were involved in the calcs, this forced them into numerics.

I know it is a long shot but my memory is not what it used to be and I recall there were data dumps occassioanlly where this happened.

 

[ZVI]

Activate workbook and try applying of this macro:

Sub UnFreezeCalc()
  Dim Sh As Worksheet
  For Each Sh In Worksheets
    Sh.EnableCalculation = True
  Next
End Sub