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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Roger Nye" <RogerNye@discussions.microsoft.com> wrote in message news:7FA33CA3-5F37-4EB8-B276-1D8CBAA4F150@microsoft.com...
|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

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


Roger Nye wrote:
>
> 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

--

Dave Peterson

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


"Niek Otten" wrote:

> Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Roger Nye" <RogerNye@discussions.microsoft.com> wrote in message news:7FA33CA3-5F37-4EB8-B276-1D8CBAA4F150@microsoft.com...
> |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
>
>
>

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

"Dave Peterson" wrote:

> 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
>
>
> Roger Nye wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

OK, I find this always worth trying, but something else seems to be going on.
Follow Dave's advice

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Roger Nye" <RogerNye@discussions.microsoft.com> wrote in message news:67FF93DC-B443-419E-9F6E-1A34B38DD9A9@microsoft.com...
| 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
|
|
| "Niek Otten" wrote:
|
| > Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > "Roger Nye" <RogerNye@discussions.microsoft.com> wrote in message news:7FA33CA3-5F37-4EB8-B276-1D8CBAA4F150@microsoft.com...
| > |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
| >
| >
| >

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


Roger Nye wrote:
>
> 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
>
> "Dave Peterson" wrote:
>
> > 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
> >
> >
> > Roger Nye wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave 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


"Dave Peterson" wrote:

> 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).
>
>
> Roger Nye wrote:
> >
> > 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
> >
> > "Dave Peterson" wrote:
> >
> > > 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
> > >
> > >
> > > Roger Nye wrote:
> > > >
> > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

It sure looks like you have a corrupted workbook.
Did you follow the hints in the links Dave gave you?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Roger Nye" <RogerNye@discussions.microsoft.com> wrote in message news:BC3A65F2-2EDE-4D30-BF51-A2F6367D375C@microsoft.com...
|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
|
|
| "Dave Peterson" wrote:
|
| > 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).
| >
| >
| > Roger Nye wrote:
| > >
| > > 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
| > >
| > > "Dave Peterson" wrote:
| > >
| > > > 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
| > > >
| > > >
| > > > Roger Nye wrote:
| > > > >
| > > > > 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
| > > >
| > > > --
| > > >
| > > > Dave Peterson
| > > >
| >
| > --
| >
| > 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


"Niek Otten" wrote:

> It sure looks like you have a corrupted workbook.
> Did you follow the hints in the links Dave gave you?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Roger Nye" <RogerNye@discussions.microsoft.com> wrote in message news:BC3A65F2-2EDE-4D30-BF51-A2F6367D375C@microsoft.com...
> |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
> |
> |
> | "Dave Peterson" wrote:
> |
> | > 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).
> | >
> | >
> | > Roger Nye wrote:
> | > >
> | > > 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
> | > >
> | > > "Dave Peterson" wrote:
> | > >
> | > > > 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
> | > > >
> | > > >
> | > > > Roger Nye wrote:
> | > > > >
> | > > > > 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
> | > > >
> | > > > --
> | > > >
> | > > > Dave Peterson
> | > > >
> | >
> | > --
> | >
> | > Dave Peterson
> | >
>
>
>