Jan 18 2022 09:35 AM
Jan 18 2022 09:35 AM
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])
Jan 18 2022 10:47 AM
Jan 18 2022 10:54 AM
Jan 19 2022 12:29 AM - edited Jan 19 2022 12:33 AM
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
Working with external links
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
Sep 09 2022 09:44 AM
This has its limitations but it works. Look at the comment of the user "Fluff".
Oct 28 2022 01:38 AM
Oct 28 2022 06:56 AM
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.
Oct 28 2022 07:32 AM
Feb 22 2023 03:00 PM
Aug 24 2023 06:47 AM
Sep 22 2023 03:01 AM
@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