Forum Discussion
XLOOKUP link to another workbook doesn't work if workbook closed
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
11 Replies
I'm using the same logic i.e. xlookup to another table in another workbook using the cell range instead of table name and header name. Works like a charm.
So now the questions is, why does Microsoft not make it work using tablename and headername.
ChristyM22 referencing returns error when table referencing is used, try referring cells instead, for example instead of table name "Weights[[Metric ]]" you may use the particular cell.
Hope That helps
- 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 issueThis 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)
- 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.
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
The #REF! error shows when a formula refers to a cell that’s not valid .
I would be happy to know if I could help.
- 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!
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.aspxExamble:
=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
