named range scope...

i get confused...

a named range can have:
    a global level (to the entire workbook),
    or
    a worksheet level (to the active worksheet),
    or
    a ...

also, i've really looked, but can't find good information on how to define 1
level name vs. the other...  (any direction on this ?)


0
MK5968 (6)
12/2/2003 2:13:42 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
273 Views

Similar Articles

[PageSpeed] 11

Mark,

Just the two.

A workbook name is created by just inserting a name in the Names box, such
as myRange.

A worksheet name is created by adding the sheet name to the name in the
Names box, such as Sheet1!myRange. You can then also create Sheet2!myRange.
You can only add a worksheet name if that sheet is active, else you get an
error.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
news:%23OC8q4NuDHA.2456@TK2MSFTNGP12.phx.gbl...
> i get confused...
>
> a named range can have:
>     a global level (to the entire workbook),
>     or
>     a worksheet level (to the active worksheet),
>     or
>     a ...
>
> also, i've really looked, but can't find good information on how to define
1
> level name vs. the other...  (any direction on this ?)
>
>


0
bob.phillips1 (6510)
12/2/2003 2:21:22 PM
thanks...

and could you tell how to do the same in VBA?


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23AapQ%23NuDHA.2340@TK2MSFTNGP12.phx.gbl...
> Mark,
>
> Just the two.
>
> A workbook name is created by just inserting a name in the Names box, such
> as myRange.
>
> A worksheet name is created by adding the sheet name to the name in the
> Names box, such as Sheet1!myRange. You can then also create
Sheet2!myRange.
> You can only add a worksheet name if that sheet is active, else you get an
> error.
>
> -- 
>
> HTH
>
> Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
> news:%23OC8q4NuDHA.2456@TK2MSFTNGP12.phx.gbl...
> > i get confused...
> >
> > a named range can have:
> >     a global level (to the entire workbook),
> >     or
> >     a worksheet level (to the active worksheet),
> >     or
> >     a ...
> >
> > also, i've really looked, but can't find good information on how to
define
> 1
> > level name vs. the other...  (any direction on this ?)
> >
> >
>
>


0
MK5968 (6)
12/2/2003 2:35:19 PM
Pleasure.

    With ActiveWorkbook.Names
        .Add Name:="myRange", RefersTo:="=Sheet1!$A$1"
        .Add Name:="Sheet1!myRange2", RefersTo:="=Sheet1!$A$1"
        .Add Name:="Sheet2!myRange2", RefersTo:="=Sheet2!$A$1"
    End With

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
news:uiz0wEOuDHA.2360@TK2MSFTNGP09.phx.gbl...
> thanks...
>
> and could you tell how to do the same in VBA?
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23AapQ%23NuDHA.2340@TK2MSFTNGP12.phx.gbl...
> > Mark,
> >
> > Just the two.
> >
> > A workbook name is created by just inserting a name in the Names box,
such
> > as myRange.
> >
> > A worksheet name is created by adding the sheet name to the name in the
> > Names box, such as Sheet1!myRange. You can then also create
> Sheet2!myRange.
> > You can only add a worksheet name if that sheet is active, else you get
an
> > error.
> >
> > -- 
> >
> > HTH
> >
> > Bob Phillips
> >     ... looking out across Poole Harbour to the Purbecks
> > (remove nothere from the email address if mailing direct)
> >
> > "Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
> > news:%23OC8q4NuDHA.2456@TK2MSFTNGP12.phx.gbl...
> > > i get confused...
> > >
> > > a named range can have:
> > >     a global level (to the entire workbook),
> > >     or
> > >     a worksheet level (to the active worksheet),
> > >     or
> > >     a ...
> > >
> > > also, i've really looked, but can't find good information on how to
> define
> > 1
> > > level name vs. the other...  (any direction on this ?)
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
12/2/2003 3:13:58 PM
did you mean that:
      .Add Name:="myRange", RefersTo:="=$A$1" (on the active sheet)?
whereas
    the other 2 exmpl refer to cell $A$1 on Sheet1 or Sheet 2 respectively,
