Results 1 to 7 of 7

Thread: Name Ranges

  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    I'd like to name a range but not have it specifc to a particular sheet. I'm using version 2003. Right now if I go to the Name/Define option, it always asks for a sheet name and it will always go to sheet2 and the range. But if I'm on sheet 3, I'd like it to choose the same range.

    Any help would be appreciated.
    thanks,
    Deb

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='masterd' post='766455' date='20-Mar-2009 15:19']Hi,
    I'd like to name a range but not have it specifc to a particular sheet. I'm using version 2003. Right now if I go to the Name/Define option, it always asks for a sheet name and it will always go to sheet2 and the range. But if I'm on sheet 3, I'd like it to choose the same range.

    Any help would be appreciated.
    thanks,
    Deb[/quote]

    All ranges you name in one worksheet of your workbook will be availabe in all sheets of that same workbook. As a result , you cannot use the same name twice in the same workbook.

    I hope this helps.

    Regards,

    Tom Duthie

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well someone has a workbook file where this works, but I don't have access to this file to look at it to figure it out. Just so I'm clear. I'd like to pick a named range, a1:a12, from the name box drop down and when I select it, I don't want it to take me to the original sheet I was on when I created it. I want it to be specific to just the range, so no matter what sheet I'm on, when I choose that named range, it goes to it on the current sheet.

    thanks,
    deb

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    The trick to this is to used copies of the "original" worksheet. To explain just follow these steps as an example

    1. Open a new workbook
    2. Delete all worksheets except sheet 1
    3. In A1 type 1
    4. In A2 type 2
    5. Select cells A1:A2
    6. Define a name to the range, say, Values
    7. Copy the worksheet by holding Ctrl and dragging the tab across to the right


    You will now have a new sheet called Sheet 1 (2). Go to the name box and select Values from the list, you will note the cells A1:A2 will be highlighted. This will happen on whichever sheet you are on.

    HTH
    Jerry

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

    You can have Ranges defined on each sheet in a workbook so that the range is only known in the specific sheet.

    One of the handy features of Excel is that you can define names that refer to ranges of cells. (This is a big plus when you want to write formulas that make sense.) When you create a named range, Excel assumes that you want the name to be available from every worksheet within a workbook. You can, however, specify that a name be valid only for the current worksheet. In this way you can define the same name on different worksheets in your workbook. Thus, you could have a range named MyRange on Sheet1, and a range named MyRange on Sheet2, and also on Sheet3. To create names that are applicable to a specific worksheet, follow these steps:

    1. Select the range of cells that you want to name.
    2. Choose Name from the Insert menu, then choose Define. Excel displays the Define Name dialog box.
    3. In the Names in Workbook box, enter the name of the current worksheet, an exclamation mark, and the
    name you want to define, as in Sheet1!MyRange.
    4. Click on Add.

    That's it. Now, if you go to a different worksheet, the name you defined will not be available from that worksheet--only from the worksheet in which it was defined.

    Good Luck!

    Peter Moran

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Alternatively, you could define a name like this:

    Name:GlobalName
    RefersTo:=INDIRECT("A1")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts
    [quote name='masterd' post='766455' date='20-Mar-2009 15:19']Hi,
    I'd like to name a range but not have it specifc to a particular sheet. I'm using version 2003. Right now if I go to the Name/Define option, it always asks for a sheet name and it will always go to sheet2 and the range. But if I'm on sheet 3, I'd like it to choose the same range.

    Any help would be appreciated.
    thanks,
    Deb[/quote]
    Deb:
    I just tested this possible solution, and it works with my data. See if you can apply it to your case:
    The attached file contains two worksheets. It also contains a named range, "RangeOne". "RangeOne" has this definition, if you look in the Define Name dialog box:
    =INDIRECT($A$4:$C$9)
    I placed a SUM formula in cell A12 on both of the worksheets, as
    =SUM(RangeOne)
    HTH.
    Attached Files Attached Files

Posting Permissions

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