The Secret to Understanding Names | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Excel users often refer to named ranges and named cells. In fact, I've used these terms frequently throughout this chapter. Actually, this terminology is not quite accurate.

Here's the secret to understanding names: When you create a name, you're actually creating a named formula. Unlike a normal formula, a named formula doesn't exist in a cell. Rather, it exists in Excel's memory.

This is not exactly an earth-shaking revelation, but keeping this "secret" in mind will help you understand the advanced naming techniques that follow.

When you work with the Name Manager dialog box, the Refers To field contains the formula, and the Name field contains the formula's name. You'll find that the contents of the Refers To field always begin with an equal sign, which makes it a formula.

As you can see in Figure 3-16, the workbook contains a name (InterestRate) for cell B1 on Sheet1. The Refers To field lists the following formula:

 =Sheet1!$B$1

image from book
Figure 3-16: Technically, the name InterestRate is a named formula, not a named cell.

Whenever you use the name InterestRate, Excel actually evaluates the formula with that name and returns the result. For example, you might type this formula into a cell:

 =InterestRate*1.05

When Excel evaluates this formula, it first evaluates the formula named InterestRate (which exists only in memory, not in a cell). It then multiplies the result of this named formula by 1.05 and displays the result. This cell formula, of course, is equivalent to the following formula, which uses the actual cell reference instead of the name:

 =Sheet1!$B$1*1.05

At this point, you may be wondering whether it's possible to create a named formula that doesn't contain any cell references. The answer comes in the next section.

Naming Constants

Consider a worksheet that generates an invoice and calculates sales tax for a sales amount. The common approach is to insert the sales tax rate value into a cell and then use this cell reference in your formulas. To make things easier, you probably would name this cell something like SalesTax.

You can handle this situation another way. Figure 3-17 demonstrates the following steps:

Choose Formulas Defined Names Define Name to bring up the New Name dialog box.
Enter the name (in this case, SalesTax) into the Name field.
Click the Refers To box, delete its contents and replace it with a simple formula, such as =.075.
Click OK to close the dialog box.

image from book
Figure 3-17: Defining a name that refers to a constant.

The preceding steps create a named formula that doesn't use any cell references. To try it out, enter the following formula into any cell:

 =SalesTax

This simple formula returns .075, the result of the formula named SalesTax. Because this named formula always returns the same result, you can think of it as a named constant. And you can use this constant in a more complex formula, such as the following:

 =A1*SalesTax

If you didn't change the scope from the default of Workbook, you can use SalesTax in any worksheet in the workbook.

Naming Text Constants

In the preceding example, the constant consisted of a numeric value. A constant can also consist of text. For example, you can define a constant for a company's name. You can use the New Name dialog box to create the following formula named MS:

 ="Microsoft Corporation"

Then you can use a cell formula such as

 ="Annual Report: "&MS

This formula returns the text, Annual Report: Microsoft Corporation.

Note

Names that do not refer to ranges do not appear in the Name box or in the Go To dialog box (which appears when you press F5). This makes sense because these constants don't reside anywhere tangible. They do appear in the Paste Names dialog box and in Formula AutoComplete, however, which does make sense because you'll use these names in formulas.

As you might expect, you can change the value of the constant at any time by accessing the Name Manager dialog box and simply changing the formula in the Refers To field. When you close the dialog box, Excel uses the new value to recalculate the formulas that use this name.

Although this technique is useful in many situations, changing the value takes some time. Having a constant located in a cell makes it much easier to modify. If the value is truly a "constant," however, you won't need to change it.

Using Worksheet Functions in Named Formulas

Figure 3-18 shows another example of a named formula. In this case, the formula is named ThisMonth, and the actual formula is

image from book
Figure 3-18: Defining a named formula that uses worksheet functions.

 =MONTH(TODAY())

The formula in Figure 3-18 uses two worksheet functions. The TODAY function returns the current date, and the MONTH function returns the month number of its date argument. Therefore, you can enter a formula such as the following into a cell and it will return the number of the current month. For example, if the current month is April, the formula returns 4.

 =ThisMonth

A more useful named formula would return the actual month name as text. To do so, create a formula named MonthName, defined as

 =TEXT(TODAY(),"mmmm")

Cross Ref

See Chapter 5 for more information about Excel's TEXT function.

Now enter the following formula into a cell and it returns the current month name as text. In the month of April, the formula returns the text, April.

 =MonthName

Using Cell and Range References in Named Formulas

Figure 3-19 shows yet another example of creating a named formula, this time with a cell reference. This formula, named FirstChar, returns the first character of the contents of cell A1 on Sheet1. This formula uses the LEFT function, which returns characters from the left part of a text string. The named formula is

image from book
Figure 3-19: Defining a named formula that uses a cell reference.

 =LEFT(Sheet1!$A$1,1)

After creating this named formula, you can enter the following formula into a cell. The formula always returns the first character of cell A1 on Sheet1.

 =FirstChar

