1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Understanding Defined Names and Worksheet Level Scope

Discussion in 'Ask an Excel Question' started by DashboardNovice, Oct 10, 2018.

  1. DashboardNovice

    DashboardNovice Member

    In the attached file, I have C13 named as Revenue and E13 named as Expense. These have a scope restricted to the 'Revenue and Expense' sheet, the sheet they are on.

    On the Net Income tab, in D6, I am referring to the Revenue and Expense named ranges.

    I am trying to understand how worksheet scope works. On the 'Revenue and Expense' sheet, in the name box, I can see the named ranges Revenue and Expense. This makes sense because they have a scope restricted to this worksheet.

    But my formula in the Net Income tab, still recognizes C13 as Revenue and E13 as Expense, when I select them.

    1. How can the formula in the Net Income tab, still recognized C13 as Revenue and E13 as Expense, when Revenue and Expense have a scope restricted to the 'Revenue and Expense' sheet?

    2. Also, after creating named range, when I go to Edit the range, the scope field is grayed out. Does this mean that after a range and scope have been defined at first, you cannot go back and change the scope?

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    The scope in the name manager refers to the level (sheet/workbook) in which then name is defined. Thus it is not a scope of use. Therefore you can use the names in other sheets.
    If you would type a formula or variable in the name manager directly, the scope would be workbook. In this case you have not named a range in a worksheet.
    So you can't change the scope as such, it would cut and paste those named ranges to another sheet, you'd see the scope changes.
    Does that help?
    DashboardNovice likes this.
  3. Hui

    Hui Excel Ninja Staff Member

    You can define Names as either Workbook or Worksheet scoped

    You then get a choice of which one to use
    Note that the Workbook Test is on Sheet1
    Test (Worksheet) is on the current worksheet sheet2

    If we add another Test on sheet3

    Note that we can refer to the Test (Workbook) and the Test(Worksheet),
    the Worksheet version is only referring to the Test on the Currentworksheet

    We can not even see the Test on Worksheet Sheet2

    Test (Workbook) is available on all Worksheets

    Now if you copy a worksheet which has a named formula on it, the name will become a Worksheet Name in the new workbook
    GraH - Guido likes this.
  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    You cannot edit the scope because it is not a property of the Name. Each Worksheet can have a collection of Names and the worksheet is then the parent object of the Name.

    Jan Karel Pieterse offers a (free) name manager that will allow scope to be changed but the process is 1) to create a new name, 2) find every formula reference to the name and replace it with the new name, 3) offer to delete the old name.

    Locally scoped names are of greatest benefit for internal referencing on the sheet and, particularly so, when you intend to use the sheet as a template to form copies of the sheet.

    As you show, could bring the values forward using the resulting local names
    ='Revenue and Expense 15'!Revenue-'Revenue and Expense 15'!Expense
    ='Revenue and Expense 16'!Revenue-'Revenue and Expense 16'!Expense

    but workbook scoped names might be more readable for that purpose
    = Revenue2015 - Expense2015
    = Revenue2016 - Expense2016
  5. DashboardNovice

    DashboardNovice Member

    Thank you everybody. This makes sense.

Share This Page