In Excel, a name can be global to a workbook or local to a worksheet. The traditional method we all use when we create a range name usually creates global names.
But when we work with dashboards we set up each chart to be supported by its own worksheet. These worksheets are very similar, with many names in common.
Here’s how we set up a workbook with names that apply only to a worksheet, rather than to the entire workbook:
For convenience, I list all the names that will be defined locally. Usually, I enter these in the Control sheet. Then I first define these names globally.
Suppose that the list is in the range A20:A30. You would select the range A20:B30 and in your Ribbon’s Formulas, Defined Names group, choose Create from Selection. In the dialog, make sure that only Left Column is checked. Then choose OK.
Then you would define the names locally in each appropriate worksheet. To illustrate, suppose you want to define a local name in sheet A. You can do so in several ways.
First, you can select the cell you’re naming and in the Formulas, Defined Names group, choose Define Name. For example, rather than assigning the name “ScaleType” you assign:
A!ScaleType
That is, before the name you enter the sheet name followed by an exclamation mark. (If the sheet has spaces or numbers, you also need single quotes. This is why I try to avoid spaces and numbers in my sheet names.)
Second, you can assign the name using Create Names. You take the same approach you normally would. But because we’ve already defined ScaleType globally (on the Control sheet) Excel automatically defines the name in sheet A as a local name.
To assure yourself that the name has, indeed, been defined locally, select sheet A and then choose Insert, Name, Define. When you scroll down to ScaleType you should see “A” at the far right side of list box in the same row as ScaleType. If you select sheet B, you’ll see “B” under similar conditions. But if you select the Control sheet, you’ll NOT see the sheet name, because we assigned that name globally.
And finally, if you’re in, say, sheet B and you want to refer to the name as it’s defined locally for sheet A, you refer to the name like this:
=A!ScaleType
In sheet A, of course, you just refer to it as:
=ScaleType
