Microsoft Office Tutorials and References
In Depth Information
USE NATURAL LANGUAGE FORMULAS WITHOUT USING NATURAL LANGUAGE FORMULAS
Solution: You can use INDIRECT("B10") to ensure that the formula always
points to cell B10. Even if you delete or insert rows, and even if you cut B10
and paste to B99, your formula will always point to B10 (Figure 39).
Figure 39. INDIRECT("B10") tells Excel to
always look in cell B10.
Gotcha: If you are a fan of formula auditing, note that the Trace Dependents
and Trace Precedents commands do not recognize the relationship between
cell B10 and the formula in Figure 39. If you use Trace Dependents from cell
B10, Excel will report that there are no dependents.
Summary: To force a formula to always point to cell B10, you can use
USE NATURAL LANGUAGE FORMULAS WITHOUT
USING NATURAL LANGUAGE FORMULAS
Challenge: Excel 2003 offers relatively obscure natural language formulas, but
they were removed from Excel 2007. The table nomenclature in Excel 2007 isn’t
as easy to use.
Solution: To solve this problem, you can use the intersection character in your
SUM function. Everyone knows that =SUM(A2:A10) sums the nine-cell range
from A2 through A10. Most people realize that =SUM(A1,A3,A5,A7,A9) adds
up the ﬁ ve cells speciﬁ ed. However, very few people understand that the space
character is actually an intersection operator when used in a SUM function!
Say that you have the worksheet shown in Figure 40. As discussed in “Quickly
Create Many Range Names” on page 76, you can add range names by using
Select the range A1:F13.
Use Insert, Name, Create (in Excel 2003) or Formulas, Create from Selection
(in Excel 2007).
In the Create Names or Create Names from Selection dialog, select Top
Row and Left Column. Click OK. Excel creates names for the 12 cities in
column A. For example, the name Louisville applies to cells B13:F13. Excel