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
