Solved

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

Posted on 2012-03-16
1
358 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now