despite which sheet is active in the workbook?

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%236XetbOuDHA.2260@TK2MSFTNGP09.phx.gbl...
> Pleasure.
>
>     With ActiveWorkbook.Names
>         .Add Name:="myRange", RefersTo:="=Sheet1!$A$1"
>         .Add Name:="Sheet1!myRange2", RefersTo:="=Sheet1!$A$1"
>         .Add Name:="Sheet2!myRange2", RefersTo:="=Sheet2!$A$1"
>     End With
>
> -- 
>
> HTH
>
> Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
> news:uiz0wEOuDHA.2360@TK2MSFTNGP09.phx.gbl...
> > thanks...
> >
> > and could you tell how to do the same in VBA?
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:%23AapQ%23NuDHA.2340@TK2MSFTNGP12.phx.gbl...
> > > Mark,
> > >
> > > Just the two.
> > >
> > > A workbook name is created by just inserting a name in the Names box,
> such
> > > as myRange.
> > >
> > > A worksheet name is created by adding the sheet name to the name in
the
> > > Names box, such as Sheet1!myRange. You can then also create
> > Sheet2!myRange.
> > > You can only add a worksheet name if that sheet is active, else you
get
> an
> > > error.
> > >
> > > -- 
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >     ... looking out across Poole Harbour to the Purbecks
> > > (remove nothere from the email address if mailing direct)
> > >
> > > "Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
> > > news:%23OC8q4NuDHA.2456@TK2MSFTNGP12.phx.gbl...
> > > > i get confused...
> > > >
> > > > a named range can have:
> > > >     a global level (to the entire workbook),
> > > >     or
> > > >     a worksheet level (to the active worksheet),
> > > >     or
> > > >     a ...
> > > >
> > > > also, i've really looked, but can't find good information on how to
> > define
> > > 1
> > > > level name vs. the other...  (any direction on this ?)
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
MK5968 (6)
12/2/2003 3:37:49 PM
Mark,

The first creates a workbook name, that is workbook global, the second
creates worksheet local name. It does not matter which sheet is active as I
use the sheet name qualifier in the name as well as in the range.

What I previously said (You can only add a worksheet name if that sheet is
active, else you get an error.) wasn't absolutely correct, as it can be done
if you include the correct refersto sheet as well as the name sheet.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
news:OV5OrnOuDHA.2444@TK2MSFTNGP12.phx.gbl...
> did you mean that:
>       .Add Name:="myRange", RefersTo:="=$A$1" (on the active sheet)?
> whereas
>     the other 2 exmpl refer to cell $A$1 on Sheet1 or Sheet 2
respectively,
> despite which sheet is active in the workbook?
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%236XetbOuDHA.2260@TK2MSFTNGP09.phx.gbl...
> > Pleasure.
> >
> >     With ActiveWorkbook.Names
> >         .Add Name:="myRange", RefersTo:="=Sheet1!$A$1"
> >         .Add Name:="Sheet1!myRange2", RefersTo:="=Sheet1!$A$1"
> >         .Add Name:="Sheet2!myRange2", RefersTo:="=Sheet2!$A$1"
> >     End With
> >
> > -- 
> >
> > HTH
> >
> > Bob Phillips
> >     ... looking out across Poole Harbour to the Purbecks
> > (remove nothere from the email address if mailing direct)
> >
> > "Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
> > news:uiz0wEOuDHA.2360@TK2MSFTNGP09.phx.gbl...
> > > thanks...
> > >
> > > and could you tell how to do the same in VBA?
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:%23AapQ%23NuDHA.2340@TK2MSFTNGP12.phx.gbl...
> > > > Mark,
> > > >
> > > > Just the two.
> > > >
> > > > A workbook name is created by just inserting a name in the Names
box,
> > such
> > > > as myRange.
> > > >
> > > > A worksheet name is created by adding the sheet name to the name in
> the
> > > > Names box, such as Sheet1!myRange. You can then also create
> > > Sheet2!myRange.
> > > > You can only add a worksheet name if that sheet is active, else you
> get
> > an
> > > > error.
> > > >
> > > > -- 
> > > >
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >     ... looking out across Poole Harbour to the Purbecks
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > > "Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
> > > > news:%23OC8q4NuDHA.2456@TK2MSFTNGP12.phx.gbl...
> > > > > i get confused...
> > > > >
> > > > > a named range can have:
> > > > >     a global level (to the entire workbook),
> > > > >     or
> > > > >     a worksheet level (to the active worksheet),
> > > > >     or
> > > > >     a ...
> > > > >
> > > > > also, i've really looked, but can't find good information on how
to
> > > define
> > > > 1
> > > > > level name vs. the other...  (any direction on this ?)
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
12/2/2003 3:47:08 PM
Just another way to add a worksheet level name:

