indirect function

INDIRECT(ref_text,a1)

Arguments

  • Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string.
  • a1 specifies the type of reference in argument ref_text. True or omitted ref_text is a A1-style reference. False signals that ref_text is a R1C1-style reference.

Explaining Indirect function

This is what Microsoft tells you about the indirect function:

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Let me try to explain that, cell A6 contains this formula:=INDIRECT(B6). If you enter a cell reference like B1 in cell B6, the formula in cell A6 becomes =B1 and it returns the value in cell B1. This animated picture explains it all.

indirect function example

Can it do more than that? The answer is of course yes but first a small warning, INDIRECT is a volatile function and extended use may slower your workbook considerably.

Example 1 - Avoid automatically changing cell references

When you insert or delete a row or column in excel the cell references in formulas changes, even if you use absolute cell references. The Indirect function helps you solve that problem. The following picture demonstrates what happens with relative, absolute and indirect cell references when you insert a row.

indirect example 1

Example 2 - Sum a range you specify

indirect example 2

Formula in cell E3:

=SUM(INDIRECTF3))

Cell A1 contains C3:C5. Sum function sums values in cell C3:C5. If you change the value in cell F3 to C3:C6 it sums the values in cell range C3:C6.

Example 3 - Dynamic cell ranges

A drop down list lets you select a quarter. The formula in cell G3 sums the corresponding values.

indirect example 3

Formula in cell G3:

=SUM(INDIRECT(INDEX($J$3:$J$6,MATCH(E3,I3:I6,0))))

If you want to learn more about array formulas join Advanced excel course.

If you don´t know what to do during the holidays, make sure you check out these functions and custom functions.

Functions I have written about

User defined functions

You have also the possibility to build your own functions, they are user defined functions. They are made in the vb editor using vba. Here are a some custom functions I have built.

Download excel *.xlsx file

Indirect function.xlsx