Excel 2016 - External links to files get updated when source file renamed

**fornight** · 08-28-2018, 10:16 AM

Hi,

We have a big file that has external links to other excel files. Usually we will open them all so that update of links will run faster. But we notice that in excel 2016, if we tried to rename one of the files to another name, the links to the big file will be auto updated to the new file name.

Can we stop this "auto updated" in the settings somewhere? This feature is useful but it is causing a lot of confusions and mistakes when our users wanted to run different scenarioes for their work.

**dominicb** · 08-28-2018, 11:15 AM

Good afternoon fornight

You answered your own question really in the first line

Don't have the master file open at the same time as all the feeder files when you rename the files.

As far as I am aware there is no way to stop Excel being quite so annoyingly helpful sometimes.

HTH

DominicB

**FDibbins** · 08-28-2018, 02:10 PM

If you rename the source file while it is still closed (in windows explorer for instance), the destination file will not update, but will still be looking for the old link

**fornight** · 08-29-2018, 03:04 AM

yes, I did advise my users to try to close the big file before renaming the source files....but their reply was "it was not like this in the past! Why can't we stop this feature!".

**AliGW** · 08-29-2018, 03:14 AM

The answer is: "Ask Microsoft. In the meantime, close the big file!"

**FDibbins** · 08-29-2018, 02:06 PM

What she said lol.

This is MS's way of trying to be helpful - it auto-updates links so you dont need to. Think of it the other way round. Im sure most people would WANT the link updated as they change file names (this has been the more common question (for me), than the way you want it)

**MrShorty** · 08-29-2018, 02:54 PM

their reply was "it was not like this in the past! Why can't we stop this feature!".

I have to wonder how long they have been dealing with this. Excel has behaved this way since at least 2002/XP. I don't remember if 5.0 behaved this way. Excel has behaved this way for a long time, this is not new behavior.

I would guess that, at the level of MSFT's programmers, the expectation is that people will control this behavior using the INDIRECT() function. If you pass the external reference through an INDIRECT() function, then Excel will not automatically update the reference when a file is saved under a different name. Of course, INDIRECT() only works for open files, so, while the specified external link is closed, your users will get REF errors.