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
