Forum Discussion

Excelist's avatar
Excelist
Copper Contributor
Jul 26, 2019

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 =...
excel
Formulas and Functions
ref error
  • Excelist's avatar
    Excelist
    Copper Contributor
    Jul 26, 2019

    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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 26, 2019

      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) 

      • Excelist's avatar
        Excelist
        Copper Contributor
        Jul 29, 2019

        SergeiBaklan 

        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

Resources