30 Excel Functions in 30 Days: 30 – INDIRECT

Icon30DayCongratulations! You made it to the final day in the 30XL30D challenge. It’s been an long, and interesting, journey, and I hope you learned a few useful things about Excel functions along the way. Tomorrow, I’ll do a wrap-up article, and let you know how the functions ranked in the pre-challenge voting, last month.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

For day 30, we’ll examine the INDIRECT function, which returns the reference specified by a text string. It’s one of the ways that you can create a dependent data validation drop down list, where, for example, the selection in the Country drop down controls the choices in the City drop down.

So, let’s take a look at the INDIRECT information and examples, and if you have other tips or examples, please share them in the comments.

Function 30: INDIRECT

The INDIRECT function returns the reference specified by a text string.

Indirect00

How Could You Use INDIRECT?

The INDIRECT function returns the reference specified by a text string, so you can use it to:

  • Create starting reference that doesn’t shift
  • Create reference to static named range
  • Create reference from sheet, row, column info
  • Create array of numbers that doesn’t shift

INDIRECT Syntax

The INDIRECT function has the following syntax:

  • INDIRECT(ref_text,a1)
    • ref_text is the text string for a reference.
    • a1 if TRUE or omitted, uses an A1 reference style; if FALSE, the R1C1 reference style is used

INDIRECT Traps

  • The INDIRECT function is volatile, so it could slow down your workbook, if used in many formulas.
  • If the INDIRECT function creates a reference to another workbook, that workbook must be open, or the formula will result in a #REF! error.
  • If the INDIRECT function creates a reference to a range outside the row and column limit, the formula will result in a #REF! error. (Excel 2007 and Excel 2010)
  • The INDIRECT function cannot resolve a reference to a dynamic named range

Example 1: Create starting reference that doesn’t shift

In the first example, there are identical numbers in columns C and E, and the totals are the same, using the SUM function. However, the formulas are slightly different. In cell C8, the formula is:

=SUM(C2:C7)

In cell E8, the INDIRECT function creates a reference to the starting cell, E2:

=SUM(INDIRECT(“E2”):E7)

Indirect01a

If a row is inserted at the top of the lists, and January amounts are entered, the total in column C doesn’t change. The formula changed, adjusting to the inserted row:

=SUM(C3:C8)

However, the INDIRECT function locked the starting cell to E2, so the January amount is automatically included in the column E total. The ending cell changed, but the starting cell wasn’t affected.

=SUM(INDIRECT(“E2”):E8)

Indirect01b

Example 2: Create reference to static named range

The INDIRECT function can also create a reference for a named range. In this example, the blue cells are in a range named NumList. There is also a dynamic range in column B, based on the count of numbers in that column.

The total for either range can be calculated, by using the range name with the SUM function, as you can see in cells E3 and E4

=SUM(NumList)  or =SUM(NumListDyn)

Indirect02a

Instead of typing the name in the SUM formula, you can refer to the range name in a worksheet cell. For example, with the name NumList in cell D7, the formula in cell E7 is:

=SUM(INDIRECT(D7))

Unfortunately, the INDIRECT function can’t resolve a dynamic range, so when the formula is copied down to cell E8, the result is a #REF! error.

Indirect02b

Example 3: Create reference from sheet, row, column info

You can easily create a reference based on row and column numbers, by using FALSE as the second argument in the INDIRECT function. This creates an R1C1 style reference, and in this example, a sheet name is also included — ‘MyLinks’!R2C2

=INDIRECT(“‘” & B3 & “‘!R” & C3 & “C” & D3,FALSE)

Indirect03

Example 4: Create array of numbers that doesn’t shift

In some formulas, you need an array of numbers, as in this example, where we want the average of the 3 highest numbers in column B. The numbers could be typed in the formula, as they are in cell D4:

=AVERAGE(LARGE(B1:B8,{1,2,3}))

If you need a bigger array of numbers, you probably wouldn’t want to type all of them. Another option is to use the ROW function, as in the array-entered formula in cell D5:

=AVERAGE(LARGE(B1:B8,ROW(1:3)))

A third option is to use the ROW function with INDIRECT, as in the formula in cell D6, which is also array-entered:

=AVERAGE(LARGE(B1:B8,ROW(INDIRECT(“1:3”))))

The results for all 3 formulas are the same.

Indirect04a

However, if rows are inserted at the top of the sheet, the second formula returns an incorrect result, because the rows are adjusted. Now, instead of the average of the top 3 numbers, it shows the average of the 3rd, 4th and 5th largest numbers.

With the INDIRECT function, the third formula keeps the correct row reference, and continues to show the correct result.

Indirect04b

Download the INDIRECT Function File

To see the formulas used in today’s examples, you can download the INDIRECT function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the INDIRECT Video

To see a demonstration of the examples in the INDIRECT function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Excel INDIRECT Function Gets Reference from Text String


_____________

You may also like...

12 Responses

  1. Gregory says:

    I’m glad you finished with INDIRECT, I was getting worried you were going to skip that one. Thanks for all the great examples.

  2. Shairal says:

    The best 30 days!! Thanks for all the time and effort you put into these postings. They have been great and I’ve learned a lot!

  3. Jim Cone says:

    Nicely done and very worthwhile.

  4. Thanks! I’ve learned lots too. Maybe we’ll tackle another function category later.

  5. Truely Debra these were amazing 30 days! I have learned a lot through your post and then via comments shared by people.

    I must say your were all over the places during these days. Be it twitter or google.

    You Rocked!

  6. Contextures Blog » 30 Excel Functions in 30 Days: Conclusion says:

    […] 30 Excel Functions in 30 Days: 30 – INDIRECT […]

  7. Thanks Prakash! I’m glad you liked the series.

  8. Luke Wisbey says:

    Regards INDIRECT – you can use it to handle non contiguous ranges – eg:

    {=MIN(IF(N(INDIRECT({“N4?, “N7?, “N15?,”N22?}))>TODAY(),N(INDIRECT({“N4?, “N7?, “N15?,”N22?}))))}

    though you could equally use CHOOSE for the above

    INDIRECT can also be used in conjunction with SUMPRODUCT for conditional 3D calculations, eg:

    =SUMPRODUCT(SUMIF(INDIRECT(“‘”&SheetList&”‘!A1:A100?),”apple”,INDIRECT(“‘”&SheetList&”‘!B1:B100?)))

    the above conducts a 3D SUMIF where SheetList is named range containing the sheet names of those to be included in the calculation

    Wouldn’t go so far as to say you should use this (inefficient & Volatile) – just that you can

  9. David Tyler says:

    Saving the best for last? I think this was my favorite post of the 30 days, which is saying something considering how great all the others were. Thanks for sharing these in such an easily digestible format.

  10. Sachin Oak says:

    Excellent presentation of topic, I don’t think one would get such simplified explanation of Excel formula.

    Great Job Debra!!!

  11. Atanu Chanda says:

    Debra,

    Awesome breakdown of syntax into smaller bits to make all understand. I have become a good follower of yours after going thru these posts on formulas. I hang up on different blogs and myself try to resolve a few issues in excel and have seen hundreds of articles on how to use these formulas in different example but this one of yours really gives some insight to the operational usage and lucid explanation.

    Keep up the good job !!!!!!

Leave a Reply

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