Microsoft Office Tutorials and References

In Depth Information

**Specifying worksheet names indirectly**

Specifying worksheet names indirectly

Figure 124-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 124-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)

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

corresponding worksheet.

Making a cell reference unchangeable

Another use for the INDIRECT function is to create a reference to a cell that never changes. For

example, consider this formula, which sums the values in the first 12 rows of column A:

=SUM(A1:A12)

If you insert a new row 1, Excel changes the formula to

=SUM(A2:A13)