Microsoft Office Tutorials and References
In Depth Information
Advanced Techniques That Use Names
This formula uses an absolute column reference and a relative row reference. Therefore, it always
returns a value in column A. The row depends on the row in which you use the formula. For
example, if you enter the following formula into cell F12, it displays the contents of cell A12:
=FirstInRow
You cannot use the FirstInRow formula in column A because it generates a circular
reference — a formula that refers to itself. I discuss circular references in Chapter 16.
Advanced Techniques That Use Names
This section presents several examples of advanced techniques that use names. The examples
assume that you’re familiar with the naming techniques described earlier in this chapter.
Using the INDIRECT function with a named range
Excel’s INDIRECT function lets you specify a cell address indirectly. For example, if cell A1
contains the text C45, this formula returns the contents of cell C45:
=INDIRECT(A1)
The INDIRECT function also works with named ranges. Figure 3-22 shows a worksheet with 12
range names that correspond to the month names. For example, January refers to the range
B2:E2. Cell B16 contains the following formula:
=SUM(INDIRECT(A16))
This formula returns the sum of the named range entered as text in cell A16.
You can use the Data➜Data Tools➜Data Validation command to insert a drop-down
list box in cell A16. (Use the List option in the Data Validation dialog box, and specify
A2:A13 as the list source.) This allows the user to select a month name from a list; the
total for the selected month then displays in B16.
 
Search JabSto ::




Custom Search