Microsoft KB Archive/48176

{| = Excel: Links to Closed Worksheets Return #REF! =
 * width="100%"|

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)
 * }