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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.