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 Costs.
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 CostsYear1, 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 CostsYear1!Inflation.
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.