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

[#5]

@ChristyM22 

This has its limitations but it works. Look at the comment of the user "Fluff".

https://www.mrexcel.com/board/threads/xlookup-and-closed-workbooks.1157123/post-5611713

[#6]

Don't know if this issue was solved - but I think I know what's the problem here. Xlookup wants the same number of rows for each lookup so if you used name manager or referenced to a power query table it might not show correctly. For example =XLOOKUP($A$3:$A$200&$B$3:$B$200&$C$3:$C$200&$F$3:$F$200;'MV Makelaars Navrae.xlsm'!Navrae_2[KLIENT]&'MV Makelaars Navrae.xlsm'!Navrae_2[POLIS '#]&'MV Makelaars Navrae.xlsm'!Navrae_2[VERSEKERAAR]&'MV Makelaars Navrae.xlsm'!Navrae_2[TIPE POLIS];'MV Makelaars Navrae.xlsm'!Navrae_2[PIETER TERUGVOERING];"";0) the formula is correct but it will give an error as an answer change it to as example
=XLOOKUP($A$3:$A$200&$B$3:$B$200&$C$3:$C$200&$F$3:$F$200;'[MV Makelaars Navrae.xlsm]Navrae'!$A$3:$A$200&'[MV Makelaars Navrae.xlsm]Navrae'!$B$3:$B$200&'[MV Makelaars Navrae.xlsm]Navrae'!$C$3:$C$200&'[MV Makelaars Navrae.xlsm]Navrae'!$F$3:$F$200;'[MV Makelaars Navrae.xlsm]Navrae'!$H$3:$H$200;"";0) This might solve your issue

[#7]

@LizeMarie 

This is a known limitation:

• Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! Error when they are refreshed.

Dynamic array formulas and spilled array behavior (microsoft.com)

[#8]

Mine does not show #REF and I don't have both workbooks open. I used Xlookup as per my reply in the previous message. And I work with more than one workbook doing the same thing...works 100% for me with no #REF totals even on refresh. Maybe it's luck. Maybe you should try it yourself to see its works with no #REF error while one workbook is closed. Well, I found a way and working with this workbook since xlookup rolled out think it was 2020. if your xlookup are 20 rows make sure the 20 rows array are followed through all the way. If one array is longer make all the same. Hope it make sense. Like I said it works for me - Tried and tested.