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

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×