Microsoft Office Tutorials and References

In Depth Information

**Using the INDIRECT Function**

Using the INDIRECT Function

To make a formula more flexible, you can use the Excel INDIRECT function to create a range

reference. This rarely used function accepts a text argument that resembles a range reference and

then converts the argument to an actual range reference. When you understand how this

function works, you can use it to create more powerful interactive spreadsheets.

Specifying rows indirectly

Figure 124-1 shows an example that uses the INDIRECT function. The formula in cell E5 is

=SUM(INDIRECT(“B”&E2&”:B”&E3))

Figure 124-1:
Using the INDIRECT function to sum user-supplied rows.

Notice that the argument for the INDIRECT function uses the concatenation operator to build a

range reference by using the values in cells E2 and E3. So, if E2 contains 2 and E3 contains 4, the

range reference evaluates to this string:

“B2:B4”

The INDIRECT function converts that string to an actual range reference, which is then passed to

the SUM function. In effect, the formula returns

=SUM(B2:B4)

When you change the values in E2 or E3, the formula is updated to display the sum of the

specified rows.