Home

Why does the #REF error remove formulas irretrievably? How to find lost references?

%3CLINGO-SUB%20id%3D%22lingo-sub-775000%22%20slang%3D%22en-US%22%3EWhy%20does%20the%20%23REF%20error%20remove%20formulas%20irretrievably%3F%20How%20to%20find%20lost%20references%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775000%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Community%2C%3CBR%20%2F%3EI%20am%20frustrated%20beyond%20measure%20over%20the%20infamous%20%23REF%20Error.%20Up%20to%20now%20I%20was%20%22only%22%20frustrated%20over%20having%20my%20formulas%20turned%20from%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D'%5BFilename.xlsx%5DSheetname'!CellReference%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Einto%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D'%5BFilename.xlsx%5DSheetname'!%23REF%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eon%20a%20frequent%20basis%2C%20which%20i%20consider%20one%20of%20the%20worst%20fails%20of%20excel%2C%20since%20any%20tiny%20hick%20in%20network%20connection%20causes%20this%2C%20and%20excel%20is%26nbsp%3B%3CBR%20%2F%3E1)%20%3CSTRONG%3E%3CSPAN%3Eincapable%20of%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3Edealing%20with%20it%20automatically%20(by%20%2C%20for%20example%2C%20ignoring%20it%20during%2010%20seconds%20or%20undoing%20the%20error%20once%20the%20network%20interrupt%20is%20ove)%20and%3C%2FP%3E%3CP%3E2)%3CSTRONG%3Esaving%20this%20string%20as%20formula%3C%2FSTRONG%3E%2C%20removing%20original%20information%20from%20the%20sheet.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EBut%20now%20I%20am%20staggered.%3C%2FP%3E%3CP%3Enow%20the%20error%20cause%20a%20formula%20like%3CBR%20%2F%3E%3D'%5BFilename.xlsx%5DSheetname'!CellReference%3CBR%20%2F%3Eto%20turn%20into%3CBR%20%2F%3E%3D%23REF!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EWTF%3F!%3F%20how%20am%20i%20supposed%20to%20fix%20that%20%23REF%20error%3F%20%3CSTRONG%3EHow%20dares%20Excel%20overwriting%20my%20formulas%20and%20leaving%20no%20trace%20of%20the%20previous%20content%3F%3C%2FSTRONG%3E%26nbsp%3B%20Do%20you%20have%20any%20suggestion%20how%20I%20can%20recover%20the%20original%20formula%3FHow%20i%20can%20prevent%20this%20from%20occuring%3F%20It%20kills%20rediculous%20amounts%20of%20my%20work%20time.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-775000%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eref%20error%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775012%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20does%20the%20%23REF%20error%20remove%20formulas%20irretrievably%3F%20How%20to%20find%20lost%20references%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775012%22%20slang%3D%22en-US%22%3EYou%20may%20refer%20to%20this%20link%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fhow-to-fix-the-ref-error%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fhow-to-fix-the-ref-error%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775014%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20does%20the%20%23REF%20error%20remove%20formulas%20irretrievably%3F%20How%20to%20find%20lost%20references%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775014%22%20slang%3D%22en-US%22%3E%3CP%3Enope%2C%20this%20is%20not%20an%20answer%20to%20my%20question%3A%3CBR%20%2F%3E%22The%20%23REF!%20error%20occurs%20when%20a%20reference%20is%20invalid.%20In%20many%20cases%2C%20this%20is%20because%20sheets%2C%20rows%2C%20or%20columns%20have%20been%20removed%2C%20or%20because%20a%20formula%20with%20relative%20references%20has%20been%20copied%20to%20a%20new%20location%20where%20references%20are%20invalid%22%3CBR%20%2F%3Eall%20this%20is%20not%20the%20cause%20of%20my%20%23REF%20error%2C%20explicitly%20including%20adding%20%2F%20removing%20cells%2Fcolums%2Frows.%20none%20of%20the%20proposed%20methods%20bring%20back%20my%20lost%20formulas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775275%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20does%20the%20%23REF%20error%20remove%20formulas%20irretrievably%3F%20How%20to%20find%20lost%20references%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775275%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382663%22%20target%3D%22_blank%22%3E%40Excelist%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20your%20formula%20exactly%20as%20you%20mentioned%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3D'%5BFilename.xlsx%5DSheetname'!CellReference%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eor%20it's%20part%20of%20formulas%20like%3C%2FP%3E%0A%3CP%3E%3DSUM(%3CSPAN%3E'%5BFilename.xlsx%5DSheetname'!CellReference1%2C'%5BFilename.xlsx%5DSheetname'!CellReference2)%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776989%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20does%20the%20%23REF%20error%20remove%20formulas%20irretrievably%3F%20How%20to%20find%20lost%20references%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776989%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Einteresting%20that%20you%20ask.%3CBR%20%2F%3EI%20definately%20have%20both%20kinds%20in%20my%20sheet%2C%20but%20those%20that%20I%20managed%20to%20recreate%20were%20all%20of%20the%20first%20kind%2C%20i.e.%20a%20link%20on%20its%20own%2C%20not%20part%20of%20a%20surrounding%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781309%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20does%20the%20%23REF%20error%20remove%20formulas%20irretrievably%3F%20How%20to%20find%20lost%20references%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781309%22%20slang%3D%22en-US%22%3E%3CP%3Eupdate%3A%20it%20definately%20happens%20bit%20both%20kinds%20of%20formulas.%3C%2FP%3E%3CP%3EAnd%20it%20does%20so%2C%20if%20the%20referenced%20workbook%20is%20open.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20hate%20Excel%20for%20this%20behavior.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Excelist
New Contributor

Dear Community,
I am frustrated beyond measure over the infamous #REF Error. Up to now I was "only" frustrated over having my formulas turned from

='[Filename.xlsx]Sheetname'!CellReference

into

='[Filename.xlsx]Sheetname'!#REF

 

on a frequent basis, which i consider one of the worst fails of excel, since any tiny hick in network connection causes this, and excel is 
1) incapable of dealing with it automatically (by , for example, ignoring it during 10 seconds or undoing the error once the network interrupt is ove) and

2) saving this string as formula, removing original information from the sheet.

But now I am staggered.

now the error cause a formula like
='[Filename.xlsx]Sheetname'!CellReference
to turn into
=#REF!


WTF?!? how am i supposed to fix that #REF error? How dares Excel overwriting my formulas and leaving no trace of the previous content?  Do you have any suggestion how I can recover the original formula?How i can prevent this from occuring? It kills rediculous amounts of my work time.


Thanks.


 

 

 

5 Replies

nope, this is not an answer to my question:
"The #REF! error occurs when a reference is invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where references are invalid"
all this is not the cause of my #REF error, explicitly including adding / removing cells/colums/rows. none of the proposed methods bring back my lost formulas

@Excelist 

Is your formula exactly as you mentioned

='[Filename.xlsx]Sheetname'!CellReference

or it's part of formulas like

=SUM('[Filename.xlsx]Sheetname'!CellReference1,'[Filename.xlsx]Sheetname'!CellReference2) 

@Sergei Baklan 

interesting that you ask.
I definately have both kinds in my sheet, but those that I managed to recreate were all of the first kind, i.e. a link on its own, not part of a surrounding formula

update: it definately happens bit both kinds of formulas.

And it does so, if the referenced workbook is open.

 

I really hate Excel for this behavior.