File Error: Data May Have Been Lost

[Guest]

I've been building a 40 meg, 40 tab, workbook that contains macros, named
ranges, vlookup, etc. for over a year. Just last week, I started receiving a
message on the latest version of the workbook stating File Error: Data May
Have Been Lost when opening the file. Nothing had changed, except the name
of the file which was renamed for the day's date and a few of the input
cells. I found the error pretty quickly...a formula in a couple of cells was
totally replaced with "#REF". I copied the formulas from adjacent cells and
all looked fine. I renamed the file and went about my business.

Since then, that file is fine, but I've received numerous errors that are
similar on later and on previous versions of the file. Also, now when I open
one that is not corrupt and try to save it as a new file name, excel crashes
altogether.

This is a very complex spreadsheet and I'd hate to have to rebuild. Anyone
know what the problem might be? Decreasing the file size is not an option in
this situation.

Thanks,
Teri

 

[Guest]

tj, by chance do you have workday formulas with holidays in your spreadsheet?
I recently upgraded to Office 2007, and my other Office applications seem to
work fine, but my transition to Excel 2007 has been very challenging! My
lookup tables seem to be OK now, but my workday formulas are causing a
problem. Upon opening a file with the workday formulas, I receive the "File
Error: Data May Have Been Lost" error, and then I discover that my workday
formulas have been replaced with "=N/A". The reference is completely lost.

 

[Guest]

No, but I did finally determine the cause. My computer's RAM went bad
apparently.

Thanks,
Teri

 

[Sean]

Davey;
I do have the same problem. I use Holidays in some of the Networkdays()
calculations, but all such calculations are being replaced with #N/A, whether
or not Holidays are being used in that particular formula. It's a serious
problem for me. I can't trust my work to Excel 2007.
Sean

 

[DAVEY]

Sean, I have found a work-around for my problem.
1st of all I got tired of updating the thousands of cells each year that
contained holidays. I always had last year's, current year's, and next
year's holidays in each cell, which numbered in the thousands on 1 particular
spreadsheet. I used the Search 'n Replace feature, but I had to do it on
several spreadsheets--provided I remembered to do this on every file that
used them. Also, manually typing 3 years' worth of holidays for the FIND &
then for the REPLACE leaves room for error.
I got the idea to maintain one separate file that had these dates in it
(some MS Access training gave me that idea), and then using VLOOKUP formulas,
have each workday refer to the holiday spreadsheet.
After much trial & error, I finally got everything to work right 100% of the
time.

#1 I have to open my Holiday file before opening anything else.
#2 I have to open any other files that my main spreadsheet links to. (I
have thousands of lookup formulas in my files.)
#3 I PW-protected (actually encrypted w/ a PW) the spreadsheet so that no
one could open it.
#4 I made sure that anyone to whom I gave access to the main spreadsheet
installed their Add-Ins (Analysis ToolPak, Analysis ToolPak-VBA, Conditional
Sum Wizard, Lookup Wizard, & Solver Add-in).
#5 Make sure all files linked with each other are Excel 2007 (*.xlsx) files.

Since following these steps, I no longer have this problem. Step 1 is the
key to keeping the holiday formulas from being lost. Everyone accessing the
main file must open the holiday file too.

(For a time I got the idea to simply make my holiday file tab/sheet #2 on my
main spreadsheet. Other linked spreadsheets were also added as tabs on the
main file. This ensured that everything would be open. That works, but it's
a huge pain if you need to update those subsequent tabs on a regular basis.
You have to move (not copy) them from the main spreadsheet, update the info,
and then move it back. There are more things involved in doing this, but
that's the gist of it. I would not recommend doing this because it gets
cumbersome.)

Hopefully, some or all of these will help you out with your specific
situation. If not, let me know, and I'll see if there's something else I can
suggest that may help.

 

[Sean]

Wow - I can't say I really understand what it is you are trying to do, but it
seems to me life would be much simpler if Excel worked the way it should (or
at least used to). As for my own problems, I have been working a bit more in
Excel 2007, and have not experienced the Networkdays() glitch for a couple of
days, but I'm not breathing easy yet. I was also having a severe problem with
linking tables to Word-doc's (using named-ranges) and that has also been
behaving lately - for no apparent reason!

Regards,
Sean

 

[Sean]

IT'S BAAAAAAAAACK!

Excel 2007 has started misbehaving again - My Networkdays() formulae are now
being changed to =#N/A - I wish there was a solution for this.

Sean

 

[Alan]

Sean, I have found a work-around for my problem.
1st of all I got tired of updating the thousands of cells each year that
contained holidays.  I always had last year's, current year's, and next
year's holidays in each cell, which numbered in the thousands on 1 particularspreadsheet.  I used the Search 'n Replace feature, but I had to do it on
several spreadsheets--provided I remembered to do this on every file that
used them.  Also, manually typing 3 years' worth of holidays for the FIND &
then for the REPLACE leaves room for error.
I got the idea to maintain one separate file that had these dates in it
(some MS Access training gave me that idea), and then using VLOOKUP formulas,
have each workday refer to the holidayspreadsheet.
After much trial & error, I finally got everything to work right 100% of the
time.

#1 I have to open my Holiday file before opening anything else.
#2 I have to open any other files that my mainspreadsheetlinks to.  (I
have thousands of lookup formulas in my files.)
#3 I PW-protected (actually encrypted w/ a PW) thespreadsheetso that no
one could open it.
#4 I made sure that anyone to whom I gave access to the mainspreadsheet
installed their Add-Ins (Analysis ToolPak, Analysis ToolPak-VBA, Conditional
Sum Wizard, Lookup Wizard, & Solver Add-in).
#5 Make sure all files linked with each other are Excel 2007 (*.xlsx) files.

Since following these steps, I no longer have this problem.  Step 1 is the
key to keeping the holiday formulas from being lost.  Everyone accessingthe
main file must open the holiday file too.

(For a time I got the idea to simply make my holiday file tab/sheet #2 on my
mainspreadsheet.  Other linked spreadsheets were also added as tabs on the
main file.  This ensured that everything would be open.  That works, but it's
a huge pain if you need to update those subsequent tabs on a regular basis..  
You have to move (not copy) them from the mainspreadsheet, update the info,
and then move it back.  There are more things involved in doing this, but
that's the gist of it.  I would not recommend doing this because it gets
cumbersome.)

Hopefully, some or all of these will help you out with your specific
situation.  If not, let me know, and I'll see if there's something else I can
suggest that may help.
--
§ DAVEY §






- Show quoted text -

Hi,

If all the ways are not impossible. You may try a popular Excel file
recovery tool called Advanced Excel Repair to repair your Excel file.
It is a powerful tool to repair corrupt or damaged Excel files.

Detailed information about Advanced Excel Repair can be found at
http://www.datanumen.com/aer/

And you can also download a free demo version at http://www.datanumen.com/aer/aer.exe

Alan

 

[Ian Robert]

Hi,

I have a client with a spreadsheet that keeps "corrupting" like this as you
describe.
Running on Windows Vista, Office 2007. On Opening the file the error is
"File Error : Data may have been lost" and the formula "Eomonth() " gets
replaced with "=#N/A".

I wasn't sure what was causing this, so I copied all the worksheets to a new
workbook and saved that as a new file. The error went away and then replaced
all the =#N/A with the correct formula's and saved again. The file opened a
couple of times ok...but then the error has returned in this new file.

It would seem there are definate problems with Excel 2007.

Regards

Ian