Microsoft Office Tutorials and References
In Depth Information
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 func-
tion 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 fol-
lowing does the same job, without the need to define a formula:
=SUM(B:B)
Or, you could just enter this formula directly into a cell without creating a named formula:
=SUM(OFFSET(\$B\$1,0,0,COUNTA(\$B:\$B),1))
The truth is, dynamic named formulas were more important in older versions of Excel. Dynamic named formu-
las used to be the only way to create a chart that adjusted automatically as you added new data. However, with
the introduction of tables (created by using Insert Tables Table), dynamic named formulas are rarely neces-
sary. If you create a chart from data in a table, the chart adjusts automatically.
The workbook with this example is available at this book's website. The filename is dy-
namic named formula.xlsx. The named formula is also used in a chart's SERIES for-
mula, creating a self-expanding chart.
Using an XLM macro in a named formula
The final example is both interesting — because it uses an Excel 4 XLM macro function in a named formula —
and useful — because it's a relatively simple way of getting a list of filenames into a worksheet.
Start with an empty workbook, and create a formula named FileList , defined as
=FILES(Sheet1!\$A\$1)
Search JabSto ::

Custom Search