Microsoft Office Tutorials and References

In Depth Information

**Advanced Techniques That Use Names**

The preceding steps create a named formula that uses Excel’s OFFSET and COUNTA functions to

return a range that changes, based on the number of nonempty cells in column B.

This formula assumes that the range doesn’t contain any blank cells. For example, if cell

B2 is empty, the COUNTA function would not count that cell, and the OFFSET function

would return an incorrect range.

To try out this formula, enter the following formula into any cell not in column B:

=SUM(SalesData)

This formula returns the sum of the values in column B. Note that
SalesData
does not display in

the Name box and does not appear in the Go To dialog box. You can, however, type
SalesData

into the Name box to select the range. Or, bring up the Go To dialog box and type
SalesData
to

select the range.

At this point, you may be wondering about the value of this exercise. After all, a simple formula

such as the following does the same job, without the need to define a formula:

=SUM(B:B)

The value of using dynamic named formulas becomes apparent when creating a chart. You can

use this technique to create a chart with a data series that adjusts automatically as you enter new

data.

Using a table to store your data often eliminates the need to create dynamic ranges.

Refer to Chapter 9 for more information about tables.