Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to use a range Name as an argument by pointing (Office97 SR2)

    Is there a way to use a range Name as an argument by pointing to it. Like, for example, I want to compute variance for the numbers in cells D3:I3, which are defined as Range Name xyz, by pointing to cell C3 where I have entered xyz. I want to get Var(D3:I3) by pointing to Cell C3.
    I have been successful using formulae such as: INDEX(INDIRECT($AT$3),1,AS5), where the range name appears in cell $AT$3 and the column number appears in cell AS5. But I have not had success with =Var(INDEX(INDIRECT($AT$3),1,$AT$1)), where the range name appears in cell $AT$3 and the number of columns appears in cell $AT$1. Is there another way to do this?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: how to use a range Name as an argument by pointing (Office97 SR2)

    Stephen, you are using INDIRECT and a range name correctly, but when you are computing the variance with VAR, you want to give it a range, not a single cell. OFFSET is the function that you want to use instead of INDEX. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use a range Name as an argument by pointing (Office97 SR2)

    Thank you, Sammy. I am not sure how to use offset in the situation where the cell reference is a named range being selected by the user from a Validation List. It seems like =VAR(Index(Indirect($AT$3),1,)) works when you enter it using CNTRL SHIFT ENTER but, in XL97, you are limited to range names (in $AT$3) that are less than 20 characters long. So, at this point I am still stuck. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: use a range Name as an argument by pointing (Office97 SR2)

    Stephen, offset is tough, I try to avoid it, but I think it's just what you need. Too hard to explain in words, see attached worksheet. Sorry about deleting the first post, the attachment had a macro. --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use a range Name as an argument by pointing (Office97 SR2)

    Sammy:
    Please check out WorSheet2 that I inserted into the file you sent to me. Go to the validation cell and choose, in turn, each of the named ranges with multiple B's. You will see, if you do this in XL97, that one works and the other does not work. The number of B's in the one that does not work is greater than 19.
    Let me know if you get the same results.
    Thanks
    Stephen
    Attached Files Attached Files

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: use a range Name as an argument by pointing (Office97 SR2)

    It dosn't work because the long B string in the validation is not the same as the long B name (different number of Bs). Try it with the name Data567890123456789012345: too easy to mistype Bs! However, I am running XL2K. Can try XL97 tomorrow if you still have problems. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: how to use a range Name as an argument by pointing (Office97 SR2)

    Hi,
    I'm a little confused - if you want the variance of your named range, why do you need Offset or index at all? Surely =var(indirect(AT3)) will give you what you want if AT3 contains your range name?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: use a range Name as an argument by pointing (Office97 SR2)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > if you want the variance of your named range
    <img src=/w3timages/blackline.gif width=33% height=2>
    I assumed (erroneously?) that Stephen wanted the variance of a subset of the named range. If he wants the entire range, then Rory is absolutely correct. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to use a range Name as an argument by pointing (Office97 SR2)

    Rory:
    Please see (Post: 111785).
    Yes, you are correct, =var(indirect(AT3)) will give me the variance of the named range that appears in that cell. BUT, THIS ONLY WORKS, ON OUR SYSTEMS HERE (WITH XL97), IF THE NAME OF THE NAMED RANGE HAS LESS THAN 20 CHARACTERS!
    I don't mean to be shouting but somehow this message is not getting thru to everyone who is responding to this post. This is probably my fault because I had another post (Post: 111785) going on that stated the 20-character problem. I started this one because I somehow thought that I was on to a way of getting around this problem with the =Var(INDEX(INDIRECT($AT$3),1,$AT$1)).
    So, Iam back to the point where both posts are about the same subject.
    I hope we can get this back in focus because I really have not been able to solve the problem.
    Thanks

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: how to use a range Name as an argument by pointing (Office97 SR2)

    I confess this is not a problem I've ever heard of. It can't be a limit on the length of string you can use with INDIRECT as I used to use it to refer to other workbooks (which pretty much always resulted in a longer string than that!) I got the impression from Sam's post that it was a case of the string in one cell not exactly matching the range name?
    If that's not the case, can you post a sample workbook?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: how to use a range Name as an argument by pointing (Office97 SR2)

    I have no problem with long names on my XL97 system either. I only tried one that was 25 long, but help says they can be 255. As Rory said, post a workbook that dosn't work. BTW, sorry for the Offset bunny trail.

    Better yet, does the attached worksheet work in 97? I created it in XL2K but saved it as XL97.
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to use a range Name as an argument by pointing (Office97 SR2)

    Rory:
    I sent you the file via e-mail. The zip version was too large.
    Thanks for taking a look.
    Stephen

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: how to use a range Name as an argument by pointing (Office97 SR2)

    Stephen,
    I've just taken a look at your file. The problem is not the length of the names, it's that when your code runs Selection.CreateNames it has to convert the spaces in the names into underscores. Your data validation range however does not take this into account. If you run a Replace on your Expr1003 column, changing all spaces to underscores and refresh your pivot table, the INDIRECT functions work OK because the data validation list now matches the range names that were created. The other two cells you've highlighted that are showing #REF are pointing at A25, which does not contain a valid range name as far as I can see.
    I hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to use a range Name as an argument by pointing (Office97 SR2)

    Rory:
    Thanks. Can't wait to try this tomorrow. I might even get to work on time for a change. SO the 20 char limit, which sometimes was 19 and then 21 would be explained by the fact that the shorter names were less likely to have a space in them. There I was being an Engineer again.
    thanks <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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