Congratulations! 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.
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
The INDIRECT function has the following syntax:
- 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
- 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:
In cell E8, the INDIRECT function creates a reference to the starting cell, E2:
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:
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.
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)
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:
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.
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)
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:
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:
A third option is to use the ROW function with INDIRECT, as in the formula in cell D6, which is also array-entered:
The results for all 3 formulas are the same.
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.
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