Really Weird #REF Error in Complex Excel File?

Hello,

So I have run into a very strange and extremely annoying problem: I am working on a large and complex set of Excel files in Microsoft Excel '97. Each file contains about 15 worksheets, in which 13 of the 15 worksheets have about 60 columns with data. None of the files reference cells in OTHER files.

Almost all of the cells contain a function of some kind, although not complex functions. Straightforward arithmetic (dealing with a complicated financial model). Now, here is the weird part: when I copy & paste a function into the next cell over, it gives me a #REF error. However, the function is correct and there is no reason for a #REF error to be delivered. When I paste THE SAME function into the SAME cell, the original #REF error vanishes, but a PRECEDING cell (usually in the same row but with no formulaic relationship to the cell I just changed) changes to a #REF function.

If I copy & paste the same function three times into the same cell, all #REF errors vanish. But then if I save the document, a #REF error appears again for no reason.

Has anyone else seen this problem? If so, is there any way to fix it? I have been told by colleagues that similar problems have been observed in Excel '03, but have not yet tried opening the files under a newer version of Excel...

All the best,
Chris

It does this for many different functions, but here are just a couple of examples:

=SUM(AW8:AW9)

Where AW8 = 29100
Where AW9 = 0

The same happens when =SUM(AW8:AW9) is written =AW8+AW9

These are the general "class" of functions for which this problem is cropping up. Straightforward arithmetic, and in all cases the cells they reference have valid (and simple) values.

All the best,
Chris

It's horrid when something like that happens, and it's usually in a huge file.
I have had similar problems, but I'm afraid I can't suggest anything specific.
Somewhere in your sheets there will be a mistake, a typo,
or inappropriate automatic update of an address in a copied cell, etc...
Unfortunately it may not be particularly close to where the #ref finally shows up.
It's an old-fashioned Bug-Hunt.
Have you tried the Tools>Formula Auditing facilities?
Have you got an old version of the file, from before it stopped working?
Can you look back through the changes to check for mistakes?
If all else fails you might have to re-construct the spreadsheets,
carefully copying and pasting from the existing (broken) version
and checking at each stage that everything is correct.
Hopefully at some stage in that process the bug will jump out and you can then squish it.
Mark.