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

```
=VLOOKUP(1,'D:\Book1.xlsx'!Table1[#Data],3)
```

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)?

### Note

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.