I have two spreadsheat files (D:\Book1.xlsx and D:\Book2.xlsx). The first workbook has a table, Table1 with three columns (id, Name, Value). In the second work book I to want reference values in Table1 using VLOOKUP. My current formula for referencing values is
Which works when both workbooks are open. However, if I open Book2.xlsx by itself the above formula evaluates to REF#! and the formula has an absolute path instead of the relative path.
As soon as I open Book1.xlsx the path reference becomes relative and the formula evaluates to the correct value.
So I was wondering how do I get the external reference to work without having to open both workbooks (or is this possible)?
To be clear my formula returns #REF! when the second workbook is opened by itself.
Both workbooks reside on the root of my D partition as d:\Book1.xlsx and d:\Book2.xlsx.