Excel 2007 : Worksheet links change without warning

**ctckark1** · 04-06-2011, 12:15 AM

I have a worksheet that is basically all a big link to another workbook on a shared server. For example:

='\\servername\folderA\folderB\[workbook file name.xlsm]Sheet'!D1

These formulas take up the most of the sheet essentially mirroring the other workbook so that this data can then be used elsewhere in this book, without modifying the original book.

This workbook is opened by about 6 different people over the course of a day, but I've only really noticed this issue with one of them - what happens is when this person opens both this workbook and the original workbook at the same time, it changes the links to just

='[workbook file name.xlsm]Sheet'!D1

When the original workbook is then closed, the links should revert back to the actual location of the file, which they attempt to do, but he has this share (\\servername\folderA\) mapped as Y:, so the links actually update to

='Y:\folderB\[workbook file name.xlsm]Sheet'!D1

.

Biggest problem then comes in that not all of us use the same mappings as he does. Without forcing all users of this workbook to use the same mappings across the board (would require some people to change a LOT of what they do), is there a setting or option I'm missing? I compared settings between his copy of Excel and mine as best as I could, but I may have missed something.

Any ideas would be a great help! I'm getting really tired of having to reset an entire worksheet worth of formulas to fix this on a daily basis. (I really don't want to, but as a last resort I know could set a macro that will fix the formulas on worksheet open).

Chris

**batman07** · 04-06-2011, 01:39 AM

I suggest the following procedure:
1) Make sure that all of the users are defining the same drive letter say (Y drive) on their machines.
2) Just use copy and replace procedure, (ctrl+H > options > within workbook > find what: \\servername\folderA\ and replace with: Y: > then hit replace all).

Hope that this will help.

**ctckark1** · 04-06-2011, 03:19 AM

I really want to avoid making everyone map the drives the same, because as I said this affects other users a lot more because they would have to change a large number of their personal shortcuts and document links, affecting much more than just this one document.