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

Figure 3-22: Using the INDIRECT function with a named range.

This formula essentially returns the sum of the named range entered as text in cell A16.

Tip 

You can use the Data Data Tools Data Validation command to insert a drop-down 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.

You can also reference worksheet-level names with the INDIRECT function. For example, suppose you have a number of worksheets named Region1, Region2, and so on. Each sheet contains a worksheet-level name called TotalSales. This formula retrieves the value from the appropriate sheet, using the sheet name typed in cell A1:

 =INDIRECT(A1&"!TotalSales") 

Using the INDIRECT Function to Create a Named Range with a Fixed Address

It's possible to create a name that always refers to a specific cell or range, even if you insert new rows or columns. For example, suppose you want a range named UpperLeft to always refer to the range A1. If you create the name using standard procedures, you'll find that inserting a new row 1 causes the UpperLeft range to change to A2. Or inserting a new column causes the UpperLeft range to change to B1. To create a named range that uses a fixed address that never changes, create a named formula using the following Refers To definition:

 =INDIRECT("$A$1") 

After creating this named formula, UpperLeft will always refer to cell A1, even if you insert new rows or columns. The INDIRECT function, in the preceding formula, lets you specify a cell address indirectly by using a text argument. Because the argument appears in quotation marks, it never changes.

Note 

Because this named formula uses a function, it does not appear in the Go To dialog box or in the Name box.

Using Arrays in Named Formulas

An array is a collection of items. You can visualize an array as a single-column vertical collection, a single-row horizontal collection, or a multirow and multicolumn collection.

Cross Ref 

Part IV of this book discusses arrays and array formulas, but this topic is also relevant when discussing names.

You specify an array by using brackets. A comma or semicolon separates each item in the array. Use a comma to separate items arranged horizontally and use a semicolon to separate items arranged vertically.

Use the New Name dialog box to create a formula named MonthNames that consists of the following formula definition:

 ={"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"} 

This formula defines a 12-item array of text strings, arranged horizontally.

Note 

When you type this formula, make sure that you include the brackets. Entering a formula into the New Name dialog box is different from entering an array formula into a cell.

After you define the MonthNames formula, you can use it in a formula. However, your formula needs to specify which array item to use. The INDEX function is perfect for this. For example, the following formula returns Aug:

 =INDEX(MonthNames,8) 

You can also display the entire 12-item array, but it requires 12 adjacent cells to do so. For example, to enter the 12 items of the array into A1:L1, follow these steps:

1. Use the New Name dialog box to create the formula named MonthNames.

2. Select the range A1:L1.

3. Type =MonthNames in the formula bar.

4. Press Ctrl+Shift+Enter.

Using Ctrl+Shift+Enter tells Excel to insert an array formula into the selected cells. In this case, the single formula is entered into 12 adjacent cells in Figure 3-23. Excel places brackets around an array formula to remind you that it's a special type of formula. If you examine any cell in A1:L1, you'll see its formula listed as

Figure 3-23: You can enter a named formula that contains a 12-item array into 12 adjacent cells.

 {=MonthNames} 

Creating a Dynamic Named Formula

A dynamic named formula is a named formula that refers to a range not fixed in size. You may find this concept difficult to grasp, so a quick example is in order.

Examine the worksheet shown in Figure 3-24. This sheet contains a listing of sales by month, through the month of May.

Figure 3-24: You can use a dynamic named formula to represent the sales data in column B.

Suppose you want to create a name (SalesData) for the data in column B, and you don't want this name to refer to empty cells. In other words, the reference for the SalesData range would change each month as you add a new sales figure. You could, of course, use the Name Manager dialog box to change the range name definition each month. Or, you could create a dynamic named formula that changes automatically as you enter new data.

To create a dynamic named formula, start by re-creating the worksheet shown in Figure 3-24. Then follow these steps:

1. Bring up the New Name dialog box.

2. Enter SalesData in the Name field.

3. Enter the following formula in the Refers To field:

    =OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1) 

4. Click OK to close the New Name dialog box.

The preceding steps create a named formula that uses Excel's OFFSET and COUNTA functions to return a range that changes, based on the number of non-empty cells in column B. To try out this formula, enter the following formula into any cell not in column B:

 =SUM(SalesData) 

This formula returns the sum of the values in column B. Note that SalesData does not display in the Name box and does not appear in the Go To dialog box. You can, however, type SalesData into the Name box to select the range. Or, bring up the Go To dialog box and type SalesData to select the range.

At this point, you may be wondering about the value of this exercise. After all, a simple formula such as the following does the same job, without the need to define a formula:

 =SUM(B:B) 

The value of using dynamic named formulas becomes apparent when creating a chart. You can use this technique to create a chart with a data series that adjusts automatically as you enter new data.