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:
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:
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:
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: