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 :
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:
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:
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:
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: