Functions Stopped Working

[Guest]

I have recently encountered the strangest thing in some of my Excel 2003
workbooks. The functions just stop working. I have a workbook with multiple
sheets and an array of formulas that all stopped displaying the formula
results. It almost seems like the cells have become corrupt. The formulas
are all correct as well. The only way I can get them to produce the result
is to enter into the cell (double click or F2) and press the enter key again.
MAGIC!!! the result displays. I cannot do this for thousands of cells and
multiple pages though. Even after I do, the formula will not update with a
new results unless I follow the preceeding step again. It's like Excel went
catatonic.

Has anyone else encountered this occurence? Is there a fix? Do I have to
rewrite my entire workbook?

Thanks in advance.

JD

 

[Peo Sjoblom]

You might want to copy your sheet to a new workbook since this might be a
sign that your workbook is getting corrupt.Nevertheless this has always
helped me when faced with a similar problem, select the range in question,
do edit>replace and replace = with = (in the find what box type the equal
sign and in the replace with box type the equal sign)
That will force a recalculation

 

[Guest]

Some other thoughts ..

1. Could it be just the book's calc mode being "inadvertently" set to
manual? Click Tools > Options > Calculation tab. Options are there. Check
"Automatic" > OK.

2. Try the "Open and repair" option in xl2003

Sequence:
File > Save as .. (back-up name)
Close the file
File > Open
Navigate to and select the "back-up" file in the "Open" dialog
Click on the tiny dropdown to the right of "Open" button > choose "Open and
Repair.."

 

[Guest]

Max, you're the best. I've been working in Excel for years and feel foolish
when I learn such simple solutions that were right in front of my face.
Thanks a ton. Your first suggestion was spot on! The calculation updating
was somehow set to manual.

JD

 

[Guest]

Max,

I noticed you are a silver level contributor. Not sure what that means, but
wanted to let you know my coworker says you're GOLD in her book!!!

Thanks again.

JD

 

[Guest]

Glad it helped sort out things over there.
The feedback is appreciated.

.. The calculation updating was somehow set to manual.

Excel sets the calc mode based on the mode for the 1st book opened in the
session. If the 1st book's mode happen to be manual, then any subsequent book
opened in the same session will be on manual calc mode, irrespective of their
earlier saved modes. Whenever/if in doubt, just do the quick check on the
calc mode (Tools > Options > Calc tab).

---

 

[elle lothlorien]

Max, your post is three years old but it saved my bacon today when all the formulas in my worksheets suddenly stopped working! THANKS A MILLION!

Elle
Denver, CO

 

[Steve Rams]

Max- Thanx. The auto calc fix didn't work but open and repair did!
Steve