Excel linked data formula turns to #REF upon opening workbook

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.

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.

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.