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. 

More Information