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

Propriétés

ID d'article : 2299192 - Dernière mise à jour : 17 juin 2011 - Révision : 1

Commentaires