Microsoft Office Tutorials and References
In Depth Information
Figure 3-25: You can use a dynamic named formula to represent the sales data in column B.
Suppose you want to create a name ( SalesData ) for the data in column B, but you don't want this name to refer
to empty cells. In other words, the reference for the SalesData range would change each month as you add a
new sales figure. You could, of course, use the Name Manager dialog box to change the range name definition
each month. Or, you could create a dynamic named formula that changes automatically as you enter new data.
To create a dynamic named formula, start by re-creating the worksheet shown in Figure 3-25. Then follow these
steps:
1. Bring up the New Name dialog box.
2. Type SalesData in the Name field.
3. Type the following formula in the Refers To field:
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
4. Click OK to close the New Name dialog box.
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.
Search JabSto ::




Custom Search