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

[ChristyM22]

Hello,

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

TIA

[#2]

@ChristyM22 

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.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

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

[#4]

@ChristyM22 

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

Symptoms

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!.

Cause

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.

Resolution

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

More Information

Using structured references with Excel tables
http://office.microsoft.com/en-us/excel/HA101556861033.aspx

Working with external links
http://office.microsoft.com/en-us/excel-help/CH010064844.aspx

Examble:

=XVERWEIS([@Name];Datasource.xlsx!tbl_Schring[Name];Datasource.xlsx!tbl_Schring[Schring])

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

Datasource.xlsx!tbl_Schring[Name]

through

[Datasource.xlsx]Source!$A:$A