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
Steve_BradyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.