In my workbook A, I have formulas that reference my workbook B. Once I open workbook B and enable editing, all of the formulas in workbook A show workbook B's worksheet name as #REF (but the filepath and cell references remain). This is incredibly frustrating since I must manually change all of these #REF's back to the worksheet names every single time I want to update workbook B.

This isn't the case for some other workbooks that I have that reference external workbooks. In those cases, when I open the referenced workbook, I will still get errors, but it won't physically change the formulas. Then, once I am done editing the referenced workbook, I can just go and click "Update Values" in the "Edit Links" dialog box.

What is different between these two situations? How can I change the first situation I described into the second situation? Why does Excel physically change the formulas if the structure of the referenced workbook doesn't change?

I do not want to use INDIRECT() formulas as the ranges that I am referencing in the external workbook may change.

  • Has the worksheet name changed? Some formulas like vlookup are tolerant of changes, some like countif are not.
    – Absinthe
    Aug 17 '18 at 21:54
  • To avoid the problem Use INDIRECT() function on textually referenced File and Sheet names. For example you are using,,C:\Folder\[A.xls]!Sheet1'!A1 and you are getting C:\Folder\[A.xls]!#REF'!A1. Now store File Path, WB & Sheet name in a cell and rewrite the formula,, =INDIRECT(D1&"!A1") will help you. Aug 18 '18 at 8:32
  • ,,cont.,, Or better share sample data & the Formula with us !! Aug 18 '18 at 8:33

Once I've solved the same issue by use of the following method:

  • From the File Menu click Option.
  • From Option list Click Trust Center.
  • Click Trust Center Settings.
  • In Trust Center window click Protected View.
  • Then Un-tick Enable Protected View for files located in unsafe locations.

This setting change will solve #REF! error problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.