Microsoft Office Tutorials and References

In Depth Information

don't reside anywhere tangible. They do appear in the Paste Names dialog box and in

the Formula AutoComplete drop-down list, however, which does make sense because

you'll use these names in formulas.

As you might expect, you can change the value of the constant at any time by accessing the Name Manager dia-

log box and changing the formula in the Refers To field. When you close the dialog box, Excel uses the new

value to recalculate the formulas that use this name.

Although this technique is useful in many situations, changing the value takes some time. Having a constant

located in a cell makes it much easier to modify.

Using worksheet functions in named formulas

Figure 3-19 shows another example of a named formula. In this case, the formula is named
ThisMonth,
and the

actual formula is this:

=MONTH(TODAY())

Figure 3-19:
Defining a named formula that uses worksheet functions.

The formula in Figure 3-19 uses two worksheet functions. The TODAY function returns the current date, and

the MONTH function returns the month number of its date argument. Therefore, you can enter a formula such

as the following into a cell and it will return the number of the current month. For example, if the current month

is April, the formula returns 4.

=ThisMonth

A more useful named formula would return the actual month name as text. To do so, create a formula named

MonthName,
defined as follows:

=TEXT(TODAY(),”mmmm”)