Option Explicit
Sub testme()
    With ActiveSheet
        .Range("a1").Name = "'" & .Name & "'!HiThere"
    End With
End Sub

And if you're going to work with names, do yourself a big favor and get a copy
of this:

Jan Karel Pieterse's (with Charles Williams and Matthew Henson) utility
"Name Manager.xla" from http://www.bmsltd.co.uk/mvp/

Mark Kubicki wrote:
> 
> i get confused...
> 
> a named range can have:
>     a global level (to the entire workbook),
>     or
>     a worksheet level (to the active worksheet),
>     or
>     a ...
> 
> also, i've really looked, but can't find good information on how to define 1
> level name vs. the other...  (any direction on this ?)

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/3/2003 1:29:49 AM
Reply:

Similar Artilces:

Auto Name in "from" field
We have two mailboxes, i want to be able to send a message from the secondary mailbox but have the sent item message in the secondary instead of the primary. I can do this if i have a rule set to pick up the name from the "from" field. The only problem is that i have to put in the name in the from field all the time. Is there any way for the from field to autodisplay the name instead of me having to type it in all the time? Thanks in advance Darren In the new mail window, there is a box named "Accounts" if you have more then one mail account. You just click on th...

How do I hide the names of Addressees in a sent note? ?
I wish to hide the names of the addressees from others when a send a note out to a broad audience Put them in the bcc field. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Mark Clemens" <Mark Clemens@discussions.microsoft.com> wrote in message news:DF1B74E4-C3B2-4DB1-94A1-1829E00F13A5@microsoft.com... >I wish to hide the names of the addressees from others when a send a note >out > to a broad audience > And if you don't see the bcc field in your email header area, you'll nee...

Quick way to change display name from first last to last, first
Is there a quick way to change the display name from first last to last, first other than manually or manually editing a .csv export file? Many thanks, Brian That is the only (Microsoft) way in Exchange 5.5 You can modify settings in the admin to do this for new users. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Brian Bri...

help with dynamic range
I wanting to know if this is possible without switching the way I have data entered into a spreadsheet. The spreadsheet I have has column headings of each month, Jan thru Dec, with a total column at the end. Down Column A are row names for several departments. So basically there is a chart that is 12 columns wide (one for each month), and about 30 rows longs (one for each department). Data is entered into this as each month is completed for overtime hours, so as of now, anything past Column August is blank (not including the Total column). I really want to make a chart that would ...

how to protect different ranges of cells
How do I protect different ranges of cells in excel when they are not adjacent to each other? Please give me a step by step description as this will be my first time successfully comprehending this proceedure, Select your first range. hit and hold the control key and select your second range. Format|cells|protection tab check the Locked checkbox. Remember that locking cells won't do much until you protect the worksheet (tools|protection|protect sheet). And depending on how many cells you want locked and how many you want unlocked, it might be easier to set all the cells one way (lock...

SUMIF with only one criteria cell within range
I need to use SUMIF to calculate a subtotal of column B based on the criteria of column A. The problem is, the column A has names of group in every 10th cell or so, and column B has many repeated numbers which could vary month to month. Example) Col A Col B group A a b c group B a b c d I need subtotals for group A & B, even when group A has "d" listed in Col. B in other months. Please help. Thanks. Stan Hi it would be much easier if you could fill...

