Loading

Jun 222012
 

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010

Most of my Defined Names are scoped to the Workbook. This is the default scope when defining names in Excel. So why and when do we use Defined Names scoped to the worksheet?

One of the key benefits of using the worksheet scope is the ability to clone a worksheet together with all the worksheet Defined Names. This is ideal if you wish to create a number of similar or identically structured worksheets. For example, if you have a sales worksheet for each month of the year and each worksheet is very similar in structure and nature.

Issues with Cloning Worksheets

Cloning a worksheet is very quick and simple in Excel. However, this benefit can also create problems when you clone a worksheet which has workbook Defined Names (i.e. names referring to that worksheet but scoped to the workbook) . What happens is that Excel also clones the workbook Defined Names but converts them to worksheet Defined Names. The problem here is that most users do not realise that the workbook Defined Names have been cloned. As the newly cloned worksheet now has a worksheet Defined Name scoped to itself and the workbook also has a workbook Defined Name, the model will, most likely, not work properly. This is because Excel will prioritise the Defined Name scoped to the worksheet over a Defined Name scoped to the workbook, thus all references made to that Defined Name on the newly cloned worksheet will no longer reference the original Defined Name scoped to the workbook.

Excel 2007 Cloning Workbook Names – Before

Excel 2007 Cloning Workbook Names – After

The Defined names marked by the yellow background are scoped to the workbook (hence the AA_ prefix). The diagram on the right has the names marked by the orange background as these names are now scoped to the worksheet. This is done automatically by Excel when you clone the worksheet. Download the sample workbook here.

Excel 2007 Cloning Workbook Names – Name Manager

The Name Manager shown above displays the names in the Workbook. Note how there are two of each of the AA_ prefixed names, one scoped to the workbook and one scoped to the worksheet SAL_WorkbookNames_After. These duplicate worksheet names should be deleted.

Thus, the choice of scope is important when creating Defined Names. If you are creating worksheets that have a common trait and which you will want to clone, then use worksheet names. Otherwise use workbook names. This way you will avoid this problem.

Cloning Worksheets with Workbook Names

If however, you do need to clone a worksheet that has workbook names that refer to it, you should immediately check the list of Defined Names and delete all occurrences of worksheet names that were cloned from Defined Names scoped to the workbook.

If you have followed my suggested naming convention for Defined Names, then this is extremely easy because all workbook names will be prefixed with AA_ and all worksheet names will be prefixed with BB_. When you review the list of names in the Name Manager (or Defined Name dialogue box – Excel 2003), you will immediately see, grouped together, all the names that start with AA_ but which are scoped to the worksheet. These can easily be selected and deleted. Then there will be no risk of Excel using the wrong Defined Name.

If you have not used a naming convention, then you will have to go through the list manually and look for names that are scoped both to the workbook as well as the worksheet one by one.

Note: the use of a naming convention also makes it easy to use VBA code to remove invalid names when automatically cloning worksheets!

When to Use Worksheet Names

There are two cases when I would recommend using worksheet names.

Case 1: Common Worksheet Structure

Lets say we have a model that has common characteristics on many of the worksheets which will be referenced by formulas on those worksheets. i.e.

Formulas on Sheet1 referencing Defined Names on Sheet1
Formulas on Sheet2 referencing Defined Names on Sheet2
Formulas on Sheet3 referencing Defined Names on Sheet3

