File Error: Data may have been lost

[brunette]

Hello everyone,

I am hoping you can shed some light on a hiccup I'm encountering, that's messing with my Excel in a very big way. I have monthly reports due out next week and suddenly, today I am getting a message saying "File Error: Data may have been lost" on one of my files. Ordinarily, it wouldn't be a big deal, except for the fact that I've written a gigantic macro that pretty much automates my entire report generation and, therefore, frees me from having to scrutinse all that data. If it's going to keep causing an error, I might as well scrap the entire macro and just do the entire thing manually, since I'd have to go back and check everything anyway - which I *reeeaalllly* don't want to have to do as it's almost an entire day's worth of boring, repetetive work...!

When I open the file, one of the formulas I have in the document has been replaced with #N/A. The rest of the file and its contents are otherwise fine.

I've done some poking around and found this thread, which is EXACTLY what's happening to me right now. Sadly, there was no response, so I couldn't benefit from anything there:

http://www.mrexcel.com/forum/showthread.php?t=358646

I also asked my "Uncle Google" and he says it has to do with the fact that the formula used is an analysis tookpack formula. Which is fair enough. Only problem is that I can't find anything telling me how to FIX it. Either that or another formula that will return the same reply.

The formula in question is a formula to automatically calculate the previous month. This is how it SHOULD look like:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Novembro 11</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Abertas</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Fechadas</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pendentes</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td></tr></tbody></table><p style="width:7,2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Totais_Grupo</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">EOMONTH(<font color="Green">NOW(<font color="Purple"></font>),0</font>)</font>),MONTH(<font color="Red">EOMONTH(<font color="Green">NOW(<font color="Purple"></font>),0</font>)</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E7</th><td style="text-align:left">=D7-F7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F7</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">VLOOKUP(<font color="Green">"ADMINISTRACION",Pendentes!$A:$C,2,FALSE</font>)</font>),0,VLOOKUP(<font color="Red">"ADMINISTRACION",Pendentes!$A:$C,2,FALSE</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E8</th><td style="text-align:left">=SUM(<font color="Blue">E7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F8</th><td style="text-align:left">=SUM(<font color="Blue">F7</font>)</td></tr></tbody></table></td></tr></table><br />

This is what happens after I open the file after the error:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Abertas</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Fechadas</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pendentes</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td></tr></tbody></table><p style="width:7,2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Totais_Grupo</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=#N/A</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E7</th><td style="text-align:left">=D7-F7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F7</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">VLOOKUP(<font color="Green">"ADMINISTRACION",Pendentes!$A:$C,2,FALSE</font>)</font>),0,VLOOKUP(<font color="Red">"ADMINISTRACION",Pendentes!$A:$C,2,FALSE</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E8</th><td style="text-align:left">=SUM(<font color="Blue">E7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F8</th><td style="text-align:left">=SUM(<font color="Blue">F7</font>)</td></tr></tbody></table></td></tr></table><br />

So I guess my question is:

If the problem is with the analysis tookpack formula, is there another way of obtaining the same result via a formula that does not require the use of the analysis tookpack?

If not, how to I overcome this?

As always, your help in this is greatly appreciated and I wish you all the very best for the New Year.

Beste regards,
Ana

 

[VoG]

Hello. If that was an error with the Analysis Toolpak I would expect to see #Name not #N/A.

Which version of Excel are you using?

 

[brunette]

Thanks for the super quick reply Vog.

I was just about to edit my message to include that I'm using Excel 2007. Sorry about that!

 

[VoG]

I suspect that the workbook is damaged. Try this with a copy of the workbook http://office.microsoft.com/en-us/excel-help/repairing-a-corrupted-workbook-HA010097017.aspx

 

[brunette]

Peter, thanks again for a lightning-fast response. I tried the file repair using the Office link you provided but it still displays the same error message. I tried both "repair" and "extract data" but neither works.

Help?


ps. Just thought of something which may or may not have anything to do with why this is happening.

The file that is producing an error is created via a macro that automatically appends the previous month's year and date to the end of the file name using this command:

ActiveWorkbook.SaveAs Filename:="H:\LI222G083\NÍVEIS SERV DTInformática\TRABALHO\Macros em Desenvolvimento\Quadro Inc " & Format(DateSerial(Year(Now), Month(Now) - 1, 1), "mmmm yyyy") & ".xls"

The code is fine and is creating a filename with the correct month etc. If I open the original file (where I get data from and make changes to before saving with the new filename) it does NOT produce an error, even though I have the same formula in that workbook too.

The odd thing is that I spent the afternoon yesterday testing the macro and it was all working fine with no errors on either file. It only started today. I've run the macro a few times and each time it saves the file with the "-1" month in the filename, it errors.

Thanks again,
Ana

 

[VoG]

You could try rebuilding the workbook http://www.mrexcel.com/forum/showthread.php?t=223851

 

[brunette]

I can do that. Will bash about with it and give u feedback as soon as I'm done.

Regs,
Ana