Many names in Excel are global in scope: the same name means the same thing wherever it is used. For example, the name
Inflation might refer to cell
B2 on worksheet
Often, though, you may have many similar sheets; for example, an
Costs sheet for each year of a 5 year business plan. In such cases you might want to use a different inflation rate for each year, but you would still want to use a named range. The solution is to use a name that is local to the worksheet in question.
Names local to worksheets
You can define a name that is local to a specific worksheet by prefixing the name with the worksheet name followed by an exclamation mark. So, for example, you might define
CostsYear1!Inflation to refer to
B2 on sheet
CostsYear2!Inflation to refer to
B2 on sheet
CostsYear2, and so on.
If you use the name
Inflation in a formula on sheet
CostsYear1, it will refer to the value in cell
B2 on that sheet. Similarly, the name
Inflation when used in a formula on sheet
CostsYear2 will refer to the value for year 2.
You can use a name that is local to a specific worksheet on another sheet by using the full name with the sheet prefix, for example
If you have a local and a global name with the same name, the global name is not visible on the worksheet in which the local name is defined.
The Define Name dialog shows only global names and those that are local to the currently active worksheet. Similarly, the Name Box also omits names that are local to other worksheets.