Link becomes #Ref! when file is open


My co-worker has a file that links to other files. (Personally I hate linking files, but that's another rant.)

Most of the files are okay but there's one specific source file that whenever my co-worker opens it, the formula in the file with the link becomes a #REF!.

There's nothing special with the formula. It's just a super straightforward 

='network drive:\folder\subfolder\[workbook]worksheet'!Cell

Some observations I have

  • The #REF! happens when the source file is open normally.
  • If it's open via the "Edit Links" pop up window in the link file, the formula is okay.
  • My co-worker download the source file from Sharepoint into the network drive. When it is open, excel gives the warning "This file originated from an internet location and might be unsafe."
  • The other source files are from Sharepoint as well but they don't have the same message when they're opened.

I've told my co-worker to open the file via "Edit Links" to avoid the issue, but I'm stumped and super curious as to why opening the source file a different way would make a difference. A link is a link right?

I'm convinced the "file originated from internet" has something to do with it but I'm just not sure what the rationale is. Anybody has any insight?




You won my heart, Cathy, because I hate links, too. As for your question, I wonder if you would bear with me while I guess.

Sure, a link is a link but, as you have found out, not all files were created equal. Excel does treat files it considers unsafe differently, to wit, it places them in some sort of quarantine until you press "Enable "Edits". "Qurantine" may not be a bad word to use because such files aren't really opened when they are opened. Instead they are just displayed but their content, in particular code, kept separate from anything in the computer's memory.

Now, I think it stands to reason that a reference to a file which isn't really open, just displayed, and not connected to anything in the computer, shouldn't work. Hence the #REF! error.

You say, the #REF! error happens when the file is opened "normally". In my theory that would be an overstatement. Excel doesn't open workbooks it suspects normally. It opens them in their own special way. So, the first hope would be that the links can be restored after clicking "Enable Edits". Quite likely, that button doesn't come up in your circumstances. But if the underlying cause is the one I suspect then the button itself wouldn't matter. We could presume that Excel first acted on its suspicion, then changed its mind after a second look. Accordingly, clicking on "Refresh Links" should establish the reference links. (I'm not so sure of the precise name or location of the button - compare rant above - but I know it exists.)

"Should" is the key word because I think it won't. My theory would have Excel open the file in a separate instance. So, yes, the file is finally open but not in the same instance as the rest of your Excel. So they can't "see" each other. You can test this either using VBA and counting the number of instances of the Excel.Application currently running or by quitting the Excel application of either workbook and see if both workbooks close.

If my theory is correct there is nothing you can do after the event. That leaves room for action only before. Usually, Excel will mark a workbook as unsuspicious after it has been approved ("Enable Edits" was clicked) and saved. "Normal" opening should be possible thereafter. However, you may still need a better definition of "normal". I have a lot of external files on my PC and files which used to be external and have been saved. Therefore I suspect that my observations may not be universally true. With that said, I observe that Excel often creates new instances of itself unexpectedly. I think when I open a workbook by double-clicking in Windows Explorer I get a new instance of Excel at least most of the time, while File > Open will open the file in the instance that I am calling the command from as is applicable to files opened by VBA.



Sorry, when I wrote "normally" I meant the way in which it is open. As you noted:
1) Double clicking from Window Explorer
2) Within excel: File > Open
I tried both methods and got the same result.

In the first case, I believe you are correct that it's actually opening up a different session all together since I can't flip back and forth between the files using ctrl-tab. But the second way, that definitely should be in the same session.

(And perhaps I should work on clarity of my writing LOL.) The " #REF!" that's showing up isn't just the result of the formula, it's actually changing the formula itself.

It goes from

='network drive:\folder\subfolder\[workbook]worksheet'!Cell


='network drive:\folder\subfolder\[workbook]#REF!'!Cell

or sometimes even just plain old


The fact that even the error isn't consistent is really baffling to me.

This happens as soon as the file is open. It doesn't even give me the opportunity to "enable edits" or anything else.
Cathy (rep: 53) Jan 19, '19 at 11:32 pm
The error you describe happens when a referenced source isn't available, usually, when the cell referenced has been deleted. When Excel comes near the edge of its capabilities it may issue error messages which don't portray the actual error. In such a case one is left with the fact that there is an error. I think that is the case here, too. I guess when the chips are down my most honest answer should be "I don't know". I gave it my best guess. Sorry it didn't work out.
Variatus (rep: 3378) Jan 20, '19 at 1:49 am
No worries. You've been more than helpful in so many other things.

This one really is a curiosity question more than anything else. When a file is open one way, the formula changes (even thought the source file hasn't changed a thing); when it's open in another way everything is hunky-dory. Go figure.

If an excel guru's best is an educated guess, then I don't feel so bad about being completely bewildered. Haha.
Cathy (rep: 53) Jan 21, '19 at 2:13 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login