This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to refer to range names with worksheet and workbook scope
Range names can have either worksheet or workbook scope - this blog shows how to make sure that you're referring to the right range names!

Posted by Andy Brown on 01 November 2013

In this blog

Range names can have worksheet or workbook scope
Changing the default range name used

Referring to range names with different scope

As so often, this blog was prompted by a question from someone on a recent course. To understand the question, it will help to step back a bit and explain what the problem is!

Range names can have worksheet or workbook scope

Suppose that following an expensive and wide-ranging Wise Owl survey you create the following spreadsheet, showing young people's pet hates:

Someone is now copying this sheet, by holding down the CTRL key while clicking and dragging on the worksheet tab.

The formula in the selected cell is =AVERAGE(Rating), where the range name Rating has been given to cells C5:C7. You now copy this worksheet, by clicking and dragging on the tab with the CTRL key held down, and make changes to get:

The formula is still the same, but it's now referring to the figures on the current worksheet.

What's happened is that you have two copies of the range name called Rating:

Number	Scope	Notes
1	Workbook	The original range, on the Young sheet.
2	Worksheet	The range on the copied Oldies sheet.

You can see this much more clearly in Name Manager:

Click on this icon in the Formula tab of the Excel ribbon to see your range names.

You can now see exactly which blocks of cells each range name refers to:

The range names have different scope.

Changing the default range name used

This brings me back to my original question: how to change the default range name used?

You can refer to a locally scoped range name on a different sheet by prefixing the range name with the sheet name and an exclamation mark:

Here the formula used is =AVERAGE(Oldies!Rating), to make sure that we pick up on the version of Rating on the Oldies sheet, and not the default one with workbook scope.

Conversely, if you want to use a range name with workbook scope, you must prefix it with the name of the workbook:

Here the formula used is =AVERAGE('Generation gap.xlsx'!Rating), because we want to refer to the range name originally created, rather than the local instance created for the Oldies worksheet.

Perhaps the easiest way to avoid this problem happening is to avoid creating duplicate range names in the first place!

Some other pages relevant to the above blog include:

Microsoft Excel training classes

This blog has 1 thread

Add a new post

From:	duggie
When:	16 Jun 18 at 18:53

Andrew,

Thought you might be interested to hear about the following named range bug in Excel!

We know each worksheet name has to be unique. Any attempt to name a worksheet that already has that name wil be met with an error prompt. The same applies to named ranges - sort of!

Named ranges have two scopes, worksheet and workbook.

Suppose I created a named range, say myname, at the Sheet1 level, referring to cell A1. Obviously if I try to create another named range called myname also at the Sheet1 level, the error prompt appears.

However, if you hide all named ranges in VBA, like this:

Dim nm As Name
For Each nm In Thisworkbook.Names
nm.Visible = False
Next nm

Then if you attempt to create a named range called myname (and make it point to cell A2 of Sheet1) it will work! In doing so, it removes the original myname!

From:	Andy B
When:	18 Jun 18 at 09:28

I never knew that! Thanks.

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Referring to range names with different scope

Range names can have worksheet or workbook scope

Changing the default range name used

Head office

London

Manchester