Formula not updating even after F9 calc

[Guest]

I have a spreadsheet at work that is basically a group of ledger worksheets
that summarize to another worksheet. Most of the worksheet is protected
since I send it to my employees to fill in specific parts (which are the
cells not protected). After emailing it to them, certain formulas stop
updating with the proper numbers. Specifically they are ones that simply
reference another cell on one of the worksheets in the workbook. (so
something like: ='Gribble, S'!D62 where Gribble is the name of the worksheet).

Automatic calculation is marked in the options tab. F9 and Shift F9 have no
effect. If I edit the formula and erase any part of it and type it back in
it suddenly pulls the correct value. It is only on specific worksheets or
colums, and if I enter another formula in the faulty sheet it work fine. It
is almost as if the formula is correct but it is hanging on to old numbers.
This only happens when I email them to my employee and it is not happening
all the time.

Please help!

 

[Dave Peterson]

I've never seen this in real life, but others have said doing this fixes the
problem:

Select all the cells on the worksheet
edit|Replace
what: = (equal sign)
with: =
replace all

It'll force excel to re-evaluate the formulas.

 

[Guest]

That is an easy fix. Thank You! Does anyone know the cause or how to
prevent it in the future?

 

[Dave Peterson]

Ay, there's the rub.

I've never seen anyone explain it -- besides the obvious thing that excel messed
up.

And if there was a way to prevent it, I bet MS would have resolved the problem
(or maybe not <vbg>).