Our department is using a shared workbook with multiple cells referring to other workbooks. Some of the linked cells behavior is very strange: in the path inside the cell the source workbook sheet name gets changed disregarding of our wish.

Let me explain based on an example.

I have a cell linked to another workbook:

='S:\Finance\[Budget 2016.xlsx]Sales'!$E$10

Then (after I close the book, or another person opens it after I saved the changes) it turns to this:

='S:\Finance\[Budget 2016.xlsx]xales'!$E$10

I also figured out that there is a pattern. If I had a binary workbook format (i.e., "xlsb" extension) this strange behavior would catch letter "b" from extension and replace first sheet name letter with it:

='S:\Finance\[Budget 2016.xlsb]Sales'!$E$10

will turn to

='S:\Finance\[Budget 2016.xlsb]bales'!$E$10

And of course if I try to open such broken reference I will get a #REF error inside this cell.

However I can see there is some logic (if you may call it 'logic') behind this error, I cannot seem to discover the root cause.

We use Excel 2016. Thankfuly, not all of the shared workbooks we have behave like this.

Your help is much appreciated.

  • Are these alternate links working, meaning other than appearance, is everything ok? If is breaking things, try using a unc path inplace of the S:\ network share. (your tech support should be able to help with what the correct UNC path is). The UNC some be in the form of \\myserver\myshare\ . Unlike network shares, it will not change depending on the user. Once get the links working, they should stay working no matter who opens. – bvaughn Dec 12 '16 at 22:07
  • Quick test - what if your formula is an Indirect() formula? Does it change then? – BruceWayne Dec 14 '16 at 4:43

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.