557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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!|
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!
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.
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:
|When:||16 Jun 18 at 18:53|
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!
|When:||18 Jun 18 at 09:28|
I never knew that! Thanks.
25 Aytoun Street