Microsoft Office Tutorials and References

In Depth Information

See Chapter 5 for more information about Excel's TEXT function.

Now enter the following formula into a cell, and it will return the current month name as text. In the month of

April, the formula returns the text
April
:

=MonthName

Using cell and range references in named formulas

Figure 3-20 shows yet another example of creating a named formula, this time with a cell reference. This for-

mula, named
FirstChar,
returns the first character of the contents of cell A1 on Sheet1. This formula uses the

LEFT function, which returns characters from the left part of a text string. The named formula is the following:

=LEFT(Sheet1!$A$1,1)

Figure 3-20:
Defining a named formula that uses a cell reference.

After creating this named formula, you can enter the following formula into a cell. The formula always returns

the first character of cell A1 on Sheet1:

=FirstChar

Note that if you insert a new row above row 1, the reference in the
FirstChar
name will adjust so it shows the

first character in cell A2. It's possible to create a name that always refers to a specific cell or range, even if you

insert new rows or columns. For example, suppose you want the name
FirstChar
to always refer to cell A1. You

need to modify the formula for
FirstChar
so that it users the INDIRECT function:

=LEFT(INDIRECT(“$A$1”),1)

After creating this named formula,
FirstChar
will always return the first character in cell A1, even if you insert

new rows or columns. The INDIRECT function, in the preceding formula, lets you specify a cell address indir-

ectly by using a text argument. Because the argument appears in quotation marks, it never changes.

Here's an example that uses a range reference in a named formula. The formula named
ColumnACount
returns

the number of non-empty cells in column A of Sheet1. The formula is this: