Excel 2007: formulas changed to =#N/A

[Myke 0]

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.

[Myke 0]

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."

[Myke 0]

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.

[MattW 0]

We too are having the same issue. Did you manage to get anywhere with it?

 

The sheets are using the EOMONTH formula.

[PC2870 0]

Most likely reason is you are using a function in the formula that is not one of the standard ones in 2007. Go to Tools addins and enable the addin that contains your function. Analysis toolpak would be a good start.

[deanalt 0]

http://www.archivum.info/microsoft.public.excel.programming/2007-05/msg00291.html

 

I had this problem (2003 add-in functions not being recognized by 2007) for a long time (see URL above) and, because of it, went back to EXCEL 2003. Right now, I am not seeing the problem (using another machine with 2007 on it) and am wondering if you folks still are or if, perhaps, it was finally fixed. I do recall that it changed to N/A only sometimes, not every time, so perhaps it's just an intermittancy that hasn't shown up again yet.

[Rsonder 0]

Only yesterday I discovered major problems along the same lines. I have hundreds of legacy spreadsheets (Excel 2003 and earlier) with dozens of worksheets each, all chock full of YEARFRAC() and EDATE() functions requiring Analysis Toolpack Add-in. All of them must be updated periodically or are used as templates for new versions of similar spreadsheets.

 

Same problem -- "intermittent" and surreptitious replacement of all cells with such functions with "#N/A" -- if you then save, you're f....d for ever. BTW -- closing excel, REBOOTING, and restarting Excel will cure this "behavior infection", but only until some unknown next time. (I have Vista).

 

After reading this and other posts, my latest theory to which I invite confirmations or rebuttals is that, since Excel2007 now supports these functions "out of the box", one should NOT turn on the Analysis Toolpack Add-in. I just tried and it is definitely not needed, so -- still by unproven theory -- it is possible that there is some conflict between the "native" and "add-in" versions of these formulas.

 

Please comment, correct me, or otherwise contribute your knowledge. I am so desperate that I will have to switch back to Excel 2003 (no big deal except for those who send me xlsx files now).

 

Robert

[cfield 0]

This is an all to common problem. I work in a mixed 2003/2007 environment within the office and usually work within compatibility mode saving WB's as XLS instead of XLSX. I use the mround and eomonth functions extensively in my worksheets.

 

My tip: When I can't get around using them easily, I sometimes put a comment in the first cell with the 'at risk' formula and put the formula text in the comment so I don't have to re-write the whole formula logic again.

 

I swear this #N/A issue knows when I'm working on a deadline too 'cause that is when it always seems to happen. I'm in hopes that 2010 will not have this issue.

 

Chris

[Iwarelogic 0]

This is very common problem for many excel users, if this has happened accidentally then you can definitely opt to lock the formula tab. All the best!

[Myke 0]

Sorry that I never responded. Sometimes go MIA from forums.

 

I do not remember exactly what patch it was, but I was dealing with Microsoft for days regarding this problem. After almost a week, they hooked me up with a patch which was specific to this problem. They even refunded the fee as it was an engineering problem with Excel 2007. Because this was almost 3 years ago, I don't remember how great of a job the patch did or even if it completely fixed the problem. I do, however, know that we don't have this problem anymore.

 

Sorry that I'm not of much use for this.

[Mark Walters 0]

Hi

 

I know its a while since there was any update on this, but there are people still experiencing this problem - like myself! So after scratching around like everyone else, I seem to have found the solution on Microsoft's website - although their description is not a 100% match to my experience, I optimistic that it will work, but only time will tell. If others want to try it, go to:

 

Excel 97-2003 workbooks (.xls) display #N/A! for Analysis Toolpak functions

 

for some details, or directly here for the download:

 

Description of the Excel 2007 hotfix package

 

Regards

 

Mark