Solved

Understanding the "Scope" setting in Excel's Name Manager

Posted on 2012-03-16
1
365 Views
Last Modified: 2012-04-01
Hello,

When creating new names or editing old ones in the Excel (2010) Name Manager, there is a setting entitled "Scope."  As options for this setting, the drop-down menu displays  the word "Workbook" followed by each of the worksheet (tab) names included in the workbook.

Can someone explain what this setting does and some points to consider when choosing one of the options?  For example, should the sheet name be selected when a particular name will be present only in a single worksheet or does it have more to do with how to get back to that name after it is created?

A number of my workbooks have a large number of worksheet tabs and in some cases, the Name Manager appears to be way overloaded with many of the defined names displaying #REF!.  I'm attempting here to try to understand how this works so that I can streamline the content of my Name Manager.

Thanks
0
Comment
Question by:Steve_Brady
1 Comment
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37731811
Workbook scope means that that name range is visible from all worksheets.

A worksheet specific scope allows you to create the same name range on different worksheets.  For example Print_Area is scoped to each individual worksheet for obvious reasons.

You can refer to named ranges in sheets by directly addressing them, but workbook scoped variables don't require this.

A named range called "test" with workbook scope merely be referenced as (e.g.,
[C5]=test
whereas if it is scoped in sheet1, then it has to be referenced in other sheets as (e.g.,

[C5]=Sheet1!test <-say C5 in Sheet 2 has this formula

You might create a chart that refers to a named range for its dynamic update.  If you wanted to, you could use sheet-specific named ranges for the range the chart uses, where the chart prefixes what sheet to look at.  That's an advantage and here's a tip I helped on that used this approach:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27629723.html
Its a longer thread, but you can pull up the final xlsm post and see how the chart used range names and the sheet tab was selected so it pulled the range from the proper sheet.

Rather than me counting off all the whys and hows, please read this MSFT article on names and their scopes, then ask any questions you may have that I can give you pin pointed responses.
http://office.microsoft.com/en-us/excel-help/define-and-use-names-in-formulas-HA010147120.aspx

Cheers,

Dave
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question