I lost all the formulas in an excel file

[WhiteTornado]

Hi,

I created an excel file a while back (several months) which had some great and long formulas with many "if" etc... To my surprise, my client trying to use this file today tells me that the file is no longer working. Originally I had one sheet on which I had several tables from which my formulas were looking in, I had this sheet hidden by using its properties. Now there is no formulas in any of the cells which contained them. I tried "Excel Recovery" to give it a shot, but that did not produce the formulas either. I thought this might be caused the client upgraded from Office 97 to 2003. But, in trying to look up the file on an other machine using still 97, I get the same result. I tried, using a backed up file on my own laptop, using Open Office, it is not seeing them either, they are gone, cells are set to the "standard" format. I am looking for the earliest backups I might have to test this out. But, if there is some actions I can do I would like to know.

Cheers!

 

[xlhelp]

Without knowing what was done to the file, it's hard to diagnose.

The formulae may be hidden. Try unprotecting the sheet to see if they re-appear.

 

[WhiteTornado]

Hi,
I understand, fact is nothing was done to it, it was stored on my external drive, I also had backups on DVD, which I just looked at, all the formulas are gone as well. The sheet was not protected.

 

[KenWright]

Sounds like the data was either copied and paste specialed as values, or someone used Edit / Links / Break Links,a nd probably some time ago if your backups are the same. Failing that if the file had gone corrupt at any time and Excels recovery feature kicked in, it may have recovered only values.

Either way, formulas with many IF statements in are usually poorly written formulas that are inefficient anyway. VLOOKUP or INDEX/MATCH etc are 'usually' much better than multiple IFs.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[WhiteTornado]

I found the original version of this file, uncorrupted and fully working. That's a relief! Right about the "if", I used VLOOKUP on that file, that worked great.