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.