How to Use the INDIRECT Function to Create References

This article was previously published under Q151323
This article has been archived. It is offered "as is" and will no longer be updated.
In Microsoft Excel, the INDIRECT worksheet function returns the contentsof the specified reference and displays its contents. The INDIRECTworksheet function can be used to create linked references to otherworkbooks. Each attribute of the reference (workbook name, worksheetname, and cell reference) can individually be referenced using theINDIRECT function to create a user-defined dynamic reference usingworksheet cell references as inputs.
The INDIRECT function will only return the result of a reference to anopen file. If a workbook that the INDIRECT function is indirectlyreferencing is closed (not open in memory) the function will return a#REF! error.

The following examples create a reference to a workbook using threedifferent cell inputs as references for the workbook, worksheet, and celllink.

Example 1

  1. In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation marks).
  2. In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks).
  3. In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks).
  4. In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks).
  5. Save both workbooks.
  6. In Book2, Sheet1, cell B1 type the following formula:


    NOTE: In Microsoft Excel for the Macintosh, omit ".xls" from the formula, as in the following example:


Example 2

The formula in Example 1 could alternately be written using multipleINDIRECT statements as follows:



NOTE: In Microsoft Excel for the Macintosh, omit ".xls" from the formula,as in the following example:


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

In the INDIRECT function, referencing cells without using quotation marksevaluates the result of the cell reference. For example, if cell A1contained the text "B1" and B1 contained the word "TEST", the formula=INDIRECT(A1) would return the result "TEST". Referencing a cell withquotation marks returns the result of the cell contents. In the example inthe previous sentence the formula would return the text string "B1" andnot the contents of cell B1.
For more information about the INDIRECT function, choose the Search buttonin Microsoft Excel help reference and type:

8.00 97 98 XL98 XL97 XL7 XL5 concatenate join link filename XL

Article ID: 151323 - Last Review: 12/04/2015 14:43:31 - Revision: 1.3

Microsoft Excel 97 Standard Edition, Microsoft Excel 95 Standard Edition, Microsoft Excel 5.0 Standard Edition, Microsoft Excel 98 for Macintosh

  • kbnosurvey kbarchive kbfaq KB151323