Microsoft Office Tutorials and References

In Depth Information

**Specifying worksheet names indirectly**

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.

Specifying worksheet names indirectly

Figure 50-2 shows another example, this time using a worksheet reference.

Column A, on the Summary worksheet, contains text that corresponds to other worksheets in the

workbook. Column B contains formulas that reference these text items. For example, the formula in

cell B2 is

=SUM(INDIRECT(A2&”!F1:F10”))

Figure 50-2:
Using the INDIRECT function to create references.

This formula concatenates the text in A2 with a range reference. The INDIRECT function evaluates the

result and converts it to an actual range reference. The result is equivalent to this formula:

=SUM(North!F1:F10)

This formula is copied down the column. Each formula returns the sum of range F1:F10 on the

corresponding worksheet.