ReportDate contains the date serial number for 1/1/2006. And Company contains text.
In both these examples, it probably would make more sense to set up cells in the workbook to contain these two values. But that's not the point. The point is that Excel isn't limited to naming ranges in a worksheet; Excel also can name text and numeric values.
Names also can name formulas, which can return values. To illustrate, you could define:
Here, the name MyPayment defines a formula that relies on other names
to return a payment amount. Those other names could refer to cells in a
spreadsheet or they could contain the actual values.
Excel Workbook Names That Return References
Most Excel names and definitions look something like this:
These Excel names have a workbook scope. You can use them in any worksheet in your workbook. To illustrate you could use these formulas anywhere in your workbook:
These names reference specific ranges. Therefore, when you insert or
delete rows above these ranges, or columns to the left of them, the
definition of the names will adjust accordingly.
Excel Worksheet Names That Return References
You can define Excel names that typically are used only in a single worksheet. These are said to have a worksheet scope. To define these names, include the name of the worksheet as part of the range name. To illustrate, you could create these names and definitions:
To define these names, enter Sheet2!CurrentDate and Sheet2!Sales as the name in the Define Name dialog.
In Sheet2, you would use these names just as you would use a name with a workbook scope. For example, you could enter =SUM(Sales). When you do so, Excel returns the sum of the Sales range defined on Sheet2.
In Sheet3, you would enter:
=SUM(Sales) to refer to the name with the workbook scope, defined for Sheet1.
=SUM(Sheet2!Sales) to refer to the name with the worksheet scope defined for Sheet2.
As with the first category above, because these names refer to specific ranges they adjust as you insert or delete rows or columns.
In each of these formulas, the INDIRECT function converts the reference as text in R1C1 notation into an actual reference. Here's what the R1C1 notation means in each instance
The second argument in each of these examples tells Excel that the text is in R1C1 style. Using 1 or TRUE in the second argument would tell Excel that the text reference is in A1 style. However, using an A1-style reference wouldn't work in this instance, because INDIRECT would return a reference to any specific cell you specify.
By the way, you might have seen a name defined like this:
|With cell B2 active...|
This style is dangerous. In fact, any reference like "=![Ref]" is dangerous. It can cause a crash in some versions of Excel. It also can create problems when used with VBA.
Avoid the "=![Ref]" reference style in Excel.
If you want to use relative references with names that have a worksheet scope, you have two choices. First, you can use INDIRECT formulas with R1C1, as shown above. Second, you can use relative references that include the sheet name as shown here:
|With cell B2 active...|
Notice that the definitions of all of these names exclude the "$" in their references. Therefore, all the names return a reference relative to the cell that was active when these names were defined. Because cell B2 was active, these three names serve the same function as the names above that use the INDIRECT formulas.
Names with a workbook scope must use the R1C1 style with INDIRECT, as illustrated here:
In all worksheets in your workbook, MyRow1 always refers to row 1 in the current column of the current worksheet. And MyColumnA always refers to column A in the current row of the current worksheet.
Notice, however, that the fixed rows and columns don't change if you insert new rows or columns in your worksheet. They only change if you redefine your range name.
Names with a worksheet scope can use standard A1-style referencing:
|With cell B2 active...|
Here, MyRow1 refers to row 1 in the current column of Sheet1. And MyColumnA refers to column A in the current row of Sheet1.
Notice, however, that because the names reference specific ranges,
the ranges will change if you insert rows above them or columns to their
left. To illustrate, if you insert a new column A, the name
Sheet1!MyColumnA then will reference column B.
Suppose that a workbook named MonthlySales.xls contains a range named Data. In some other report workbook, you could define:
When you define a name like this, it simplifies formulas that use external references. To illustrate, a formula like...
...is much easier to work with than a formula like...
If you ever want to change the source of your Data to, say,
NewMonthlySales.xls, it's easy to do. In the report workbook choose
Edit, Links, and then click the Change Source button to point all links
from MonthlySales.xls to the new workbook.
Names can use spreadsheet formulas to return cell references, as the following examples illustrate.
Names defined using the OFFSET function can return a value or reference based on the top-left cell of a reference range. Here, in the MyList example, the value in the NumRows cell determines the height of the range that MyList returns.
|=OFFSET(reference, rows, cols, height, width)|
|MyList||=OFFSET(TopLeftCell, 0, 0, NumRows, 3)|
Names defined with the INDEX function return the value or reference at the intersection of the row and column numbers...if specified. If a row or column number is omitted, the name returns the entire column or row that is specified.
|=INDEX(array, row_num, column_num)|
|MyCell||=INDEX(MyDataRange, RowNum, ColumnNum)|
|MyRow||=INDEX(MyDataRange, RowNum, )|
|MyColumn||=INDEX(MyDataRange, , ColumnNum)|
Names defined using the CHOOSE function return the value or reference from a list. Here, the ChoiceNumber could have the value of 1, 2, or 3, and return the reference specified.
|=CHOOSE(index_num, value_ref1, value_ref2, ...)|
|MyRef||=CHOOSE(ChoiceNumber, MyRef1, MyRef2, MyRef3)|
Names defined with the IF function can return values or references, based on a logical test. Here, if TestValue equals 3, the MyChoice name returns MyRef1. Otherwise, it returns MyRef2.
|=IF(logical_test, value_if_true, value_if_false)|
|MyChoice||=IF(TestValue=3, MyRef1, MyRef2)|
In a worksheet, array formulas loop through a range of values, performing various tests and returning values for the items that pass the test. To array-enter a formula, you type in the formula then hold down the Ctrl and Shift keys when you press Enter.
Names defined using array-formula logic will use that logic. To illustrate, suppose you array-enter the following formula in a cell. It will return the sum of the Ties sold in Scotland.
Here, when Product equals Ties and Region equals Scotland, Excel puts the Amount in a temporary array. Then SUM returns the sum of those amounts. In the example, the sum equals 8.
Similarly, if you define MySales using the same formula, and then enter =MySales into a cell, the cell returns the value 8.
This introduction hasn't explained the many benefits you can gain from using each of Excel's wide variety of naming techniques. Instead, I've tried to provide an overview of the key ways that you can use Excel names.
I certainly haven't explained every technique you could use. As you work with Excel, keep this power in mind. I'm certain you'll discover additional ways to gain even more power from Excel.
Learn how to create top-quality dashboard reports with Excel.