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

%3CLINGO-SUB%20id%3D%22lingo-sub-3064872%22%20slang%3D%22en-US%22%3EXLOOKUP%20link%20to%20another%20workbook%20doesn't%20work%20if%20workbook%20closed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3064872%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20an%20XLOOKUP%20in%20one%20worksheet%20that%20is%20pulling%20data%20from%20a%20reference%20table%20in%20a%20separate%20workbook.%26nbsp%3B%20The%20lookup%20works%20fine%20if%20the%20reference%20workbook%20is%20open%2C%20but%20if%20it%20is%20closed%2C%20I%20get%20a%20%23REF%20error.%26nbsp%3B%20I%20have%20searched%20and%20have%20not%20been%20able%20to%20ascertain%20why%20I%20am%20having%20issues%20with%20this.%3C%2FP%3E%3CP%3E%3DXLOOKUP(%5B%40Metric%5D%2C'D%3A%5CEstimating%5CSteel%20Weight%20Table.xlsx'!tblWeights%5B%5BMetric%20%5D%5D%2C'D%3A%5CEstimating%5CSteel%20Weight%20Table.xlsx'!tblWeights%5BImperial%5D)%3C%2FP%3E%3CP%3ETIA%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3064872%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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

3 Replies

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

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

@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