Then it makes sense to use worksheet names. Examples of such names would be:

  • Titles (often showing months, quarters or years)
  • Column Labels (usually on the left and may include several levels)
  • Standard Helper Columns or Rows (perhaps a row to calculate the year or month number or a column used for validation purposes
  • Columns or Rows used by Conditional Formatting or Data validation formulas

In all of these cases, these structural names should be referenced only by formulas on the worksheet to which they are scoped. The key benefit to using Defined Names scoped to the worksheet is that the worksheet can be cloned and all the formulas will continue to work without modification as the Defined Names are cloned with the worksheet.

When I create names for a structural purpose as described above, I usually prefix them with a BBB_ rather than the BB_. This is a way of differentiating them from the worksheet names described under Case 2 below. I can then also use these Defined Names easily in VBA code.

Case 2: Common Data Descriptors

Lets say we have a model that contains twelve worksheets representing sales figures by month. Each worksheet contains a column for each of the following:

  • Country
  • Currency
  • Category
  • Qty

If we had just an Annual Sales Summary sheet, I would create a Named Range scoped to the workbook for each for these columns. However, with twelve monthly worksheets, I would have to create Named Ranges for each column for each of the twelve worksheets (by prefixing the Defined Name with part of the worksheet name, each name would then be unique). This would require the creation of 48 names which is not only time consuming in the first instance, but will also makes it much harder to maintain the model. What if we had 10 such columns to name?

The solution is to use Named Ranges scoped to the worksheet. This way we can create just one worksheet and set it up the way we want, then simply clone it to create the other eleven month and name them appropriately. See my post Worksheets: Naming Conventions

These Named Ranges are not part of the structure of all worksheets in the model but are specific to the data contained on these worksheets. Thus I would prefix them with the simple BB_ to show that they are worksheet names but not part of the common worksheet structure. Download the sample workbook here.

Excel 2007 Defined Names – Worksheet Named Ranges Example

Using Worksheet Names

When using a workbook Defined Name in a formula, you simply use the name exactly as it has been defined. If you use a worksheet Defined Name on the same worksheet that it is scoped to, then it also can also be used exactly as defined. However, if you use a Defined Name in a formula that is scoped to a worksheet other than the one that it is scoped to, then you need to prefix the name with the worksheet name. i.e.

Name Scope Formula on Sheet1
AA_TotalRevenue Workbook AA_TotalRevenue
BB_TotalRevenue Sheet1 BB_TotalRevenue
BB_TotalRevenue Sheet2 Sheet1!BB_TotalRevenue

However, again, when cloning worksheets, it can be problematic if you use names on one worksheet that are scoped to another. Because of this, I always avoid using any worksheet name on a worksheet other than the one to which it is scoped.

The main issue with using worksheet names on a worksheet that the name is not scoped to is that it can get confusing and messy and hard to manage and control. That is what workbook names are for.

Downloads Used:
Defined Names Cloning Sample Workbook
Defined Names Scope Sample Workbook

  7 Responses to “Defined Names: Choosing the Scope”

  1. Thanks for the information. Hope you don’t mind a question. At my job I deal with many workbooks. There is one Folder per month with a workbook for each day of the month, each day has 5 sheets. Many of the cells refer from 1 pg to another. Then we have an annual workbook we call the “link sheet”, with 12 sheets (1 for each month). Each monthly sheet has about 1,200 formulas that pulls data from certain cells in the daily sheets. I would lie to learn a better way to set up formulas that won’t be so time consuming when cloning these sheets each month. Currently, I clone a monthly Master WB containing daily WBs. These are named VEC_Aug (1) and so on thru (31). setting up for Sep I highlight all the WBs and rename to VEC_Sep (1). Then I have to open each Book to change names on the pages to match the month & date. The real problem is having to manually change formulas in individual cells referring to cells in the same WB. Surely there is a better way to do this. The Link sheet isn’t that big of a deal, I use ‘search/replace’ to point all formulas to the correct workbook/sheet. Your thoughts?

  2. I have a workbook where I’m using data validation to provide lists on sheet 1, the lists are on sheet 3 in named ranges; sheet 2 is the calculator which provides the outputs to sheet 1 (only sheet 1 is visible to the user).
    I want to provide a macro so the user can create a copy of sheet 1, which therefore needs to make a copy of sheet 2 (calculations) at the same time. The two sheets must be grouped and copied so that every ‘pair’ of sheets refer correctly to each other.
    However, when I do this, Excel asks whether I want to use the existing names or rename them, even though there are no names on sheet 1 or 2, they exist only on sheet 3 which is not getting copied, but are referenced by sheet 1.
    Sheet 1 or 2 can be copied on their own, but it’s when they are grouped and copied the warning pops up. But I need them grouped (at the time of copying) so the formulas refer correctly.
    Only workaround I can see is to programmatically rewrite all the formulas.

    • I’d have to see an example to get a clearer picture of your issue. You shouldn’t have to clone sheets together or are you using 3D formulae across sheets?

  3. Brilliant article, very clear and useful. Thank you for taking the time to post for the rest of us. I wish I had found it BEFORE I cloned 5 worksheets to 16 worksheets. 🙂 I think if I start over, it will be quicker, easier, and better than if I try to salvage my 16 existing worksheets. But, this is exactly the info that I needed today!

  4. No I’m not using 3D formulas. Sheet 1 is for user input, sheet 2 for calculations; both sheets refer to each other (i.e. the answers presented on sheet 1 are obtained by ref. to sheet 2). I clone them as a group such that I then have a sheet 3 for user input with calculations on sheet 4. If I cloned them separately – sheet 1 to create sheet 3 – sheet 3 would still refer to sheet 2 for its answers, even though its input values were changed. Similarly sheet 4 which is supposed to refer to sheet 3, would still refer to sheet 1. Hope that makes sense! If they’re cloned as a group, Excel correctly translates the refs so sheets 3 and 4 refer to each other. Anyway I resolved it by moving the lists and scoping their names to the worksheet.

    • Hi Alan
      I can’t quite see what you are doing and why you need to have an input and calculations sheet that need to be cloned together. However, it may be easier to change the logic somewhere. How often do you need to do this cloning? Is it per month? How many inputs are needed? I’m wondering how big this sheet is. IT may be easier to do one of the following. Have a fixed input sheet in rows or columns for the data inputs or for the calcs and use dynamic references to connect to the correct sheet based on say the sheet name. If the sheets are big, then the calcs can get heavy so it wouldn’t work. Even if you still need to use a separate input and calc sheet, you could still use dynamic refs between the two sheets. Best is by using a naming convention for the sheets and creating a dynamic reference based on the sheet names

      • Hi Admin
        It does get cloned a lot. Anyway thanks for that I haven’t thought of using a dynamic sheet reference before. There are only a few inputs and outputs that use cross-sheet formulas so that wouldn’t slow it down much.
        Thanks for your help.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)