Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Name Across Multi-Worksheets? (Excel 97-SR2)

    Is it possible to have a range name that refers to several worksheets? Sort of thing like:

    MyData = Sheet1:Sheet3!$B$2:$B$20

    i.e. Sheet1, Sheet2 and Sheet3,B2:B20 in each sheet.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range Name Across Multi-Worksheets? (Excel 97-SR2)

    Yes, but you can't do it manually with a preselected range.. Go into Name, Define, and use the Refers To spreadsheet box to select the range. I just defined

    =Sheet1:Sheet4!$C$17:$E$20,Sheet1:Sheet4!$G$21:$H$ 24,Sheet1:Sheet4!$J$23:$L$25

    as a named range.
    -John ... I float in liquid gardens
    UTC -7ąDS

  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: Range Name Across Multi-Worksheets? (Excel 97-SR2)

    You can do this, but excel doesn't seem to really like it (at least in Excel97). You can't index, or use stat functions with the name (min, max, avg, etc), so it seems like it is "possible" with NO (at least none that I have found) use!

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range Name Across Multi-Worksheets? (Excel 97-SR2)

    Excel through XP stinks in terms of 3D capabilities for many functions such as SUMIF, COUNTIF, etc, which I personally could use. But I answered the question as put, and many functions, such as SUM & COUNT, can be used on 3D ranges.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Name Across Multi-Worksheets? (Excel 97-SR2)

    Hi There

    Well.. I copied and pasted:

    =Sheet1:Sheet4!$C$17:$E$20,Sheet1:Sheet4!$G$21:$H$ 24,Sheet1:Sheet4!$J$23:$L$25

    And any attempt to do anything with the the newly defined range generated a #VALUE! error. So, I guess the answer is you can define the name but not do much useful with it. It was a rhetorical question in any case, related to another post, so lets not break sweat trying to solve this one. Thanks however for the enlightenment, as always.

    Regards
    Peter

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range Name Across Multi-Worksheets? (Excel 97-SR2)

    The way I have been doing it is to use the Refers To box and then the worksheet selection icon, the one similar to the Print Page setup icon Sheets tab, and then first select all the sheets I want in the reference and then the cell range(s) I want in the reference, then return to the dialog and OK out. See attachment.

    And then as you already noted there's only some simple things you can do with the 3D Range. I'll be happy to join with you in whining about Excel's 3D limitations. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Name Across Multi-Worksheets? (Excel 97-SR2)

    Whining is no fun at all compared to the kick I get from finding a smart solution to a seemingly intractable challenge. Though my wife still thinks me a little odd whenever I get orgasmic over my latest masterpiece which outwardly at least seems to add up a few columns and divide them by the day of the week! ;-)

    Regards
    Peter

  8. #8
    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: Range Name Across Multi-Worksheets? (Excel 97-SR2)

    In addition to other limitations, a 3D range MUST BE the SAME range on the sheets. It can NOT be different addresses across diferent sheets.

    Steve

Posting Permissions

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