A Name s Scope

A Name's Scope

Before I get into creating and working with names, it's important to understand that all names have a scope. A name's scope defines where you can use the name. Names are scoped either at the workbook level or at the worksheet level for a particular sheet.

Referencing Names

You can refer to a workbook level name just by using its name from any sheet in the workbook. For worksheet level names, you must precede the name with the name of the worksheet unless you're using it on its own worksheet.

For example, assume you have a workbook with two sheets, Sheet1 and Sheet2. In this workbook, you have Total_Sales (a workbook level name), North_Sales (a worksheet level name on Sheet1), and South_Sales (a worksheet level name on Sheet2). On Sheet1 or Sheet2, you can refer to Total_Sales by simply using the name:

 =Total_Sales 

If you're on Sheet1 and you want to refer to North_Sales, you can use a similar formula because North_Sales is defined on Sheet1:

 =North_Sales 

However, if you want to refer to South_Sales on Sheet1, you'll need to do a little more work. Sheet1 can't "see" the name South_Sales because it's defined on another sheet. Sheet1 can only see workbook level names and worksheet level names defined on Sheet1. To refer to South_Sales on Sheet1, prefix the name with the worksheet name and an exclamation point:

 =Sheet2!South_Sales 

Tip 

If your worksheet name contains a space, enclose the worksheet name in single quotes when referring to a name defined on that sheet:

     ='My Sheet'!My_Name 

Generally, it's a good practice to scope your names as narrowly as possible. If you will be using a name on only one worksheet, set that name's scope at the worksheet level. For names that you want to use throughout your workbook, a workbook level scope is more appropriate.

Note 

Only the worksheet-level names on the current sheet appear in the Name box. Similarly, only worksheet-level names on the current sheet appear in the list under Formulas Defined Names Use in Formulas.

Referencing Names from Another Workbook

Chapter 2 described how to use links to reference cells or ranges in other workbooks. The same rules apply when using names defined in another workbook.

For example, the following formula uses a range named MonthlySales, defined in a work- book named Budget.xlsx (which is assumed to be open):

 =AVERAGE(Budget.xlsx!MonthlySales) 

If the name MonthlySales is a worksheet level name on Sheet1, the formula looks like this:

 =AVERAGE([Budget.xlsx]Sheet1!MonthlySales) 

Conflicting Names

Using worksheet-level names can be a bit confusing because Excel lets you define worksheet- level names even if the workbook contains the same name as a workbook-level name. In such a case, the worksheet-level name takes precedence over the workbook-level name but only in the worksheet in which you defined the sheet-level name.

For example, you can define a workbook-level name of Total for a cell on Sheet1. You can also define a worksheet-level name of Sheet2!Total. When Sheet2 is active, Total refers to the worksheet-level name. When any other sheet is active, Total refers to the workbook- level name. Confusing? Probably. To make your life easier, I recommend that you simply avoid using the same name at the workbook level and worksheet level.

One way you can avoid this type of conflict is to adopt a naming convention when you create names. By using a naming convention, your names will tell you more about themselves. For instance, you could prefix all your workbook level names with wb and your worksheet level names with ws. With this method, you'll never confuse wbTotal with wsTotal.