11

This has happened to me multiple times (but not always). It is very frustrating and I have no idea why it does this.

A.xls contains formula that links to B.xls Sheet1. I am working on A.xls. I decide to open B.xls. Once I do - all references to Sheet1 turns into #REF.

ie.

='C:\..\[B.xls]!Sheet1'!A1

turns into

='C:\..\[B.xls]!#REF'!A1

and naturally all values that were previously correct now returns #REF.

This is absolutely frustrating as there is no way for me to undo it without re-opening my last saved version of A.xls. Find and replace is not ideal either because A.xls refers to several different worksheets of B.xls - and they're all now #REF.

Any ideas why? :( Running Excel 2010.

Improve this question

13 Answers 13

Reset to default
3

I got the same problem. mine is office 2010. the excel file was store in share drive (NAS).

Solution: Go to File > Option > Trust Center. On right side, Click Trust Center Settings. In Trust Center window, on left side, click Protected View then on right side untick Enable Protected View for files originating from the Internet.

Now my #REF! problem solved.

Improve this answer
2

One way to avoid the problem:
Use INDIRECT() on textually referenced File and Sheet names.

Example:
Make Z1 be 'C:\..\[B.xls]!Sheet1' (with the quotes? Untested)
Then use =INDIRECT(Z1&"!A1") where you need to reference cell A1 in that sheet.

The INDIRECT function only works if you have the secondary Excel file opened. Once closed, all those INDIRECT references become #REF.

Improve this answer
8
  • 1
    INDIRECT will only work with open work books
    – PeterH
    Oct 6, 2017 at 15:56
  • But they DO NOT turn into "#REF".
    – Hannu
    Oct 7, 2017 at 8:31
  • Yes it will once you close the other workbook, try it for yourself
    – PeterH
    Oct 7, 2017 at 8:38
  • It will possibly DISPLAY "#REF", but the formula, with the link, will not change as OP described.
    – Hannu
    Oct 7, 2017 at 8:39
  • 1
    Excel has a lot of idiosyncrasies, that is true.
    – Hannu
    Oct 7, 2017 at 8:49
0

Most formulas in excel do not update their formulas if they refer to closed workbooks. Now, if you open a workbook the formulas refer to, excel will try to update it immediately.

Your problem I believe is that changes have been made to B.xls and the formulas in A.xls are trying to refresh its formulas when you opened B.xls. Because there has been changes in B.xls from since the formulas in A.xls were last calculated (such as sheets were renamed or plainly deleted), the formulas in A.xls cannot find the previous reference, and so return #REF!.

I think you can have two options to avoid this:

  1. Remove the formulas in A.xls by copy/pasting values. This will prevent any updates in A.xls from changes in B.xls and any other formulas in the sheet. This is a downside in itself though.
  2. Make sure any changes to B.xls does not involve sheet deletion, renaming or row/column deletion in those that supply the formulas in A.xls.
Improve this answer
1
  • Thanks for the response, Jerry. The sheets that were referred still exists. Also, most of my reference were simply to the value without so much as an arithmetic operation. Excel managed to get the values even with B.xls closed. Thankfully it does not happen most of the time - but for whatever luck I have the times that it does, I hadn't saved the workbook in a while.
    – csg
    Aug 8, 2013 at 18:12
0

To help with Jerry's comment. When you close A.xls it will remember the values in it from the formula. If you then alter B.xls in anyway such as workbook name or sheet name, then when A.xls tries to update itself with the new data it cannot be found because the referenced name has changed.

Say your formula is something like ='[B.xls]Sheet3'!$B$1588 if you change the name of B.xls after closing A.xls to "Data Saved 08.09.2013" then when you open A.xls it will have the values from B.xls. As soon as you open Data Saved 08.09.2013 then A.xls will realize that the data does not exist anymore and lose it.

The work around for this is to make sure that you update the links in the formula. This should solve it all.

Improve this answer
2
  • In my case it happened after I update the links. I changed the reference from B.xls to C.xls - workbooks of the same structure just different values.
    – csg
    Aug 9, 2013 at 14:08
  • When you updated the links did you also update the sheet names? I know this may sound a bit condescending but, we have to ask.
    – Bmize729
    Aug 11, 2013 at 22:45
0

The solution can be to open the file where you have given the links and then go to Data > Edit Links > Open Source. This will open the source without making the #ref error.

Another alternative is to keep two versions with and without the links. The version without links can be made by Data > Edit Links > Break Link.

I think it is some kind of a bug in excel which makes the sheet name #ref.

Improve this answer
0

The problem lies in the trust center. If you work on networkdrives you have to check "allow connections to trusted locations". This avoids losing the link.

Improve this answer
0

It seems that the links are changed to #REF! because the target spreadsheet (B.xls) is not trusted. By going to FILE->Options->Trust Center->Trust Center Settings-Trusted Locations and adding the folder containing B.xls I was able to make the problem stop happening. Presumably you could add B.xls to "Trusted Documents" and get the same result but I haven't run that test.

Improve this answer
1
  • Thanks to user524256 who's answer set me on the right path.
    – Greg B
    Jan 27, 2016 at 16:17
0

Change the cell format to numeric may be from text/others listed in the cell formats list. This can be achieved by right clicking on the cell or selected cells in Excel.

Improve this answer
0

In mine case I had similar problem that formula broke each time when I opend Excel. Formula was referenced to a table in another sheet in the same excel file.

The problem was solved when I deleted the space in front of the column name of the table on which formula was referenced.

I am not sure if the problem is the same as yours but maybe helps..

Improve this answer
0

Picking up useful pointers from those above, this worked for me for files with referencing errors when the linked file on our network was opened:

Options > Trust Centre > Trust Centre Settings > Trusted Locations > Check the box alongside "Allow Trusted Locations on my network (not recommended).

Trust errors have been fixed!

Thank you.

Improve this answer
0

Apart from the 'Allow Trusted Locations on my network (not recommended)' setting, check that the Excel File Types are both the newer file type '.xlsx'. We ad an issue were a '.xls' spreadsheet was getting #REF issues when looking up a '.xlsx' spreadsheet.

Improve this answer
0
-1

The solution is to go to File > Options > Formulas and uncheck Data entered in a table is invalid.

Improve this answer
-1

To be explicit, I have replicated the following issue:

"#REF" replaces "Sheet" in formulas to an external workbook.

Conditions:

  1. Open file (in directory that is not listed as a Trusted Location) containing links, remain in Excel's Protected View (don't click Enable Editing).
  2. Open the other file that is being referenced (remain in Protected View for this file too).
  3. (While referenced file is still in protected view): 'Enable Editing' only in linked file.

