A sheet-level name can refer to a single cell or range of cells, just like a standard (workbook-level) name. The difference is that the scope of the sheet-level name is limited to the sheet to which it belongs. This means that you can use the same name on each of several sheets. Formulas on each sheet will reference the names that sheet, ignoring identical names on the other sheets in the workbook.
There are several ways to create sheet-level names. They include:
ActiveWorkbook.Names.Add Name:=WorkingDays, RefersTo:=Selection
ActiveSheet.Names.Add Name:=WorkingDays, RefersTo:=Selection
You can find more about sheet-level names and VBA on this page.
Sheet-level names are very convenient if you have built a template sheet that uses named ranges referring to that sheet. Duplicating the template will create name conflicts if you have created standard names; if you have used sheet-level names, the new sheet will have all of the sheet-level names duplicated but the names on the new sheet will refer to that sheet, not the original template.
In Excel 2007, the Name Manager will display
all names in the workbook. The Name Manager is on the Formulas tab
of the ribbon; the list of names will look something like this --
In the list of names is a column called Scope. Sheet-level names have the name of their respective sheet; workbook-level names show Workbook as the scope.
In earlier versions of Excel, you will only see sheet-level names for the active sheet when you go to Insert > Name > Define. All workbook-level names will also be visible.
The Name box on each worksheet will show workbook-level names and any sheet-level names for that sheet.
To consolidate data to a summary using sheet-level names, use the INDIRECT formula. =INDIRECT(A2) is interpreted as "return the value from the sheet or range whose name is in A2". So, consolidating from January and February sheets to a Summary sheet, you could use a layout like this:
If the sheet-level names contain more than one cell, you must use an array formula or you will get a #VALUE! error. In the example above, January is in A3. Select cells B3:L3 (12 columns wide), then create this formula:
Confirm the formula with Ctrl+Shift+Enter to make it an array, and the values from January will be displayed in the Summary sheet. This can now be filled down to consolidate the data from the February sheet, and any other sheets whose names appear in column A.