Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    3D Range Name (Excel 2000 >)

    Hi all,
    I have tried it out and it does not seem that Excel like it! But just to bury my suspisions, can somebody confirm that a 3D range name is not possible!!!
    PS, If you wonder what I mean by 3D range name, its the same as a normal range name, but the selection spans multiple sheets, ie. One name for selections across many sheets!
    Regards,
    Rudi

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

    Re: 3D Range Name (Excel 2000 >)

    It is possible, I just defined a name that refers to:

    =Sheet1!$B$9:$B$12,Sheet2!$C$11:$C$13,Sheet3!$A$3: $A$7

    Not sure what one can do with it though.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: 3D Range Name (Excel 2000 >)

    You can create a 3D range name as follows:
    - Select Insert | Name | Define...
    - Type a name in the 'Names in workbook' box, say Test.
    - Type a 3D reference in the 'Refers to' box, for example

    =Sheet1:Sheet2!$A$1:$B$4

    If the sheet names contain spaces, you should surround the sheet part in single quotes:

    ='Sheet 1:Sheet 2'!$A$1:$B$4

    You can then use a formula such as =Sum(Test)

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 3D Range Name (Excel 2000 >)

    I see you cannot use it to navigate -- which makes sense! I assume that it can only be used to supply source data to an object or for calculations as you mentioned!
    When i tried the format that Pieterse showed, the name range did not keep the references when i select the name in the dropdown. I re-tried it after the post replies, and it still did not keep the reference, infact it replaces the reference!!! Stange?. Hans, I see your version does not display in the drop down....any reasons for that?
    Regards,
    Rudi

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

    Re: 3D Range Name (Excel 2000 >)

    If by dropdown you mean the Name Box on the left hand side of the formula bar: this dropdown lists only "simple" named ranges. It does not list ranges defined as a formula, 3D ranges in the format I described, and probably others. Such names can be used in formulas and in VBA code.

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

    Re: 3D Range Name (Excel 2000 >)

    If done the way I showed, the name does appear in the name dropdown, but only takes you to the range that is defined first in the name.
    Unfortunately, this also resets the range the name refers to to the first address.
    As long as you don't select the name from the name dropdown box, all stays put.
    Looks like it is best to do this differently!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 3D Range Name (Excel 2000 >)

    I must admit, it was an unusual question, that can out in discussion! Hans's method works well, but the limitation with it is that it can only refer to the same range across the sheets, not do different locations on different sheets!
    I'm not going to fuss over it, as I am not using it! It was only a discussion among friends! It goes to show that I was proved wrong by both of you! I attempted it and thought it did not work, but I was using it wrong! It makes sense though if in context with calculations and so forth!

    THANX
    Regards,
    Rudi

Posting Permissions

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