Define names in workbook scope

[Franky]

Hello guys,

I use MS Excel 2010. In this app, I know that using Formulas > Define names > define name it is possible to name a cell (or range). At that dialog box there is a scope rectangle which if I choose the entire sheets scope (workbook) so it's said that this name is valid for the entire workbook. First what does this valid mean please? Then, OK, I think I understand it but why when I name cell A1 (in the worksheet1 for example) to "first" its name on that worksheet is valid, that is its name, here "first", is shown in Name Box, but although I've used workbook scope, when I change the worksheet (to worksheet2) and active cell A1, just A1 is shown there not the put name, "first"!?

 

[TanyaC]

If you name A1 "First" in worksheet1, the name is a valid name that defines the cell reference worksheet1!A1.

The name is not allocated to any other range of cells.

So, A1 in worksheet2 is still A1. Setting the scope of the name to global does define the same name for the same cell(s) in any other worksheets.

However, in worksheet2 you can reference the name "First" and it will use the contents of the named cell(s) from worksheet1.

An Example;

In Worksheet1 A!:A3 I enter the contents..

A1: Apples
A2: Oranges
A3: Lemons.

Then I define the name "Fruit" that covers that range of cells. I also set the scope of the name to be workbook (Which is the default)

Now I go to worksheet2 and I create a data validation list object and I tell it to use the "Fruits" named range. I create this list in Worksheet2 at B2 (any cell would be fine, just using B2 as an example).

When I click on the list drop down in worksheet2!B2 it will show me apples, oranges and lemons.

Had I not used the workbook scope I could not have used the name in any but the named scope sheets. Eg. If I had selected Sheet1 as the scope, then that name can only be referenced from within sheet1

 

[Franky]

really .
thanks

 

[TanyaC]

really .
thanks

Ok, since all I did was to confuse you, maybe you should take a look at these resources...

How to Create a Named Range in Excel

Define named cell references or ranges - Excel

Define and use names in formulas - Excel

 

[Franky]

No need to those links. The problem is solved! Thanks again.

 

[TanyaC]

No need to those links. The problem is solved! Thanks again.

Good to hear!

Tanya