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.

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.