Microsoft Knowledge Base

Excel: INDIRECT() Returns #REF! with 3-D Reference
Last reviewed: September 12, 1996
Article ID: Q102918
The information in this article applies to:
  • Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
  • Microsoft Excel for the Macintosh, version 4.0

SUMMARY

The INDIRECT() function in Microsoft Excel returns a #REF! error value when the ref_text argument contains a 3-D reference, as in the following example: 

   =SUM(INDIRECT("[BOOK1]Sheet1:Sheet2!A1"))
If the ref_text argument contains a single bound or unbound sheet name, it will be evaluated correctly. To use INDIRECT() to evaluate multiple sheet references, break up the sheet references as follows: 

 =SUM(INDIRECT("[BOOK1]Sheet1!A1"),INDIRECT("[BOOK1]Sheet2!A1"))
TIP: You can store the workbook name and the cell reference or range in separate cells and concatenate these cells with the sheet names. This technique is especially useful if you have more than a few sheets. For example, if you have the following on the sheet in which you're entering the INDIRECT() function 

   A1: [BOOK1]  B1: !A1
you can use this formula: 

   =SUM(INDIRECT(A1&"Sheet1"&B1),INDIRECT(A1&"Sheet2"&B1)

MORE INFORMATION

The INDIRECT() function is used to return a value in a cell that is indicated by the ref_text argument. This is either a reference to a cell containing a reference or a reference expressed as a text string. This reference can be an external reference; however, if it is an external reference, the external sheet must be open for the function to be evaluated correctly (otherwise it returns a #REF! error value).

INDIRECT() is useful when you have portions of a reference in separate cells, for example, a column letter in one cell and a row number in another. When you concatenate the two with the INDIRECT() function, it evaluates the cell reference and returns the value in the indicated cell. It is also useful for keeping a reference static. When the ref_text argument is a text string such as "Sheet1!A1", INDIRECT() won't be updated if you move or delete Sheet1!A1; instead, it will return the new value in that cell.

REFERENCES

"Online Help," version 5.0 "Function Reference," version 4.0, page 238-239

KBCategory: kbusage
KBSubcategory:

Additional reference words: 4.0 4.00 4.0a 4.00a 5.0 5.00



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: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.