Microsoft Office Tutorials and References
In Depth Information
Using Dynamic Names
Using Dynamic Names
This tip describes a useful concept that can save you lots of time: the dynamic named formula,
which is a named formula that refers to a range that isn’t fixed in size. This idea can be difficult to
grasp, so I provide a quick example.
Examine the worksheet shown in Figure 91-1. This sheet contains a listing of sales by month,
through the month of May.
Figure 91-1: You can use a dynamic named formula to represent the sales data in column B.
Suppose that you want to create a name (SalesData) for the data in column B and you don’t
want this name to refer to empty cells. In other words, the reference for the SalesData range
changes each month as you add a new sales figure. You can, of course, use the Name Manager
dialog box to change the range name definition each month. Or, you can create a dynamic
named formula that changes automatically as you enter new data (or delete existing data).
To create a dynamic named formula, start by re-creating the worksheet shown in Figure 91-1.
Then follow these steps:
1. Select Formulas
Defined Names
Define Name to display the New Name dialog box.
2. Enter SalesData in the Name field.
3. Enter the following formula in the Refers To field (see Figure 91-2):
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
4. Click OK to close the New Name dialog box.
 
Search JabSto ::




Custom Search