You have seen past lessons on the power of using names in your formulas to make the formulas more intuitive and protect against any changes in linked workbooks.
What is you have similar worksheets in a workbook. What can you do about names then if each sheet has similar information and calculations.
Excel offers you a way.
The exercise is the Names Sheet Related file. The result is the Names Sheet Related Result file.
Here is a simple worksheet calculating sales.
Suppose we also wanted to do calculations for Year 2 and Year 3 also on different sheets.
If we were just looking at this sheet on a standalone basis, we might do this setup:
Named range- Units- row 3
Named range- Price- row 4
Sales- calculated as Units times Price
We could then copy this sheet and change the numbers for year 2 and year 3. The Units X Price named range calculation for sales would work for each sheet.
However, suppose we wanted to summarize the information on a separate sheet. We could not do a sum of the Units named range, for example, because we used the same name across multiple sheets.
There are two ways to work around the situations, each using unique named ranges.
One way of unique names would be to use the underscore and extended names. For example, the unit names could be units_yr1, units_yr2 and units_yr3. Price names would be price_yr1, price_yr2 and price_yr3.
The other way is to tie in the sheet name. The unit names could be Year1!units, Year2!units and Year3!units, for example. You start the named range using the name of the worksheet followed by the ! symbol. For this to work best, sheet names should be short. You also do not want any spaces in the sheet names.
One thing to put out on the second option. Say you enter a name called Year1!units, with Year1 representing the sheet. Excel will not display the Year1! part of the name when it displays the name. Don't worry though, you have not lost it but instead you have created a sheet specific name. Excel does not display the sheet reference in a sheet specific named range.
Either way would work. Here is why the second way can be better for you:
The number of names that display in a sheet will be much shorter.
The names that display will also be shorter. Note that even though we set the names up as Year1!price, Year1!sales and Year1! sales, what displays is just price, sales and units.
The formulas using the names can be shorter. In this example, the formula for sales can be entered as units * price. (shorter than units_yr1 * price_yr1).
You can copy the formulas from one sheet to the next and they can work. For example, if you have set up sheet specific names for units and price in our example, the formula will display as units * price and you can copy it from the Year 1 sheet to the Year 2 sheet, etc.
Yet another advantage is when you copy sheets. The new sheet will automatically carry over sheet specific names that are specific to the new sheet. So if you set up sheet specific names in the Year1 sheet in our example, and then copied the Year1 sheet, renaming it to Year2, the new Year2 sheet will have sheet specific names for units and price.
Suppose then that you want to use the sheet specific names to summarize information. Let's say we want to do that across the 3 years in our example.
Notice the formula highlighted. We use the sheet reference as part of the named ranges to summarize the units.
One drawback on sheet level names. If you want to delete names, you have to do it sheet by sheet. Small price to pay for the benefit.
To summarize, sheet specific named ranges have many benefits that can speed up setting formulas and maintaining your named ranges.