XLOOKUP link to another workbook doesn't work if workbook closed

New Contributor


I have an XLOOKUP in one worksheet that is pulling data from a reference table in a separate workbook.  The lookup works fine if the reference workbook is open, but if it is closed, I get a #REF error.  I have searched and have not been able to ascertain why I am having issues with this.

=XLOOKUP([@Metric],'D:\Estimating\Steel Weight Table.xlsx'!tblWeights[[Metric ]],'D:\Estimating\Steel Weight Table.xlsx'!tblWeights[Imperial])


3 Replies


The #REF! error shows when a formula refers to a cell that’s not valid .

How to correct a #REF! error



I would be happy to know if I could help.



I know I don't know anything (Socrates)

Thank -you, but the error only happens if the supporting workbook is closed. It works fine if both workbooks are open.


Links to data tables residing in external files display #REF!


Consider the following scenario:
1. You insert a table in an Excel file, named for example source.xlsx.
2. In another file, named for example destination.xlsx, you insert a link to the data table which resides in source.xlsx.
3. You close both source.xlsx and destination.xlsx.
4. You open destination.xlsx and you choose to update links when prompted.

Result: the cells that are linked to the external data table display #REF!.


This is expected behavior for Excel.
New references to external workbooks that aren’t open will successfully parse without verifying the reference, but will return #REF. 
When loading an external structure reference to a closed workbook, the reference is not updated by default.  If you choose to calculate the formula the result is #REF! instead of the corresponding value.


Structured references require the external workbook to remain open for the formulas to work properly. 

More Information

Using structured references with Excel tables

Working with external links




Example: Both files located in the same folder are closed.

Only the target file is opened.

The result: error messages for all formulas that have a file reference from a file that is close.


 replace the covers, so