I just came across this while trying to recreate someone else's problem.
I start with an open workbook, call it WB.XLSX. I create a new, blank workbook and save it as TEXT.XLSX. I define the workbook-level name foobar in TEXT.XLSX referring to ="wb". I return to WB.XLSX, enter the formula =TEXT.XLSX!foobar, and the cell returns wb.
Next I insert a new, blank worksheet in WB.XLSX, define the worksheet-level name foobar referring to ="ws", then name that worksheet TEXT.XLSX. I return to the previous worksheet in WB.XLSX, move to a blank cell, and enter the formula =TEXT.XLSX!foobar, and that cell returns ws.
I have 2 formulas which appear identical but return different values.
Yes, I know that if I close TEXT.XLSX, Excel will add the file's drive-directory path to the workbook name, thus eliminating the ambiguity WHILE THAT FILE IS CLOSED. However, when that file is open, ambiguous name references are possible. I consider this a deficiency in Excel name reference syntax. The only way to fix it would be to require square brackets around base filenames in external references to workbook-level defined names, so in the 1st case above the formula would need to have been =[TEXT.XLSX]!foobar. That'd break formula backwards compatibility, so I realize it ain't gonna happen unless & until MSFT decides to overhaul the object model to allow for multiple files with the same base filename to be open at the same time in the same Excel instance.
Bookmarks