I came across an issue with the Excel version of Office 365 ProPlus, as below described.

A.xls(x) contains formula that links to B.xls Sheet1. When working on A.xls. I decide to navigate through windows explorer and open B.xls(x). Once I do all references to Sheet1 turns into #REF of A.xls(x). And naturally all values that were previously correct now returns #REF. Even without making any changes to B.xls(x), just opening the file cause this problem. Both these files are on the mapped network location within the same LAN. User is on Win7 & Office 365 ProPlus. The server is running on Windows 2008 R2 with no office installed. This occurred to users who are member of the AD and in Workgroup. We have tried re-installing, repairing, etc. but the problem remains the same. But if the files were on the local HDD, we didn't face the same issue.

Hi,

According to your description, this issue may be caused by the hyperlink/reference changed from relative to absolute links. For example, if you use the reference in formula, we only need to enter like this:[filename.xlsx]Sheet1'!$C$107:$C$112, when we re-open the file, Excel will autocomplete the path, like: [C:\Mydir\filename.xlsx]Sheet1'!$C$107:$C$112.

To workaround the issue, we may update the link manually, please see the image below:

Go to Data tab>Edit link>Update value or change data source.

More references for the common scenarios of this issue:

• You map a drive under the root of a share. For example, you map drive Z to \\<var>Server</var>\<var>Share</var>\Folder1.
• You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.
• You open the file by a UNC path.
• As a consequence the link will be broken.

https://support.microsoft.com/kb/328440/en-us?wa=wsignin1.0

Hope it can be helpful to you.

Regards,

George Zhao
TechNet Community Support

Dear George,

We have done as your advise, but still the problem remains the same. I'm trying to post a video which demonstrates this problem, but unfortunately the forum doesn't allow to post links and picture until my account is verified. Could you please help in this regard.

Sadish

Dear George,

Please see the below link which demonstrates this issue for better understanding. Hope this helps in troubleshooting further.

http://youtu.be/jWcUZadAvEk

Hi,

I have watched your video, and followed your steps to reproduce it, but the reference displayed correctly.

In the video, I have not seen that you updated the hyperlink (Such as Go to Data tab>Edit link>Update value or change data source.) 

Thus, I recommend you try the workarounds to check they are helpful:

1. Use absolute link. For example:  \\192.168.11.5\test\file name

2. Use Hyperlink formula:

=hyperlink("file:////yourpathtothe/something.xxx","Click me")

You can even put the path in a dedicated cell and the filename in another.

=hyperlink("file:////" & $a$1 & A2,"Click me")

Hope it's helpful.

Regards,

George Zhao
TechNet Community Support

Dear George,

Please see below another example from the active files which we use. And changing the references as suggested is not a practical approach considering the numbers of file links and the records we maintain in similar fashion for many years on a weekly basis. Also as seen on the video, the file shared location/mapped drive/etc. are always static.

In Office 2010 we didn't face this issue, how is this possible in Office 365 ProPlus. We also updated the Office 365 ProPlus to the latest version and still the problem remains the same. We would kindly request to provide a solution patch, rather than workaround, as they are not practical. Much appreciate your support in this matter.

http://youtu.be/AkancnqufbQ

Hi,

Sorry for reply delay, I have watched the second video. I found there is a "plus sign" in the beginning of the formula bar, please try to delete it and test.

Or you may sand us a sample file (Only part of the files and remove the private information via Email (
ibsofc@microsoft.com). I'd like to test it.

Regards,

George Zhao
TechNet Community Support

Dear George,

When we disable the Protected View options in XL as mentioned below it fixes this problem, But this also creates a security vulnerability.

In Excel --> File -> Options -> Trust Center -> Trust Center Setting --> Protected View and three options disabled.

Also regarding your comments, when we remove the #Ref and + tags from the formula and save, the problem reappears again when we open the XL file. I will mail you the files shortly.

Regards,

Sadish

Hi Sadish,

I have not received the mail yet :). Do you mind re-sending it? In my environment, I also enable the Protected View, but this issue does not occur. Thus, I recommend you check the security settings of Network share.

Regards,

George Zhao
TechNet Community Support