Excel: Links to Closed Worksheets Return #REF!

 Last reviewed: November 2, 1994
Article ID: Q48176

SUMMARY

Microsoft Excel can link to information in a closed worksheet only if the link is a simple link using an absolute cell reference (for example, =Worksheet1!$A$2).

If the link is complex (containing formulas such as =Worksheet1!$A$2*6 or =-Worksheet1!$A$2), or uses a relative reference (such as =Worksheet1!A2), Excel returns #REF! when the supporting worksheet is closed.

To link to a closed worksheet, use simple links with absolute references. To perform calculations with linked information, such as =Worksheet1!$A$2*6, do the following:

  1. Link directly to the information. For example, enter =Worksheet1!$A$2 into cell A1 of the worksheet.

  2. Perform the calculations on the cell that contains the simple link. For example, enter =A1*6 in cell A2 on the worksheet.

MORE INFORMATION

The following examples of simple, absolute references both link correctly to closed worksheets: 

   =Worksheet1!$A$1          (Absolute reference)
   =Worksheet1!$A$1:$B$10    (Array of absolutely referenced cells)
The following references return #REF! when the supporting worksheet is closed: 

   =Worksheet1!A1            (Relative reference)
   =Worksheet1!$A$2*6        (Absolute reference multiplied by six)
   =SUM(Worksheet1!A1:A5)    (Sum of relative referenced array)

KBCategory: kbother
KBSubcategory:

Additional reference words: 1.00 1.03 1.04 1.06 1.50 2.20

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.