Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Define names in workbook scope

20 May 2014   #1
Franky

windows 7 32-bit
 
 
Define names in workbook scope

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"!?


My System SpecsSystem Spec
.
20 May 2014   #2
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

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
My System SpecsSystem Spec
21 May 2014   #3
Franky

windows 7 32-bit
 
 

really .
thanks
My System SpecsSystem Spec
.

21 May 2014   #4
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

Quote   Quote: Originally Posted by Kurdman View Post
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
My System SpecsSystem Spec
21 May 2014   #5
Franky

windows 7 32-bit
 
 

No need to those links. The problem is solved! Thanks again.
My System SpecsSystem Spec
22 May 2014   #6
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

Quote   Quote: Originally Posted by Kurdman View Post
No need to those links. The problem is solved! Thanks again.

Good to hear!

Tanya
My System SpecsSystem Spec
Reply

 Define names in workbook scope




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
Does Windows save logs somewhere that shows folder names / file names?
Probably it doesn't and my question is a long shot - but I recently toasted a drive, and it had many folders and files which I have little memory of. I also never used one of those nice apps that makes a list. Any help appreciated. Much obliged. Tt
General Discussion
"save the changes” message after removing the protection from workbook
Some time ago I protected the Excel 2010 file from the path File > Protect workbook > Encrypt with password and gave it a password. Now that I removed that password via below method: Open the workbook and use Save As In the lower right of the file window will be "Tools" Choose "General...
Microsoft Office
DHCP assigning address outside of Scope
Hi Everyone, I have a strange networking situation. Let me start by saying this situation is on a Domain, and that I am using Windows 2011 SBS and Windows 7 clients. My server has a DHCP range of 192.x.x.150 to 192.x.x.199 - and all other I.P. addresses are static. On a workstation located...
Network & Sharing
Firewall Rule: Unable to change scope for TCP and UDP rules
I am attempting to run a (legitimate) ADCH++ hub from my computer. I am trying to restrict access to few certain IP blocks. The problem that I encounter is that the auto-created firewall rules Windows creates are not able to be changed. In Windows Firewall with Advanced Security, when I attempt...
System Security
Microsoft Office Outlook 2007 - Change middle names to last names
Hello everyone, I need a help regarding the contacts being synchronized with Outlook. All my contacts have their first names under 'First Name', but their last names under 'Middle Name'. I want to copy the middle names of everyone to the 'Last Name' box. Can anyone please help me on this. There...
Microsoft Office
Excel 2010 - Workbook to Workbook Reference?
I have two workbooks that I am using and I am trying to add one cell's data to another cell in another Excel workbook. It used to be as simple as beginning to "add" another cell's data(from another workbook you just migrate to the other workbook) into a different workbook. It isn't working like...
Microsoft Office


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd

All times are GMT -5. The time now is 16:03.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App