Understanding Defined Names and Worksheet Level Scope

[DashboardNovice]

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?

 

[GraH - Guido]

Hi,
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?

 

[Hui]

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

 

[Peter Bartholomew]

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

 

[DashboardNovice]

Thank you everybody. This makes sense.