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