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.