• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

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

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
Steve_Brady
Asked:
Steve_Brady
1 Solution
 
dlmilleCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now