Excel INDIRECT Function

Excel’s INDIRECT Function has many applications and perhaps its simplest is to fix a range of cells you want to reference.

For example a standard SUM function looks like this:

=SUM(B16:B24)

If you insert a row within this range the formula dynamically updates and becomes:

=SUM(B16:B25)

It will dynamically update even if you absolute the reference like this:

=SUM($B$16:$B$24)

To stop the reference dynamically updating when you insert rows or columns use the INDIRECT function like this:

=SUM(INDIRECT("B16:B24"))

In most cases you’d want your formulas to dynamically update when you insert new rows or columns but there are some instances when this is a nuisance and that’s when INDIRECT is your ally.

How Does The INDIRECT Function Work?

Excel’s INDIRECT function returns a reference specified by a text string.

The text string can be obtained by:

  • referencing another cell, or
  • you can enter it in double quotes, or
  • you can generate it by nesting other functions

And you can also use the ampersand (&) symbol to concatenate text and build your text string that way. More on using the ampersand in a moment.

Notice how the range specified in this INDIRECT function below is surrounded by double quotes, thus making it a text string.

=SUM(INDIRECT("B16:B24"))

INDIRECT Function Syntax

The syntax is:

=INDIRECT(ref_text,[a1])

ref_text – is the text string (like in the example above “$B$16:$B$24″) or the cell you’re referring to containing the text string.

a1 – this is asking you if your reference uses the A1 reference style or R1C1 style. To use the A1 style* simply omit this argument, but if you want to use R1C1 enter ‘FALSE’.

*A1 reference style is what you’re probably familiar with and it is where the columns have letters and the rows have numbers. The R1C1 style uses the R to specify a row and then the number of that row, and the C to specify a column and then the number of that column.

In the example below the INDIRECT function in cell A4 is evaluating the contents in cell C4. I could have also typed this formula like this:

=INDIRECT("C4") and my result would again be ‘This text’.

INDIRECT Excel

Ok, that’s enough theory.

Other Uses for INDIRECT

1. Generate a Reference on the Fly

By nesting it with other functions we can generate references like this one:

=SUM(INDIRECT("B27:B"&ROW(B35)))

In this example we’ve used the ROW function which returns the row number of the selected cell, in this example it’s 35. Our formula above evaluates to:

=SUM(B27:B35)

Well why didn’t I just type in =SUM(B27:B35) I hear you say. And fair enough too.

The point of this example was to give you a taste of how you can nest other functions in the INDIRECT function to derive cell references. If it ‘tastes’ good I recommend you try nesting other referencing functions like COLUMN, ADDRESS, and CELL.

Referencing Other Worksheets and Workbooks

You can reference other worksheets and workbooks with the INDIRECT function too.

References to other workbooks must be formatted like this:

=INDIRECT(" '[your_workbook_name.xlsx]your_sheet_name'!$A$3")

References to other worksheets must be formatted like this:

=INDIRECT(" 'your_sheet_name'!H34")

Note 1: spaces between double quotes and apostrophe (“ ‘[your) are so you can clearly see all of the components. You don’t actually put a space in your formula.

Note 2: you can reference other worksheets and workbooks but if you reference another workbook it must be open otherwise you will get a #REF! error.

2. Use INDIRECT with a Named Range

This is probably the second most useful application for the INDIRECT function.

Let’s say you’ve got 4 named ranges of data; North, South, East, West.

INDIRECT Excel

You’ve got a drop down list (data validation list or combo box) that allows you to choose from the 4 different regions (with names that are exactly the same as the named ranges you’ve set up for your regions).

When you make your choice the report automatically updates with the relevant figures without the need for any IF Functions or other jiggery pokery.

INDIRECT Excel

You can basically use the INDIRECT function in any formula that requires a reference to a cell or range of cells.

Download the workbook and look at the examples in more detail.

Want More Excel Formulas

Why not visit our list of Excel formulas. You’ll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy :)

FREE PDF Download
100 Excel Tips & Tricks

Excel Tips & Tricks E-Book
Just enter your details below
* indicates required

We respect your email privacy

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *

Comments

  1. kayakbob says

    Mynda – This function is considerably more practical for my current need than a series of embedded IF statements. I would never have even thought to look for a function like this without your tutorials. Thanks so much!

    kayakbob

  2. venkat says

    very good introductory lesson on indirect function. one can work on it. extremely usefu. Indirect function is one of those fucntion whilch confuses even those who are familiar with excel and excel vba. thanks a lot. is there any advanced lessons on this funcion.

      • Bill Richards says

        I have the following MAX OFFSET formula in one workbook that is referencing to another workbook. It returns a #VALUE! until the source workbook is open. Once opened data appears and is updated. I’m hoping there’s a way to make it a MAX INDEX formula or some sort of MAX LOOKUP formula to resolve this issue, but I just can’t seem to figure it out completely.

        =MAX(OFFSET(‘[C:\Desktop\Book1.xlsx]1′!$G$34,0,0,1,$A$1))

        I came up with:
        =MAX(‘[C:\Desktop\Book1.xlsx]1′!$G$34:INDEX(‘[C:\Desktop\Book1.xlsx]1′!$G$34:$AP$34,0,$A$1))

        This works fine if both workbooks are open, but when only Book2 is open it returns the correct results when a 1 or 2 is input into $A$1 & the result stays without going back to #VALUE!. Great!! However; any other number (3 through 36 in this case) returns #REF!. Open Book1 a correct result appears. Close both, Open Book2 #REF! again.

        Can you help?

        Formula is in cell A3 for Book1.xlsx sheet 1; in cell A4 for Book1.xlsx sheet 2 and so on.

        Results – If I type 4 in A1 of Book2.xlsx; then it returns the max value within Book1.xlsx G34, H34, I34, J34 (below example – Answer 199). If I type 36; then it goes through AP34; which is the last cell with data (below example – Answer 241). Row 19 in BOOK1.XLSX does have numbers 1-36.

        BOOK2.XLSX

        A B
        1 4
        2
        3 =MAX(OFFSET(‘[C:\Desktop\Book1.xlsx]1′!$G$34,0,0,1,$A$1))
        4 =MAX(OFFSET(‘[C:\Desktop\Book1.xlsx]2′!$G$34,0,0,1,$A$1))

        BOOK1.XLSX Sheet 1

        G H I J K AP
        19 1 2 3 4 5 36

        34 186 199 145 190 241 200

  3. says

    Heya i’m for the primary time here. I found this board and I find It really helpful & it helped me out a lot. I’m hoping to
    give one thing back and aid others such as you helped me.

  4. Yasir Khan - online marketer says

    Thank you for sharing these tips Mynda :)

    You portal is well organized and lightening for Excel’s lovers.

    -Yassir

Trackbacks