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
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
Last edited: