Broken Links in the name manager that repair when read only

[As-Lag]

Dear Forum

I have an excel workbook that refers to another workbook for data. This has worked for me for many years. Last week, I copied a sheet from a workbook that refers to the database to the database. The sheet was called MR. Things started happen in my other sheets and I traced the problem to the Name Manager; I found all the names had been duplicated with 'MR'! in Name. I deleted the sheet MR and they all disappeared. Then disaster - all my links when #NAME?.

Again, I traced the problem to be in the Names Manager. I opened two versions of Excel and opened a job in each. The "primary" excel had the #NAME? problem but the read-only Excel, the problem disappeared. I attach a screenshot showing the situations. It appears that the address to the database has been truncated. The workbook and database are in totally different folders, not in the same folder.

I am in the process of writing a bit of VBA to correct the problem when I discivered this anomoly. Any clues how I may fix the problem please?

Regards Daniel

 

[As-Lag]

Quick update.

I wrote a little VBA to change all the names to include the full path to the database. Ran the macro and all went well. Opened the Name Manager and all the addresses reverted back to the truncated address.

Very confusing.

 

[davidbeach]

If the sheet being referenced in the link is open, the link does not show the path; close the file and the path shows up on its own.

I’ll see your silver lining and raise you two black clouds. - Protection Operations

 

[As-Lag]

David

Thanks. That explains that. Still havbe the #NAME? problem. Driving me nuts. May have to use LibreCalc in future if I can't fix it. Years of work gone.

 

[As-Lag]

Fixed it.

I had some stray names that I used a VBA script to remove from the Names Manager. The script also deleted some important names. I have reassigned the names and it now works.

Silly me. Teach me to not check what my code is doing.