Microsoft Office Tutorials and References
In Depth Information
=AVERAGE(‘Annual Budget.xlsx'!MonthlySales)
If the name MonthlySales is a worksheet-level name on Sheet1, the formula looks like this:
=AVERAGE(‘[Annual Budget.xlsx]Sheet1'!MonthlySales)
If you use the pointing method to create such formulas, Excel takes care of the details automatically.
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
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 easi-
er, I recommend that you simply avoid using the same name at the workbook and worksheet levels.
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 con-
fuse wbTotal with wsTotal.
The Name Manager
Now that you understand the concept of scope, you can start creating and using names. Excel has a handy fea-
ture for maintaining names called the Name Manager, shown in Figure 3-1.
Search JabSto ::

Custom Search