Using Cell Names (Like Programming Variables)

Problem

You frequently use a particular cell in formulas and are tired of typing the reference. You'd like a more descriptive syntax, similar to that used for variable or constant names in traditional programming languages.

Solution

Use cell names .

Discussion

Select the cell or cell range for which you want to define a name. Next, select Insert images/U2192.jpg border=0> Name images/U2192.jpg border=0> Define... from the main menu bar to open the Define Name dialog box (see in Figure 1-20).

Figure 1-20. Define Name dialog box

Enter a name for the selected cell range in the edit field under "Names in workbook." Make sure the "Refers to" field does indeed refer to the cell range you'd like to name. If you had the range selected when you opened this dialog box, then the cell range should already be correct. If you didn't have it selected, press the icon in the lower-right corner; this will allow you to select the proper range without leaving the Define Name dialog box. Once your name is entered and the cell reference is correct, press the Add button. You'll now be able to use that name to refer to the associated cell range in any formulas in your workbook.

Notice that the cell reference in the "Refers to" field in Figure 1-20 includes the sheet name, Sheet1, followed by the exclamation mark (!), which precedes the absolute A1-style cell reference. This format, using the sheet name followed by !, makes the reference refer to the specified cell on that specific sheet. Thus, you can use the name in a formula on any other sheet and it will still point to the cell on Sheet1.

I find names quite useful. Typically, if I'm performing calculations that require the use of empirical constants or commonly used data, I'll set up a specific sheet in my workbook that contains only constants and commonly used data (or formulas). Then I name these so I can refer to them throughout the workbook. I find descriptive names more intuitive and easier to remember than cryptic cell references that span sheets in a workbook.