VLOOKUP behaving erratically

[sparky2205]

Hi folks,
There are 2 files: FileA and FileB, for simplicity sake. File B contains a simple vlookup to File A wrapped inside an IFERROR.
Both files are stored at different locations on the network.
When I open the files all is fine. The network location disappears from the formula. i.e. just the file name remains. This is the behaviour I expect.
When one of my colleagues opens the files the vlookup doesn't work. The part of the formula containing the network location is replaced with the #REF error. Not the whole formula, just the network location part. But this obviously results in the whole formula returning 0.

Sample formulas:
Working correctly (i.e. when I open the files);
With FileB closed =IFERROR(VLOOKUP(B111,'\\vm-server1.vistamed.local\data on nas\NPI\Misc Items (logs_trackers_etc)\NPI & R&D Open Orders\[NPI & R&D Open Orders 2021.xlsx]OO 04-10'!$AA$2:$AC$62,3,FALSE),0) Part in bold is the network location of FileB
With FileB open =IFERROR(VLOOKUP(B111,'[NPI & R&D Open Orders 2021.xlsx]OO 04-10'!$AA$2:$AC$62,3,FALSE),0) as expected network location has disappeared and all is well.

Not working correctly (i.e. when my colleague opens the files);
With FileB closed =IFERROR(VLOOKUP(B111,'\\vm-server1.vistamed.local\data on nas\NPI\Misc Items (logs_trackers_etc)\NPI & R&D Open Orders\[NPI & R&D Open Orders 2021.xlsx]OO 04-10'!$AA$2:$AC$62,3,FALSE),0) Part in bold is the network location of FileB
With FileB open =IFERROR(VLOOKUP(B111,#REF'[NPI & R&D Open Orders 2021.xlsx]OO 04-10'!$AA$2:$AC$62,3,FALSE),0) as stated above the network location is replaced with #REF

A new one on me guys. Have any of you ever seen this type of behaviour?

 

[Alex Blakenburg]

When your colleague opens the file is he using a drive mapping ?

 

[Candyman8019]

Just spitballing, but I wonder if it has something to do with the order of which workbooks are saved/closed first., ie A save and close, then B save and close or vice versa.
I'm assuming your colleague has permissions to access that network location.

 

[sparky2205]

No drive mapping going on. It's as simple as opening 2 files.
Yes, my colleague does have access to both files.

I did see someone recommend using INDIRECT before the lookup file destination but this was in relation to sharepoint files, so I don't know if it would have any affect here.