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.
 
Search JabSto ::




Custom Search