Data Loss when Excel is Reopened - #N/A in place of formulas

[m24jones]

When I reopened a large excel file, I received an error message that data was lost. In the file many formulas were converted to #N/A. Any idea how this happened? What can I do to prevent this from happening again?

Thanks.

 

[shemayisroel]

What version of excel are you using?

Did excel previously crash before you were able to save data?

When you go to tools, options, save tab what do you have your save auto recovery every how many minutes?

 

[m24jones]

Thanks for the response.

I'm using Excel 2007, but saving the file in the 97-2003 .xls format.

This has happened before, but the program is not crashing. I'm doing a clean save, close and reopen. I do have autosave on, set at every 2 minutes.

The #N/A replaces almost every IRR function in the workbook, plus others in a seemingly random way.

 

[shemayisroel]

m24jones

Sorry can't help any further on this one, not sure why as the IRR function is also present in 2003.

Don't know much about xls 2007 but since you have supplied additional info perhaps someone else could shed some extra light.

Does your IRR formula still work?

 

[m24jones]

The IRR formula works after I re-input the formula. Quite a pain, and a complete mystery.

 

[shemayisroel]

The IRR formula works after I re-input the formula. Quite a pain, and a complete mystery.

see if your calculations option is on auto...

 

[m24jones]

I have the calculation set on iteration. There are circular equations in the model. Do I need to switch this back to auto calculation each time I go to close the file?

 

[xenou]

One trick is to:
Do a find and replace on equals signs.

Find:
=
Replace with (look in formulas):
=

This forces a recalculation, as you do when re-entering the formulas, such as with F2 and Enter.

I can't say why exactly this is happening to you though....