I have 2 workbooks as follows
Book1 contains two named ranges. MyRange1 has a scope of sheet1 and MyRange1 has a scope of Workbook. Note: two distinct ranges, two different scopes, both have the same range name.
Book2 has links to book1’s MyRange1 at the workbook scope. See the formula’s in column B. =VLOOKUP(A2,book1.xlsm!MyRange1,2)
If I open book2, then open book1, The range with the workbook scope is missing and the link in Book2 is changed to look at the sheet scoped range name.
I have found a bit of discussion regarding this issue using Google, but I have not found a solution.
These workbooks likely were created using Excel 95. They worked well in Office 2003.