Result:

  • Formulas in linked file using references to the other file become broken, that is to say, 'Sheet1' references become replaced with '#REF'

Example:

='U:PATH[sub_wb2.xlsx]Sheet1'!$B3

becomes:

='[sub_wb2.xlsx]#REF'!$B3

Diagnosis:

According to my expert googleing, this has been a glitch that has existed in MS Excel's Protected View since Excel 2010. Seeing that it wasn't a fix that the then development team could issue via what they call a "Hotfix," they officially decided to postpone fixing the error.

Related reading:

https://islandearth.com/2011/11/21/excel-2010-protected-mode-trashing-my-linked-workbooks/

As of this month (it is June 2020) it evidently still hasn't been fixed...

My suggested workaround:

Make sure that:

U:PATH

Is a Trusted Location on local computer's Excel before opening your files...

Excel > Options > Trust Center > Trust Center Settings... > Trusted Locations

(✓) Allow Trusted Locations on my network (not recommended)

Add new location...

In essence, attempt avoiding the Protected View error altogether.

A gentle disclaimer

I'm new to SuperUser, VBA, and programming in general. So, please forgive any of my answer's amateur formatting. :-)

Improve this answer
2
  • Hi and welcome to SU. Interesting answer, but I think you will get a better reception to your contribution if you reformat. Generally, only use code blocks for code or formulas. Additionally, the intention of your first sentence is not immediately clear (to me at least). You might consider re-wording it.
    – FlexYourData
    Jun 10, 2020 at 11:13
  • Thank you! I have changed the formatting a bit.
    – CodingW'Fizzle
    Jun 11, 2020 at 17:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .