Local scope Lambda randomly produces #NAME

Dear Microsoft,

The penny has finally dropped on what is happening here.

We are an international team.  Our production environment is fragmented into different Excels to as much as possible avoid people clashing when updating (their) data. *)  There is a final production Excel which sources data thru any number of links to all these feeder files.

The disastrous issue is this:  As many will know from PAINFUL experience, each Windows user installation creates a unique file tree (in C:\Users) for that user.  This plays havoc with any links between Office applications when the files are exchanged between different users (be that by email or OD).  Since all our users have laptops, there is never more than one user profile on a computer and all computers are hence supposed to be configured with the same user name, to resolve the issue.

Alas, some are not 😞

To work around the issue, our Excels in their Auto_Open "sense" the file tree of the local user and relink any Excel links according to the user's configuration.

Here is what transpires:

When you create an external reference such as someData='[someWB.xlsm]someWS'!someName where someWB is inside your OD and another user sharing that same OD opens that same Excel, even before anything else can happen, the (unresolved) reference will now read:

someData='C:\Users\nameOfAuthor\...\someWB.xlsm'!someName

It is clear that my efforts to repoint someData to the appropriate path for someWB in my OD must fail because the crucial reference to someWS has already been lost.

PLEASE FIX AS A MATTER OF URGENCY.

*) We were initially very excited about Online Collaboration but our experience with it in our complex production environment has been a massive disappointment.  Nomadic (offline) users in particular were extremely badly served.  We hence stick with Online Collaboration turned off.