Excel 2007: formulas changed to =#N/A

Multiple Excel spreadsheets are experiencing a very similar issue, which I have been unable to find a resolution for. Researching has led to finding that a lot of other people have experienced a similar issue, but with no workaround or way to resolve it: Google Forum on Topic.

We upgraded from Office XP (2002) to Office 2007 back in September. All systems are running Windows XP SP2 with updates installed. Office 2007 SP1 has been installed, along with updates.

Recently discovered, opening the affected spreadsheets shows all of the cells with formulas with #N/A errors. Looking at the formulas, all have been replaced with =#N/A; the cells are displaying properly then because that is what the formula tells the cell to display. Looking at previous versions shows the same results, back until 10/9/2007 when the formulas are shown and the correct results displayed in the cell. The next 3 versions of the file all open up with the message File Error: Data may have been lost, then all of the cells show the #N/A error.

If calculation is set to manual and the file is opened, the same File Error message is displayed, but all of the cells contents show the correct data. However, all of the formulas show =#N/A. If you calculate everything (Ctrl + Alt + Shift + F9), all of these cells display #N/A.

If I copy the contents of the older file to the newest file, the forumla works and displays the proper results (as do all cells which reference this cell). The Analysis Toolpack add-in does not affect the outcome, whether installed or not.

I have determined that this is not user specific, nor is it specific to the version of Office (2002/2007) or the file (*.xls/*.xlsx). My only guess is that, along the line, something was removed from these files and caused a corruption. When opening in Excel 2002, the error message states that there has been a major corruption of the file and is beyond repair.

Because these files are very detailed and would require an insane amount of work to copy all of the formulas (there are about 15 sheets in each workbook, and there are about 10 workbooks experiencing this problem), I'm hoping that there is a quicker resolution to this. Has anyone ever encountered this? Also, does anyone know a better method for correcting all of these formula changes? Any help would be greatly appreciated.

Thanks in advance and sorry for the length (it's not an easy scenario to describe in words).


Edit: I just realized that the two functions being used which are displaying this error are the CUMPRINC and EOMONTH functions.

Microsoft offered no help with this. I called up and spent more time informing them of an issue which they knew nothing about than getting any help. Their response? They e-mailed me with a link to a document describing the best tips to prevent file corruption, all of which we employ already. Thank you so much, Microsoft Support.

Essentially, all repairs must be performed manually. If anyone finds a better solution or reason for this occurring, please let me know. Staff is not happy with hearing "Sorry, but the file is corrupted and no one knows why or how to easily repair it."

Unfortunately, this situation is getting worse. More and more of our spreadsheets are showing up with this problem, which leads me to reason that this is not a simple file corruption, as Microsoft claims it to be. This appears to be more related to the Office 2007 upgrade and using templates which were based on the 97-2003 file formats (*.xls), despite the fact that Microsoft claims these files should function normally in compatibility mode.

Some of the files in question contain user-created macros and some do not. As a test, we've saved some of these templates as the 2007 file format (*.xlsx) and saved the ones with macros as macro-enabled 2007 file format (*.xlsm). It's going to take weeks of testing, however, to determine whether or not this helps prevent future problems of this nature.

Needless to say, no one here is happy with what is happening to our files, and because I was in charge of the 2007 upgrade, all the heat is falling on me.

Before upgrading, I had run compatibility scanning tools. Nothing like this showed up on the scans, so I doubt seriously that this was an oversight by my department.

BIG help needed here.