Summing the number of incomplete jobs within a date range -sumprod
Hello, I need help figuring out a formula that counts the number of incomplete jobs for a specific worker that are within the next seven days. I would only want to count the "Date Completed" column if it was blank and within seven days from today's date. I've tried using sumproduct, but I can't seem to figure out how to get the date calculations to work properly. My data looks similar to this Worker Due Date Date Completed Bob 1/18/2010 1/13/2010 Bob 1/21/2010 Bob 2/20/2010 Tom ...

change display name
Hi all, Exchange 2003 /windows 2003 envir. Currently, we have users show as First name last name in our GAL. If we want to show users as Last name, First name, where should I change for all lists in the GAL? Thank you. On Fri, 25 Jun 2010 14:48:35 -0700, ed <ed@discussions.microsoft.com> wrote: >Hi all, > >Exchange 2003 /windows 2003 envir. > >Currently, we have users show as First name last name in our GAL. If we >want to show users as Last name, First name, where should I change for all >lists in the GAL? To change the existing AD ob...

rename category names
I have the data in the below format vendor amount a 100 b 200 c 300 d 400 Now in the chart i want the category names as a,b,other(c,d should appear as other). Total amount of c and d should show up for other. Can you please suggest how this is possible. Hi, The chart will not summarise or group your data. You do that on the worksheet using formula then plot the new summarised data. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "rename category names" <rename category names@discu...

Naming a subform from a main form
I'm having a heck of a time getting to a control on a sub form from a main form. I use the following convention Forms![frm MAIN_FORM]![frm SUB_FORM]![CONTROL_NAME] However when I run the code, I get an error message informing me Access cannot find the field named [frm SUB_FORM]. Then I tried taking the mainform out of the line leaving me with Forms![frm SUB-FORM]![CONTROL_NAME] Then the message reads can find find the form named frm subform. As far as I can tell the subform is opened. I can see it. I'm baffled. Can you help me? Common problem!! Open your main form in d...

Name Box #2
Hi Everybody How do you create Hidden Names for ranges? I would like to name a range of cells and not have the name appear in the drop down Name box, but be able to refer to it in VBA code. I am using Excel 2002 Many thanks Hi PraxisPete, Try something like: '=============>> Public Sub Tester03() With ActiveWorkbook .Names.Add "Test", _ RefersTo:=.Sheets("Sheet1").Range("A1"), _ Visible:=False End With End Sub '<<============= --- Regards, Norman "PraxisPete" <PraxisPete@discussions.micr...

Sign in name
Is it possible to change my net passport sign in name using MS Money 2000? I would recommend you disassociate the existing one from Money, restart MOney and then reassociate the new one. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com especially if it's a UK specific wish. I do not respond to any unsolicited email regarding Money "Duane" <anonymous...

Name ranges and columns
I'm using MS Office Excel 2003. I'd like to be able to refer to a column by a name so that I could use 'Price'23 instead of C23 and so forth. I thought that I'd be able to do that by highlighting the column and then using Insert->Name->Define or similar (I can't figure out the difference between the Name->Create, Name->Define and Name->Label variations. In any case, none of them seem to allow the kind of construction I'd like. Do I have the syntax wrong in Price23 (for example ... how to separate column and row parts) or is the whole thing impo...

sheet names and representing a date
Hi I have a workbook that each sheet tab needs to be named the after a particular date in its worksheet. This is so that the contents can be imported into a database as they are updated. The dates have to be presented "yyyymmdd" with no dividers. Obviously it is a representation of a date and not the real thing. I am always getting errors because currently the sheets are being named manually with many mistakes. The sheet name comes from an actual date in cell E5 I have some vba to do it, but it is clumsy, using year month and day functions combined with conditional statements to al...

Excel 2003 User name
We use ghost to image all our machines and ive noticed recently, when users use excel their user name comes up as venuswilliams (the guy that originally built our image had a thing for her so he used her as a test user). Its fair simple to change the username in excel if you go to Tools - Options, then click on the General tab and down the bottom is the user name field. But what i want to do is change it so it always takes the logged on user. Ive searched for venuswilliams in the registry and deleted everything relating to her but as soon as i log in as a new user on that machine in excel i...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

