Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New article: Defined Names In Excel (97, 2000,xp,2003)

    Hi everyone,

    About a year ago I published an article in a Dutch magazine about using defined names in Excel.

    I thought it was high time to translate that article into English and publish it on my site.

    Here's the introduction of the article:

    Defined Names In Excel

    In Excel you can give a range of cells a name. When you do that, you can use this name instead of the address to point to that range. In general it is easier to remember a name than a range address of the start and end of a range of cells.

    Using names has a couple of advantages: ranges are easier to find, formulas are simpler to understand and maintenance of the spreadsheet model becomes more reliable.

    Furthermore, some thingsin Excel can only be achieved bij using defined names. But much more is possible with defined names than just naming a range of cells. Because you can also put a formula into a name, a whole world of possibilities opens up!

    Even more so, since you can also put the old Excel 4 macro functions to use in a defined name. This enables you to do things normally only possible using VBA.

    And here's the TOC:

    Introduction
    How To Define Range Names
    How To Use Range Names
    Absolute And Relative Addressing
    The Context Of Names
    Special Names
    A Step Further: A Formula In A Defined Name
    Dynamic Names
    Another Step Further: XL4 Macro Functions In Names
    Passing Arguments To A Defined Name Formula
    Bugs in Excel's Name Object
    Conclusion


    Go and read it, then tell me what you think of it!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New article: Defined Names In Excel (97, 2000,xp,2003)

    Very useful! Thanks again! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: New article: Defined Names In Excel (97, 2000,xp,2003)

    An excellent article. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    One trick you don't mention, that I have seen come up on occasion, is to use named ranges to compare the contents of 2 sheets:
    [I figure you have seen this, but for those who have not, some details]
    Goto cell A1 of Sheet2
    insert-name -define
    Name:Compare
    Refersto:
    =Sheet2!A1 {Note the lack of "$"}
    <ok>
    Now goto the other sheet that you want to compare the cells in sheet2 to
    Select the range of cells (a1: whatever)
    format- conditional format...
    formula is:
    =A1<>Compare
    [format...]
    patterns(tab) - choose a color
    <ok><ok>

    Now any cell in this sheet that has a different value than the corresponding cell in Sheet2 will be highlighted...

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New article: Defined Names In Excel (97, 2000,xp,2003)

    Very nice, once again! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    The only thing I might be able to contribute (from personal (in)experience) is one of the possible "gotchas" with dynamic ranges, as discussed in DAO Error with dynamic range (2000 sp-3/ 98SE).

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New article: Defined Names In Excel (97, 2000,xp,2003)

    Nice example Steve. I didn't include too many examples of using defined names, I just didn't know where to start or stop <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New article: Defined Names In Excel (97, 2000,xp,2003)

    Yes, I read that thread. Dynamic names are indeed a problem when you need to get at named ranges using "other" applications than Excel.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: New article: Defined Names In Excel (97, 2000,xp,2003)

    I think more examples of "defined formulas" as opposed to "defined ranges" are more important. I think the typical view of these as "named ranges", though in actuality I think that is a misnomer. There is not (at least in my mind) really a distinction between "named ranges" and "named formulas", all are named formulas (some just refer to a range).

    I think this "leap" into thinking of them as "formulas" is the biggest "problem" and barrier with people using them as anything much more than "named ranges", and helping gett past this barrier would be the most benefical to expand their usefulness to the most people...

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New article: Defined Names In Excel (97, 2000,xp,2003)

    That and the fact the Excel's Define Name interface is so poor.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •