Microsoft KB Archive/48176

From BetaArchive Wiki

Excel: Links to Closed Worksheets Return #REF!

Last reviewed: November 2, 1994
Article ID: Q48176


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.


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


Additional reference words: 1.00 1.03 1.04 1.06 1.50 2.20

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