Anyone else seen this:
Open a happily working .xls that uses some analysis toolpack functions in Excel 2007 (SP1). Do a bit of work, click save, close.
Next time you need the file open it and find that some or all of the ATP functions (EOMONTH, EDATE, NETWORKDAYS, etc) and any cells that depend on them have been helpfully replaced by ‘=#N/A’.
It seems another intermittent problem like the freezing I mentioned the other week. This one though is a proper data loss. If that file is saved (possible as the underlying formula is changed to =#N/A but if its not calculated it still reports the correct answer). As far as I have seen and read this is a compatibility mode only issue – it has a better memory in native mode. Unless you know different?
From what I have read it appears to be a known issue specifically with the ATP functions that have been ‘incorporated’ into the Excel core. I say ‘incorporated’ rather than incorporated because losing them from time to time does not seem exactly incorporated to me.
Apparently the solution is to enable the ATP in the Excel 2007 Addins list. Which is a bit odd. Or to use a native file format. I havent been able to check that as I am struggling to repro the problem.
It looks a bit like the internal codes have moved. Except that its inconsistent. The fact it flows through the calc chain is a bit of a scare too (not sure how far).
Anyone seen this? Any other theories? or fixes/workarounds?
I think it is often partnered by a ‘File Error: data may have been lost’ warning on open.
cheers
Simon
Smurf on Spreadsheets 
Tuesday, 24th February, 2009 at 2:08 am |
File Error: data may have been lost’ warning on open.
I face this in almost all projects developed in 2003 and later opened in 2007…I had not zeroed it down to the ATP functions….Its a bit scary….because its also followed by a crash when I close the file
Tuesday, 24th February, 2009 at 6:49 am |
I haven’t seen it for a long time, but then my habit is to always go in and enable the specific add-ins I want, no matter the Excel version. So I’ve specifically set the reference to the ATP in 2007. I do remember the issue from when I first started using 2007 though. Based on the fact that I haven’t seen any issues and have the ATP enabled, it could support the theory.
Tuesday, 24th February, 2009 at 8:59 am |
Brilliant – MS boast about writing native Excel functions for all the ATP functions (actually not true since they forgot/couldn’t be bothered to do the Fourier transform) – I’ll continue to ignore the horrible ATP functions – having static cells is an invitation to disaster if someone updates the data underlying a regression but forgets to update the ATP
Tuesday, 24th February, 2009 at 11:06 am |
There has been plenty of discussion in / around this on the Financial Mechanics web based forum http://www.fi-mech.com/discussion/viewforum.php?f=12 …. no one has got a one size fits all solution …. the only certainty is the backward compatibility 2007 >> 2003 is less than perfect
Tuesday, 24th February, 2009 at 5:43 pm |
I haven’t noticed that error, but did have something similar in terms of end result – saving a file in xl07 and closing it, then on open discover that several range names have been lost – formulas that used them now have REF errors. I was able to narrow it down to the file name itself – contained “.”s as in ‘name v1.2.xltm’ which doesn’t make sense, but when I started using “-“s instead of “.” the problem appears to have been resolved (or at least well hidden) – took 1/2 day to narrow it down – add that to the complaints about XL07 being less productive than XL03
–Charlie
Tuesday, 24th February, 2009 at 5:52 pm |
Charlie,
I too have started to encounter lost range names, but mine don’t include a dot. I first encountered with a name _refPendings. Odd thing I have found is that sometimes, the name is in the XML structure of the file, sometimes not.
I have reported it to MS, and they picked up that I was not on SP1 – maybe it has been resolved in SP1 they say – yeah! Are you on SP1/SP2 Beta?
I found that creating a new name as well sometimes makes it stick, but not always.
To my mind, this is calamitous.
BTW, does yours use an external query?
Tuesday, 24th February, 2009 at 5:58 pm |
BTW, I jsut tried it with a dot embedded name, name.abc, and it happily stuck, so I don’t think that that is the cause.
Tuesday, 24th February, 2009 at 6:25 pm |
Arghh – how do I find out what version I am using – I feel like an idiot – it must be obvious, but I can not find it – thanks
Tuesday, 24th February, 2009 at 6:28 pm |
I know the dot embedded name is not likely the source, but I could clearly get it to fail and not fail, by just changing the file name – it was a macro template and I was saving/opening it as a template file. I will check the version soon
Thanks
Tuesday, 24th February, 2009 at 6:36 pm |
Hey Charlie you nicked my thunder there – dropped range names is the next post – now posted.
Version:
Blob – Excel options – resources, its down the bottom
Tuesday, 24th February, 2009 at 6:39 pm |
Thanks Simon – I am running SP1
Tuesday, 24th February, 2009 at 7:46 pm |
Excel 2007 – the longest, most expensive public beta test on record.
Wednesday, 25th February, 2009 at 4:39 am |
“Excel 2007 – the longest, most expensive public beta test on record.”
Also the most painful, with the least corrections implemented per bug report.
Sunday, 15th March, 2009 at 3:30 pm |
Yeah, would be a good one to confirm a fix on. Interested to see the ‘enable the VBA’ as a fix.
As to reproducing the problem to see whether this fixes it, best I noticed was from a week’s training in Boston. I got through the week in Compat Mode, but I never did a Save As from 07, always renamed in Explorer and loaded each time. Two individuals on the class noticed the problem, btw, don’t think there is/was a # n the error, just =N/A, and this was the next load after doing a SaveAs. Just FYI, if that helps.
As willers noted, there’s some further discussion on our client forum, which anyone is welcome to look at http://www.fi-mech.com/discussion/viewtopic.php?f=12&t=54
Wednesday, 15th April, 2009 at 7:30 pm |
I have been encountering this problem for months and thought it was me. Decided to google and am happy that I am not alone – but not happy that there does not appear to be a solution. My data shows properly in the cell but appears as =#NA in the formula tool bar. It seeems to happen only when I use a drop box formula and not when I type in a formula myself. I save all of my files in a compatibility mode because I may have to email them. I think I will try saving them in the 2007 version only and resave it as a compatible file for emailing and see if that helps. These are fresh files and not files started in 2003 and resaved.
Monday, 14th September, 2009 at 8:25 pm |
I can confirm everyone’s observation in this thread, though I only discovered this yesterday, with major headaches since I have 100’s of legacy spreadsheets with dozens of worksheets inside all using EDATE() and YEARFRAC() which in Excel 2003 and prior required Analysis Toolpak installed, but appear to be native to Excel 2007 now.
The only one additional observation I can add is the following: Take a specific legacy spreadsheet that looks good under Excel 2003, but shows #N/A in cells with EDATE or YEARFRAC. Fortunately I discovered this before saving said spreadsheet.
Close the spreadsheet, exit Excel2007, reload, same #N/A observation.
Mail the spredsheet to a computer with identical version of Excel2007, open it there — all looks fine! Repeat on original computer, still #N/A.
Now, REBOOT, original computer, load the same spreadsheet again, and now those same cells look fine!
So this leads me to think that Excel 2007 for causes unknown may drop into some “bad” mode and from then on un-recognize EDATE(), YEARFRAC(), and other functions like it.
I reboot almost every day (old Windows 3.1 habit), so the “mean time to failure” is not necessarily long. Of course I have not reproduced this yet — it’s hard because the #N/A failure itself occurs only rarely, but often enough to scare the bejesus out of me.
If at least I could develop a quick test on a file to ascertain if the EDATE() and YEARFRAC() cells were still ok, without having to go look for one in the N-th worksheet…
Comments, anyone?
Wednesday, 30th September, 2009 at 10:39 am |
Funnily enough I have the same problem, but have had the ATP’s installed in 2007 since the start. Note that my error does contain the # symbol. Also, I did have the “Data lost” msgbox. (I did have the “minor loss of confidality message al well when saving, but ignored that since it only involved some format stuff). Rebooting doesn’t work, but then again, I was unfortunate to discover the errors after saving.
I guess the only thing one can do is not use compatibility mode.
Thursday, 15th October, 2009 at 8:26 am |
The same problem over here also, but in some particular files only and in some particular cells. Will try the following:
a) Enable the analysis tool pak options
b) Rename the file.
Monday, 16th November, 2009 at 11:32 am |
We are getting this now. The more I work with 2007 the more rubbish I find in it. At this place we are strugging with too many styles removing all the formatting, the number format attached to the normal style being changed which MS are supposed to be giving us a fix for and now this!
What we are seeing is that when it corrupts, we get formulas appearing correctly in the formula bar but displaying as #n/a and numbers appearing correctly but the formula appearing as #n/a. Calculation is set to auto but refreshing it seems to do nothing.
Thursday, 4th February, 2010 at 9:16 pm |
I am getting this too… the underlying formula is “=#N/A”, but the value of the cell seems to work just fine in relation to other cells.
Like SA Basu, it’s only happened on some cells (not all).
Wednesday, 3rd March, 2010 at 11:39 am |
Hi there, I have the same problem with Excel2007. I have a .xls file (2003 compatible) with a NetworkingDays Formula in there and all looks fine. But then the next time I open the file I get the message that “Data might have been lost” and the cell where the NetworkingDay Formulas was display the value that was calculated but in the top the formula is =#N/A.
Will try to save as .xlsx and see if the problem persists.
Wednesday, 10th August, 2011 at 5:16 am |
I have a spreadsheet (.xls) that does an OBDC import on another (.xls) spreadsheet. No analysis toolpak functions are involved. Once in a while, Excel replaces certain array formulae with ={#VALUE!}. Cells continue to display the last calculated result.
Monday, 19th December, 2011 at 5:01 pm |
I have the same ATP problem, and after scratching around one article indicated that Microsoft has a fix for this, after which I found the following articles – I can’t vouch for them yet as only time will tell, but for those that want to check out and try the official fix:
General desc
or go straight to the download at
PS – my experience doesn’t agree 100% with microsoft’s description of the syptoms, but are close enough for me.
Monday, 19th December, 2011 at 5:04 pm |
Sorry, the links seem to have been removed, trying again:
http://support.microsoft.com/kb/2451764
and
http://support.microsoft.com/kb/973932/%5BanySimpleType%5D