Microsoft Office Tutorials and References

In Depth Information

If you use
MonthNames
as an argument for a function, implicit intersection applies only if the function argu-

ment is interpreted as a single value. For example, if you enter this formula in cell E6, implicit intersection

works, and the formula returns 5 (the number of characters in “April”):

=LEN(MonthNames)

But if you enter this formula, implicit intersection does not apply, and the formula returns 12, the number of

cells in the
MonthNames
range:

=COUNTA(MonthNames)

Using the range operator with names

You can also use the range operator, which is a colon (:), to work with named ranges. Refer to Figure 3-11. For

example, this formula returns the sum of the values in the nine-cell range that extends from Arizona Jan (cell

B2) through Idaho March (cell D4):

=SUM((Arizona Jan):(Idaho Mar))

Referencing a single cell in a multicell named range

You can use Excel's INDEX function to return a single cell from a multicell named range. Assume that range

A1:A10 is named
DataRange.
The following formula displays the fourth value (the value in A4) in
DataRange:

=INDEX(DataRange,4)

The second and third arguments for the INDEX function are optional — although at least one of them must al-

ways be specified. The second argument (used in the preceding formula) specifies the row offset within the

DataRange
range.

If
DataRange
consists of multiple cells in a single row (for example, A1:J1), use a formula like the following

one to return the fourth element in the range. This formula omits the second argument for the INDEX function,

but uses the third argument that specifies the column offset with the
DataRange
range:

=INDEX(DataRange,,4)

If the range consists of multiple rows and columns, use both the second and third arguments for the INDEX

function. For example, if
DataRange
is defined as A1:J10, this formula returns the value in the fourth row and

fifth column of the named range
:

=INDEX(DataRange,4,5)

Applying names to existing formulas

When you create a name for a cell or range, Excel does not scan your formulas automatically and replace the

cell references with your new name. You can, however, tell Excel to “apply” names to a range of formulas.

Select the range that contains the formulas that you want to modify so they will use names rather than cell refer-

ences. Then choose Formulas
⇒
Defined Names
⇒
Define Name
⇒
Apply Names. The Apply Names dialog box ap-