 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2007Most formulae replaced by #NA
I just opened a worksheet that has no external links and the majority (80-90%) of the formulae have been replaced by #NA - basically my work has been destroyed. This has happened before with another sheet. Both were large and complex sheets. Any idea what might be causing this or how to prevent it in future? I am using Excel 2007 and both files were saved as an xlsx file. Thanks Roger Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9  SignatureKind regards, Niek Otten Microsoft MVP - Excel |I just opened a worksheet that has no external links and the majority | (80-90%) of the formulae have been replaced by #NA - basically my work has [quoted text clipped - 9 lines] | Thanks | Roger Thanks Niek, but I am not sure I understand. It causes it to recalculate, but doesn't change anything. The formulae still show =#NA. Best regards Roger > Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9 > [quoted text clipped - 11 lines] > | Thanks > | Roger OK, I find this always worth trying, but something else seems to be going on. Follow Dave's advice SignatureKind regards, Niek Otten Microsoft MVP - Excel | Thanks Niek, | but I am not sure I understand. [quoted text clipped - 18 lines] | > | Thanks | > | Roger Do you mean that the formulas have been converted to values (#n/a values)? Or that the formulas just don't evaluate correctly? If the formulas are being converted to values, check to see what addins you have running. I bet that there's a misbehaving addin that's doing the harm. Well, if you didn't convert them to values yourself. Here are a couple of sites that may help you do the detective work: Chip Pearson's: http://www.cpearson.com/excel/StartupErrors.aspx Jan Karel Pieterse's: http://www.jkp-ads.com/Articles/StartupProblems.asp > I just opened a worksheet that has no external links and the majority > (80-90%) of the formulae have been replaced by #NA - basically my work has [quoted text clipped - 9 lines] > Thanks > Roger SignatureDave Peterson Hi Dave, Unfortunately I mean the formula is literally replaced by =#NA The active add-ins installed are: - Analysis Toolpak - Analysis Toolpak VBA - Conditional Sum Wizard - Solver Add-in Do any of these look like good candidates to remove? Thanks Roger > Do you mean that the formulas have been converted to values (#n/a values)? Or > that the formulas just don't evaluate correctly? [quoted text clipped - 24 lines] > > Thanks > > Roger Nope. Did you see these addins under Tools|Addins (in xl2003 menu system). Or did you see them in the Project explorer in the VBE? You may want look for COM addins, too. In excel, you can do this to see what's installed: Tools|Customize|Commands tab|Tools Category Scroll down to "COM Add-Ins..." and drag it to your favorite toolbar. (I put it below Add-Ins under the Tools menu (still xl2003)) Then check to see what's installed here. If I recall correctly, there was an addin that was associated with another program (either mainframe or AS400 related <maybe????>) that did this for another poster--but I've forgotten the details (sorry). > Hi Dave, > Unfortunately I mean the formula is literally replaced by =#NA [quoted text clipped - 42 lines] > > > > Dave Peterson SignatureDave Peterson I saw them in Office Button|"Excel Options"|Add-Ins (I am using Excel 2007). There are no COM add-ins. There are many inactive add-ins, but I presume these are not relevant and anyway they are all in the Program Files\Microsoft Office directory, so I guess they are pukka. Could it simply be that Excel 2007 is full of bugs? It certainly doesn't behave predictably (or at least I can't understand it). For one thing, it wants to recalculate for 30 seconds, even if I just change a single cell that doesn't affect any others. Many thanks Roger > Nope. > [quoted text clipped - 61 lines] > > > > > > Dave Peterson It sure looks like you have a corrupted workbook. Did you follow the hints in the links Dave gave you? SignatureKind regards, Niek Otten Microsoft MVP - Excel |I saw them in Office Button|"Excel Options"|Add-Ins (I am using Excel 2007). | [quoted text clipped - 76 lines] | > > > | > > > Dave Peterson Hi Niek, I tried running Excel in safe mode and detect and repair. The result was unchanged. This afternoon I have recreated the workbook, so now I don't need a fix to restore the workbook (although I wouldn't mind a fix to restore my Sunday afternoon - start the weekend in safe mode). What worries me is that this is the second time this has happened on entirely independent workbooks. So I have to wonder how long before it happens again. Thanks both of you for the suggestions. Best regards Roger > It sure looks like you have a corrupted workbook. > Did you follow the hints in the links Dave gave you? [quoted text clipped - 79 lines] > | > > > > | > > > Dave Peterson I'd check again. But I don't have any more guesses. > I saw them in Office Button|"Excel Options"|Add-Ins (I am using Excel 2007). > [quoted text clipped - 80 lines] > > > > Dave Peterson SignatureDave Peterson
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.