How to use the INDIRECT function to create references in Excel (213933)


The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Excel 2002
  • Microsoft Office Excel 2003
This article was previously published under Q213933
For a Microsoft Excel 97 and earlier and Microsoft Excel 98 and earlier version of this article, see 151323.

SUMMARY

In Microsoft Excel, the INDIRECT worksheet function returns the contents of the specified reference and displays its contents. You can use the INDIRECT worksheet function to create linked references to other workbooks. You can reference each attribute of the reference (workbook name, worksheet name, and cell reference) individually by using the INDIRECT function to create a user-defined dynamic reference with worksheet cell references as inputs.

MORE INFORMATION

Note The INDIRECT function only returns the result of a reference to an open file. If a workbook that the INDIRECT function is indirectly referencing is closed, the function returns a #REF! error.

To create a reference to a workbook using three different cell inputs as references for the workbook, worksheet, and cell link, follow the steps in the following examples.

Example 1

  1. Start Excel.
  2. In Book1, Sheet1, cell A1 type: This is a test.
  3. On the File menu, click New.
  4. Click Workbook, and then click OK.
  5. In Book2, Sheet1, cell A1 type: Book1.
  6. In Book2, Sheet1, cell A2 type: Sheet1.
  7. In Book2, Sheet1, cell A3 type: A1.
  8. Save both workbooks.
  9. In Book2, Sheet1, cell B1 type the following formula:

    =INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)

    The formula returns "This is a test."

Example 2

You can replace the formula in Example 1 with multiple INDIRECT statements, as in the following formula:

=INDIRECT("'["&INDIRECT("A1")&".xls]"&INDIRECT("A2")&"'!"&INDIRECT("A3"))

Note the difference in referencing the cells. Example 1 references cells A1, A2, and A3 without using quotation marks, while Example 2 references the cells using quotation marks around the references.

The INDIRECT function, which references cells without using quotation marks, evaluates the result of the cell reference. For example, if cell A1 contains the text "B1," and B1 contains the word "TEST," the formula =INDIRECT(A1) returns the result "TEST." However, referencing a cell with quotation marks returns the result of the cell contents. In the example in the previous sentence, the formula returns the text string "B1" and not the contents of cell B1.

REFERENCES

For more information about the INDIRECT worksheet function, click Microsoft Excel Help on the Help menu, type indirect in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:1/19/2006
Keywords:kbhowto KB213933
©2004 Microsoft Corporation. All rights reserved.