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.

Refer to Chapter 9 for more information about tables.

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)