Microsoft Office Tutorials and References

In Depth Information

**Using Dynamic Names**

Figure 91-2:
Creating a dynamic named formula.

The preceding steps create a named formula that uses the Excel OFFSET and COUNTA functions

to return a range that changes, based on the number of non-empty cells in column B. 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. To verify that the name refers to only the

nonblank cells, try this formula:

=ROWS(SalesData)

The formula returns 5, the number of rows in the range. Add more data or delete data from the

end of the column, and you see that the SalesData name adjusts according.

Note that SalesData isn’t displayed in the Name box and doesn’t appear in the Go To dialog box.

You can, however, open the Go To dialog box and type
SalesData
to select the range.

This formula works only for data that doesn’t contain gaps. In this example, if cell B3 is

empty, the SalesData name doesn’t include the last value in the column.

At this point, you might 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)

One of the most common uses for a dynamic named formula is for setting up data to be used in a

chart. You can use this technique to create a chart with a data series that adjusts automatically as

you enter new data:

=Sheet1!$E$1:$K$490