Registrations are now open. Join us today! There is still a lot of work to do on the wiki yet! More information about editing can be found here. Already have an account?

# 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)
```