Forum Discussion
Why does the #REF error remove formulas irretrievably? How to find lost references?
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
- You may refer to this link:
https://exceljet.net/formula/how-to-fix-the-ref-errornope, 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 formulasIs 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)
