BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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:
|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!
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
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!