Solved

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

Posted on 2012-03-16
1
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 42

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

717 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