Microsoft KB Archive/48176

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: Link directly to the information. For example, enter =Worksheet1!$A$2 into cell A1 of the worksheet. 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 Last reviewed: November 2, 1994 © 1998 Microsoft Corporation. All rights reserved. Terms of Use.