Page 207 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

Inside Out

Names as Formulas
Excel actually stores the names of ranges as a formula. You can take advantage of this han­
dling in several ways to enhance the shortcuts you use in your spreadsheets. Ordinarily, you
cannot use a name more than once within a single workbook, but what if you have a work-
book with multiple sheets that are all similar, such as the Y2001ByMonth.xls workbook? It
would be handy to be able to use the same name to refer to the same area of a worksheet
Chapter 8
regardless of which worksheet it is. You can do this by specifying the name to be specific
to the worksheet and not available to the entire workbook.

Select the cells you want to name as you usually would, and click in the name box to type
a name. This time, instead of just typing the name, type the name of the worksheet first,
followed by an exclamation point (!), and then the name of the range. (You must still follow
normal naming rules behind the exclamation point.)

The exclamation point serves as a separator between the sheet name and the range
name. When Excel sees a sheet name, it knows to define the name as being specific to
the worksheet.
You can also expand names even further by using relative references. Names will use an
absolute reference by default, but if you enter the range that the name refers to manually
by clicking Insert, Name, Define, you can use a relative reference. As an example, open the
Y2001ByMonth.xls workbook, display the January worksheet, and follow these steps:
1 Select any cell in column A.
2 Click Insert, Name, Define.
3 In the Name box, type DailyValues.
4 In the Refers To box, type =A$6:A$36.
5 Click the Add button and then the OK button.
6 Select cell D38.
7 Type in =Sum(DailyValues) and press Enter.

continued

181