DataWright Information Services

Consulting and Resources for Excel and Access

Using sheet-level names in Excel workbooks

What are sheet-level names?

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.

How can I create sheet-level names?

There are several ways to create sheet-level names. They include:

Using the Name box

  • Select the cell(s) in question, and then select the Name box (the box just to the left of the Formula bar, where you normally see the address of the active cell)
  • Type in the name of the worksheet, followed by an exclamation mark and the name of the range. For example, to create a range called WorkDays on the January sheet, you would type January!WorkingDays. Note: If the worksheet name has blanks, you will have to surround the sheet name in single quotes. (eg, 'January 2007'!WorkingDays). This is a very good reason to never use apostrophes in sheet names.
  • Press Enter and the name will be created.

Using Insert > Name > Define

  • Select the cell(s) of interest, then Insert > Name > Define
  • In the top bar, enter the name using the syntax shown above (eg, January!WorkingDays or 'January 2007'!WorkingDays)
  • Click OK and the name will be created.

Using VBA

  • Creating a sheet-level name in VBA is the same as creating a workbook-level name. The difference is that you add the name to the Names collection of the active sheet, not the workbook.
  • Creating a workbook-level name:
ActiveWorkbook.Names.Add Name:=WorkingDays, RefersTo:=Selection
  • Creating a worksheet-level name:
ActiveSheet.Names.Add Name:=WorkingDays, RefersTo:=Selection

You can find more about sheet-level names and VBA on this page.

Why use sheet-level names?

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.

Viewing names

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.

The Excel 2007 name manager

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.

Consolidating data using sheet-level names

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:

Consolidating sheet-level names

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:

=INDIRECT("'"&A3&"'!WorkingDays")

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.