User's name is missing from GAL
Hey all -- Weird problem. We have a large (500+) mailbox Exchange 2003 server, and for some reason, one, and only one that I know of's name is missing from the Global Address Book. She's not "Hidden From GAL", and I don't see anything wrong. Any idea why this person is missing? Can I force a GAL re-build or something to see if she gets added? I really don't know why she's missing. You may check to see that her account has the showInAddressBooks attribute is populated with the DN of the GAL. You can do this via ADSI Edit pretty easily. Colby -- P...

Calculation based with Range of Date
Hi I m facing problem with my worksheets which i have to calculate total sales in particular month. sheet 1 have Date Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST DD/MM/YY 1.10.2005 10000 2.10.2005 250000 3.10.2005 150000 4.10.2005 1000 1.11.2005 2.11.2005 3.12.2005 4.12.2005 1.1.2005 etc Sheet 2 have Quarterly Return Month Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST January February March In Sheet 2 i want to ...

Sorting report by date range
I hae a query set up to sort all my data into nice little reports. The only problem is a month from now I don't want to have to print everything just to get yesterdays report. I would like a query that says only show the records for date X through Y, and I would like to use a form to enter the dates, run the query then show the report for printing. Thanks David Add 2 combo boxes to an unbound form (beginning of date range, and end of date range). Enter the following parameter in the date field of your query. Between [forms]![yourformname]![comboX] and [forms]![yourformname]![comboY...

Outgoing mail server name
How do we set our outgoing mail server name for an exchange 5.5 environment. Our external DNS has us listed as mail.companyname.com but when mail goes out, the header shows it as servername.companyname.com and we are getting mail blocked by companies that do a reverse DNS lookup. David. Just have your ISP (whomever owns the netblock) set up the appropriate record for your mail server's IP address, if they'll do it....although note that most servers that check, check only for the existence of a PTR, don't compare it to the sending server's FQDN. David wrote: > How do...

Changing Shape Names Is Confusing
I put a shape on a stencil and named it "IconA." When I first dropped it on a page, it retained that name, so I renamed it "Icon 1" using VBA [Shapes("IconA").Name = Ex. 1"]. I later modified the stencil, renamed it "IconA" (using Format > Special) and ran the same VBA program, but the name of the dropped icon became "IconA.6". How can I rename "Icon" so that it remains "Icon"? And why does it exhibit this behavior? Why can't I reuse the name as default? Mike I'd start over here... http://blogs.msdn.c...

Fuzzy Search Against List Of Company Names?
I've got a client that does bond and equity trading on behalf of various "funds". Some of these funds are owned by groups that do not care to invest certain companies. Each of those groups supplies an explicit list of companies that they do not want to invest in. The traders don't have to make judgement calls. All they have to do is check to see if a company is on the list before buying into it. But if there are a lot of lists and/or some lists are very long - and not always alphabetically sequenced - it becomes a problem. In addition, the name of the company that the t...

Can the distribution list suppress individual names?
How can I get the distribution list suppress individual names in the TO: header? Other mailers allow the distribution group to show just "Mylist" (the name of the list) in each recipient's TO: header... Thanks. Personal Distribution Lists are always expanded by the Outlook client. Try putting the personal distribution lists in the BCC field and use something silly in the To field like "Friends <your_email_address_goes_here>" "Keyman" <Keyman@discussions.microsoft.com> wrote in message news:759D3CC2-D65A-4359-8F14-16E182823035@microsoft.co...

Outlool 2000 -TO: names appear when typed
Does anyone know if you can activate the automatic name feature in the To, CC box. That is, in Outlook 2000, if you begin to type a name in the To box can you get it to appear automatically. Right now I have to click on the "to" icon and then type in a name There's no autocomplete in OL2000 - you'd need to upgrade to OL2002/2003 for this. OL2000 has autoresolve - if you type "George" and then check names/press F9, it will try to look up George in your contacts/address books. Note - posting your valid e-mail address in a newsgroup post, or in your news accoun...

Name conflicts
When copying a sheet within a workbook, I often get this message - a formula or sheet you want to move or copy contains the name 'mark' which already exists on the destination worksheet. Do you want to use this version of the name? This happens only once in a while, and I don't know why. Is there any way to get rid of the name conflicts or whatever is causing them? ...