Excel INDIRECT function

How to use the INDIRECT function to create references in Excel

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.

Note The INDIRECT function only returns the result of a reference to an open fil... 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.

1. Start Excel.
2. In Book1, Sheet1, cell A1 type This is a test.
3. In Microsoft Office Excel 2003 and in earlier versions of Excel, click New on the File menu, click Workbook, and then click OK.
   
   In Microsoft Office Excel 2007, click the Microsoft Office Button, click New, and then click Create.
4. In Book2, Sheet1, cell A1 type Book1.
5. In Book2, Sheet1, cell A2 type Sheet1.
6. In Book2, Sheet1, cell A3 type A1.
7. Save both workbooks.
8. In Excel 2003 and in earlier versions of Excel, type the following formula in Book2, Sheet1, cell B1:

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

   In Excel 2007, type the following formula:

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

   The formula returns "This is a test."

Example 2

In Excel 2003 and in earlier versions of Excel, 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"))

In Excel 2007, type the following formula:

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

Note the difference in how Excel references 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 references cells without using quotation marks. This function 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" instead of the contents of cell B1.

 

Old News

Help using the Excel INDIRECT function

The INDIRECT function allows you to use a text string containing a range or cell reference which can be fed into a formula. For example the formula =INDIRECT("C3") would return the value in cell C3. This is the same as saying =C3 and is therefore normally pointless. The example (right) achieves the same result but looks up the text cell reference from within another cell (C6).

You may wish to make a workbook interactive in order to display various results which are obtained by using values from alternative ranges. (Sometimes the OFFSET function is the best way of achieving this.) The INDIRECT function becomes very useful when you wish to select various ranges of data from a collection of alternative worksheets.

INDIRECT Function

The INDIRECT worksheet function is a useful tool for creating cell or range references "on the fly", as a formula is evaluated, rather than "hard coding" them into the formula.  Microsoft's on-line Help documentation of this function only hints at is usefulness.  

In its simplest usage, the INDIRECT function allows you to put the address of one cell in another, and get data from the the first cell by referencing the second.  For example, if cell A1 has the value "C3", then =INDIRECT(A1) will return the value in C3.   

The real power of the INDIRECT function is that it can turn any string into a reference. This includes any string that you build up using string constants and the values of other cells in the formula, strung together with the & concatenation operator.   For example, the simple formula

=SUM(A5:A10)

will sum the values in the range A5:A10.  However, suppose you want to be able to specify which range of rows to sum "on the fly", without having to change the formula.  The INDIRECT function allows you to do this.  Suppose you put your starting row cell B1, and your ending row in C1.  Then, you can use the formula

=SUM(INDIRECT("A"&B1&":A"&C1))

The argument to the INDIRECT function is

"A"&B1&":A"&C1

If B1 contains 5 and C1 contains 10, this evaluates to the string  "A5:A10".  The INDIRECT function converts this string to an actual range reference, which is passed to the SUM function.

For VBA formulas to return relative sheet names (e.g., the name of the next or previous worksheet), see the Referencing Worksheets From Formulas page.

Another useful feature of the INDIRECT function is that since it takes string argument, you can use it to work with cell references that you don't want Excel to automatically change when you insert or delete rows.   Normally, Excel will change cell references when you insert or delete rows or columns, even when you use absolute referencing. If you have the formula  =SUM($A$1:$A$10), and then insert a row at row 5, Excel will convert the formula to =SUM($A$1:$A$11). If you don't want this to happen, use the INDIRECT function to change a text string to a reference:

=SUM(INDIRECT("A1:A10"))

Since Excel sees "A1:A10" as a text string rather than a range reference, it will not change it when rows or columns are deleted or inserted.

This feature is important when working with some array formulas.  Frequently, an array formula will use the ROW() function to return an array of numbers.  For example, the following formula will return the average of the 10 largest numbers in the range A1:A60 :

=AVERAGE(LARGE(A1:A60,ROW(1:10)))

However, if you insert a row between rows 1 and 10, Excel will change the formula to

=AVERAGE(LARGE(A1:A60,ROW(1:11)))

which will return the average of the 11 largest numbers.  If we use the  function with a string, Excel won't change the reference, so the formula will remain correct, regardless of whether and where rows are inserted or deleted.

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))

You can use the INDIRECT function in conjunction with the ADDRESS function. The ADDRESS function uses row and column numbers to create a string address. For example, the formula =ADDRESS(5,6) returns the string $F$5, since $F$5 is the 5th row of column 6. You can use then pass this to  INDIRECT to get the value in cell F5. For example,  =INDIRECT(ADDRESS(5,6)) . While this example may seem trivial, it illustrates a technique that you can use to build more complicated formulas.

INDIRECT Function Arguments The INDIRECT function has two arguments: ref_text: A cell reference or text string (or both), that create the range reference. The referenced range can be a cell, a range of cells, or a named range. a1: TRUE or FALSE. Does the reference use A1 reference style? If this argument is TRUE, or omitted, the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1 style. Note: If the INDIRECT formula refers to a different workbook, that workbook must be open, or the formula will return a #REF! error.

Lock a Cell Reference If you create a simple link to a cell, e.g. =A4, then insert a row above row 4, your formula will automatically change to =A5. To prevent this change, you can use the INDIRECT function. To demonstrate the difference, you'll create two formulas -- one that uses a simple link, and one that uses a text string in an INDIRECT formula.

In cell A4, type a number (10 in this example). In cell B2, type the following formula:    =INDIRECT("A4") Press the Enter key, and the formula will return the number in cell A4. In cell C2, type the following formula:    =A4 Press the Enter key, and the formula will return the number in cell A4.  To insert a row, right-click on the Row 4 button, and from the pop-up menu, choose Insert. As shown at right, cell B2, which contains the INDIRECT formula, now returns 0, because cell A4 is empty. The text string, "A4", in the INDIRECT formula did not change when the row was inserted. Cell C2, which contains the link, still returns 10, because its formula has changed to =A5.