Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    Los Angeles, California, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicating Range Names (XL 97 SR-2)

    I know I've seen this posted some where on the Lounge! How do I create a range name on an XL sheet that exists on another sheet within the same workbook? <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  2. #2
    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: Duplicating Range Names (XL 97 SR-2)

    Preface the name with the sheet name
    aaa
    refers to
    =Sheet1!$A$1

    and will be listed under name define for all sheets

    Sheet2!aaa
    refers to:
    =Sheet2!$A$1

    will be listed ONLY on the sheet2 insert name define

    Steve

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

    Re: Duplicating Range Names (XL 97 SR-2)

    There are two kinds of names:

    Global names (the default)
    Local names.

    Local names are names local to a particular worksheet. You create one by preceding the name with the sheet name:

    'Sheet Name'!TheName

    The apostrophs are not needed when the sheetname does not contain "special" characters like the space.

    Be warned! When a sheet contains a local name, there is no reliable way of using the Global name on that same sheet. Not even by preceding the name (in the function in a cell) with the workbook's name!

    Also, if you have another sheet (inadvertantly) without the local name, the global name will be used automatically. This might cause serious errors in your workbook.

    I would therefor advise against using both a global and a local version of a name. Best to use a different name for the global name.

    Finally: download the Name Manager from the Excel MVP Page below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating Range Names (XL 97 SR-2)

    Navigator Utilities at www.robbo.com.au has a "Name Navigator" which allows you to easily find and edit your Names, including editing and hiding local and global names. It also lists all references to the named range allowing you to jump directly to the reference.

    regards

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Duplicating Range Names (XL 97 SR-2)

    Hi,

    May I add a little to Jan's very pertinent comments.

    It is possible to have multiple similar or identical sheets and have a the same range defined on every sheet in one definition, with the proviso that you always want to refer to the range on the current sheet.

    This can be done by defining a range named Test as: =INDIRECT("$A$1")

    In whatever sheet you have active, a reference to Test will point to A1 on that sheet.

    HTH

    Peter Moran
    Two head are always better than one!

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

    Re: Duplicating Range Names (XL 97 SR-2)

    Another way of achieving the same is using this refersTo property:

    =!$A$1

    (Note the exclamation mark)
    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
  •