Essential Excel: Using the INDIRECT Function.
The Indirect Function
The INDIRECT worksheet function is a great function to generate cell or range references "on the fly" or dynamically , as a formula is evaluated, rather than "hard coding" them into the formula.
In essence, the INDIRECT function returns a reference to a range.
As we will see below, it can be used in a nuber of useful ways ,from creating a reference that won't change if row or columns are inserted, to creating a reference from letters and numbers in other cells.
For example, if cell A1 has the value "C1", then =INDIRECT(A1) will return the value in C3.
But the real advantage 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, constructed with the ' & 'concatenation operator. For example,
Lets see what the formula above is doing
It is allowing us to specify which range of rows to sum "dynamically", without having to change the formula. The INDIRECT function allows you to do this.
We put our starting row in cell B10, and your ending row in C10.
The argument to the INDIRECT function is
If B10 contains 11and C1 contains 17, this evaluates to the string "A10:A17". The INDIRECT function converts this string to an actual range reference, which is passed to the SUM function.
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 alter after the insertion or deletion of rows.
Normally, Excel will change cell references when you insert or delete rows or columns, even when you use absolute referencing.
Lets say you have the formula =SUM($A$1:$A$20),
and then insert a row at row 5, Excel will convert the formula to =SUM($A$1:$A$21). If you don't want this to happen, use the INDIRECT function to change a text string to a reference:
Since Excel sees "A1:A20" as a text string rather than a range reference, it won't be altered when rows or columns are deleted or inserted.
This feature can be useful when dealing with some Array formulae.
Frequently, an array formula will use the ROW() function ( this returns the row number of a reference) to return an array of numbers.
The following formula will return the average of the 10 largest numbers in the range A1:A100 :
However, if you insert a row between rows 1 and 10, Excel will change the formula to
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.
You can use the INDIRECT function in conjunction with the ADDRESS function.
The ADDRESS function uses Row and Column numbers to create a cell 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 the INDIRECT Function to get the value in cell F5. =INDIRECT(ADDRESS(5,6))
This is a technique that you can use to build more complicated formulas.