Excel: Links to Closed Worksheets Return #REF!Last reviewed: November 2, 1994 |
SUMMARYMicrosoft 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:
MORE INFORMATIONThe 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 Last reviewed: November 2, 1994 |