Microsoft Office Tutorials and References

In Depth Information

If you use a nonexistent name (or a name that's scoped to a different worksheet) in a formula, Excel displays a

#NAME? error, indicating that it cannot find the name you are trying to use. Often, this means that you mis-

spelled the name or that the name was deleted.

Using the intersection operators with names

Excel's range intersection operator is a single space character. The following formula, for example, displays the

sum of the cells at the intersection of two ranges: B1:C20 and A8:D8:

=SUM(B1:C20 A8:D8)

The intersection of these two ranges consists of two cells: B8 and C8.

The intersection operator also works with named ranges. Figure 3-11 shows a worksheet containing named

ranges that correspond to the row and column labels. For example,
January
refers to B2:B10, and
Arizona

refers to B2:M2. The following formula returns the contents of the cell at the intersection of the
May
range and

the
Utah
range:

=May Utah

Figure 3-11:
The formula in cell C3 uses the intersection operator.

Using a space character to separate two range references or names is known as
explicit intersection
because you

explicitly tell Excel to determine the intersection of the ranges.

Excel can also perform
implicit intersections,
which occur when Excel chooses a value from a multicell range

based on the row or column of the formula that contains the reference. An example should clear this up. Figure

3-12 shows a worksheet that contains a range (C3:C14) named
MonthNames.
Cell E8 contains the simple for-

mula shown here: