Microsoft KB Archive/102918

= 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.