The next example uses a range reference in a named formula. Figure 3-20 shows the New Name dialog box when defining the following named formula (named Total ).

image from book
Figure 3-20: Defining a named formula that uses a range reference.

 =SUM(Sheet1!$A$1:$D$4)

After creating this named formula, you can enter the following formula into any cell on any sheet. The formula returns the sum of the values in A1:D4 on Sheet1.

 =Total

Notice that the cell references in the two preceding named formulas are absolute references. By default, all cell and range references in named formulas use an absolute reference, with the worksheet qualifier. But, as you can see in the next section, overriding this default behavior by using a relative cell reference can result in some very interesting named formulas.

Using Named Formulas with Relative References

As I noted previously, when you use the New Name dialog box to create a named formula that refers to cells or ranges, the Refers To field always uses absolute cell references and the references include the sheet name qualifier. In this section, I describe how to use relative cell and range references in named formulas.

USING A RELATIVE CELL REFERENCE

Begin with a simple example by following these steps to create a named formula that uses a relative reference:

Start with an empty worksheet.
Select cell A1 (this step is very important).
Choose Formulas Defined Names Define Name to bring up the New Name dialog box.
Enter CellToRight in the Name field.
Delete the contents of the Refers To field and type the following formula (don't point to the cell in the sheet):
```
 =Sheet1!B1 
```
Click OK to close the New Name dialog box.
Type something (anything) into cell B1.
Enter this formula into cell A1:
```
 =CellToRight 
```
You'll find that the formula in A1 simply returns the contents of cell B1.

Next, copy the formula in cell A1 down a few rows. Then enter some values in column B. You'll find that the formula in column A returns the contents of the cell to the right. In other words, the named formula (CellToRight) acts in a relative manner.

You can use the CellToRight name in any cell (not just cells in column A). For example, if you enter =CellToRight into cell D12, it returns the contents of cell E12.

To demonstrate that the formula named CellToRight truly uses a relative cell reference, activate any cell other than cell A1 and display the New Name dialog box (see Figure 3-21). You'll see that the Refers To field contains a formula that points one cell to the right of the active cell, not A1. For example, if cell E5 is selected when the New Name dialog box is displayed, the formula for CellToRight appears as

image from book
Figure 3-21: The CellToRight named formula varies, depending on the active cell.

 =Sheet1!F5

If you use the CellToRight name on a different worksheet, you'll find that it continues to reference the cell to the right-but it's the cell with the same address on Sheet1. This happens because the named formula includes a sheet reference. To modify the named formula so it works on any sheet, follow these steps:

Activate cell A1 on Sheet1.
Choose Formulas Defined Names Name Manager to bring up the Name Manager dialog box.
In the Name Manager dialog box, select the CellToRight item in the list box.
Delete the contents of the Refers To field and type this formula:
```
 =!B1 
```
Click OK to close the Define Name dialog box.

After making this change, you'll find that the CellToRight named formula works correctly on any worksheet in the workbook.

Note

The named formula does not work if you use it in a formula in column XFD because the formula attempts to reference a nonexistent cell. (There is no column to the right of column XFD.)

USING A RELATIVE RANGE REFERENCE

This example expands upon the previous example and demonstrates how to create a named formula that sums the values in ten cells directly to the right of a particular cell. To create this named formula, follow these steps:

Activate cell A1.
Choose Formulas Defined Names Define Name to bring up the New Name dialog box.
Enter Sum10Cells in the Name field.
Enter this formula in the Refers To field:
```
 =SUM(!B1:!K1) 
```

After creating this named formula, you can insert the following formula into any cell in any sheet, and it will display the sum of the ten cells directly to the right:

 =Sum10Cells

For example, if you enter this formula into cell D12, it returns the sum of the values in the ten-cell range E12:N12.

Note that because cell A1 was the active cell when you defined the named formula, the relative references used in the formula definition are relative to cell A1. Also note that the sheet name was not used in the formula. Omitting the sheet name (but including the exclamation point) causes the named formula to work in any sheet.

If you select cell D12 and then bring up the Name Manager dialog box, you'll see that the Refers To field for the Sum10Cells name displays the following:

 =SUM(!E12:!N12)

Note

The Sum10Cells named formula does not work if you use it in a cell that resides in a column beyond column XET. That's because the formula becomes invalid as it tries to reference a nonexistent cell beyond column XFD.

USING A MIXED RANGE REFERENCE

As I discuss in Chapter 2, a cell reference can be absolute, relative, or mixed. A mixed cell reference consists of either of the following:

An absolute column reference and a relative row reference (for example, $A1)
A relative column reference and an absolute row reference (for example, A$1)

As you might expect, a named formula can use mixed cell references. To demonstrate, activate cell B1. Use the New Name dialog box to create a formula named FirstInRow, using this formula definition:

 =!$A1

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

Note

You cannot use the FirstInRow formula in column A because it generates a circular reference-a formula that refers to itself.