Results 1 to 7 of 7

Thread: Named ranges

  1. #1

    Named ranges



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Is it possible to use named ranges in a drop down list ?. ie

    named range "_Carbon10" contains
    3 HOOK
    6 HOOK
    10R
    14R
    5-8
    6-10
    8-12
    10-14

    Lists names :- " _Carbon10" ," _Carbon13" , "_Carbon20" etc, each named range includes a list of 5 -10 long
    all different to each name as above

    If cell A1 has a result based on two selections chosen ie cell A1 = Carbon10 (with no underscore) how can I display the named range list "_Carbon10" in B1

    B1 list must be selectable
    Many Thanks Paul

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    if Cell A1= Carbon10
    please use formula in Cell B1 ="_"&A1

  3. #3
    Also if B1= "Named range " _Carbon20" it will display the named range called _Carbon 20 etc ienamed range "_Carbon10" contains4 HOOK8R10R14R4-65-86-108-1210-14 this is a different list in its contents

  4. #4

    Named ranges

    Sorry last post should have said:-

    That only gives the result B1= "_Carbon10" and not the list associated with the named range "_Carbon 10"
    I would like B1= a drop down list associated with the named range "_Carbon10" ie
    named range "_Carbon10" contains see below:-
    3 HOOK
    6 HOOK
    10R
    14R
    5-8
    6-10
    8-12
    10-14

    Also if B1= "Named range " _Carbon20" it will display the named range called _Carbon 20 etc ie named range "_Carbon10" contains
    4 HOOK
    8R
    10R
    14R
    4-6
    5-8
    6-10
    8-12
    10-14 (this is a different list in its contents)
    Thanks

  5. #5
    Use Data Validation with a DV type of list and a formula of

    =IF($A$1="Carbon10",_Carbon10,IF($A$1="Carbon13",_Carbon13,IF($A$1="Carbon20",_Carbon20)))


    Just add extra IFs for further lists

  6. #6
    ANother way, making the formula simpler, is, if the value in A1 will always align to the named range, you could use a DV formula of

    =INDIRECT("_"&$A$1)

  7. #7

    Thanks Bob, Works well

    Quote Originally Posted by Bob Phillips View Post
    ANother way, making the formula simpler, is, if the value in A1 will always align to the named range, you could use a DV formula of

    =INDIRECT("_"&$A$1)
    Thanks

Posting Permissions

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