Jan 18 2022 09:35 AM
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
Jan 18 2022 10:47 AM
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.
Jan 18 2022 10:54 AM
Jan 19 2022 12:29 AM - edited Jan 19 2022 12:33 AM
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.
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