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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Copying worksheet

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:

Old people's pet hates

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:

Name Manager icon

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 two range names created

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:

Referring to a local range name

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:

Using workbook scoped range name

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!

This blog has 0 threads Add post