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




Custom Search