To be explicit, I have replicated the following issue:
"#REF" replaces "Sheet" in formulas to an external workbook.
Conditions:
- Open file (in directory that is not listed as a Trusted Location) containing links, remain in Excel's Protected View (don't click Enable Editing).
- Open the other file that is being referenced (remain in Protected View for this file too).
- (While referenced file is still in protected view): 'Enable Editing' only in linked file.
Result:
- Formulas in linked file using references to the other file become broken, that is to say, 'Sheet1' references become replaced with '#REF'
Example:
='U:PATH[sub_wb2.xlsx]Sheet1'!$B3
becomes:
='[sub_wb2.xlsx]#REF'!$B3
Diagnosis:
According to my expert googleing, this has been a glitch that has existed in MS Excel's Protected View since Excel 2010. Seeing that it wasn't a fix that the then development team could issue via what they call a "Hotfix," they officially decided to postpone fixing the error.
Related reading:
https://islandearth.com/2011/11/21/excel-2010-protected-mode-trashing-my-linked-workbooks/
As of this month (it is June 2020) it evidently still hasn't been fixed...
My suggested workaround:
Make sure that:
U:PATH
Is a Trusted Location on local computer's Excel before opening your files...
Excel > Options > Trust Center > Trust Center Settings... > Trusted Locations
(✓) Allow Trusted Locations on my network (not recommended)
Add new location...
In essence, attempt avoiding the Protected View error altogether.
A gentle disclaimer
I'm new to SuperUser, VBA, and programming in general. So, please forgive any of my answer's amateur formatting. :-)