How to Use the INDIRECT Function to Create References

Article translations Article translations
Article ID: 151323 - View products that this article applies to.
This article was previously published under Q151323
Expand all | Collapse all

On This Page

SUMMARY

In Microsoft Excel, the INDIRECT worksheet function returns the contents of the specified reference and displays its contents. The INDIRECT worksheet function can be used to create linked references to other workbooks. Each attribute of the reference (workbook name, worksheet name, and cell reference) can individually be referenced using the INDIRECT function to create a user-defined dynamic reference using worksheet cell references as inputs.

MORE INFORMATION

The INDIRECT function will only return the result of a reference to an open file. If a workbook that the INDIRECT function is indirectly referencing is closed (not open in memory) the function will return a #REF! error.

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

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:

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

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

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

Example 2

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

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

INDIRECT("A3"))

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

=INDIRECT("'["&INDIRECT("A1")&"]"&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.

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

REFERENCES

For more information about the INDIRECT function, choose the Search button in Microsoft Excel help reference and type:

   indirect
				

Properties

Article ID: 151323 - Last Review: August 17, 2005 - Revision: 1.3
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbfaq KB151323
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com