COMBO BOX Drop List Function
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: COMBO BOX Drop List Function

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Excellent,
    this was just what I was looking for also.

    The main formula syntax is

    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    In this case the Table_array is not placed in ascending order, and therefore Range_lookup should be FALSE, and the formula should look like

    =VLOOKUP(INDEX(INDIRECT(INDEX(Categories,B4)),E4),INDIRECT(INDEX(Categories,B4)&"List"),2,FALSE)

    This works well for me.

    andy-s

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dear Mr. Excel,
    I got some big problems and really need ur help badly.

    I am now developing a list for training course, I need to display 3 fields (1. Course Categories 2. Course Name 3. Duration). Since there are many courses under each category. I would like to use 2 combo box for "Course Categories" & "Course Name" field to list all the information rather than typing it eveytimes.

    I got 2 questions:
    1. after i selected range & cell link in the combo box, everytime when i select the combo box. It will only display the no. (e.g 1 for selection 1, 2 for selection 2).But what i need is to display the text, how can i do it?

    2. How can I interlink the two combo box. like when i choose the category 1, the other combo box will list those courses under category 1? Do I need to write any marco and how to write?

    I really appreciate ur help and thank u so much in advance!!!

    hope to rec.ur reply soon, many thanks!
    edith

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-13 00:43, edith168 wrote:
    Dear Mr. Excel,
    I got some big problems and really need ur help badly.

    I am now developing a list for training course, I need to display 3 fields (1. Course Categories 2. Course Name 3. Duration). Since there are many courses under each category. I would like to use 2 combo box for "Course Categories" & "Course Name" field to list all the information rather than typing it eveytimes.

    I got 2 questions:
    1. after i selected range & cell link in the combo box, everytime when i select the combo box. It will only display the no. (e.g 1 for selection 1, 2 for selection 2).But what i need is to display the text, how can i do it?

    2. How can I interlink the two combo box. like when i choose the category 1, the other combo box will list those courses under category 1? Do I need to write any marco and how to write?

    I really appreciate ur help and thank u so much in advance!!!

    hope to rec.ur reply soon, many thanks!
    edith
    Edith,

    What follows might meet your needs. It might also require some nerve to implement. It's anycase something that is done using dropdown lists created with data validation. The scheme below has the same logic, but uses the comboboxes created with Toolbars|Forms.

    Insert an empty worksheet in your workbook, if you already don't have one. Name this worksheet Admin. Create the following data in A1:G5 (I trust it's similar to your own data).


    ****** http-equiv="Content-Type" content="text/html; charset=windows-1252">

    ******>















































    Cat

    Cat1

    Duration

    Cat2

    Duration

    Cat3

    Duration

    Cat1

    internet

    6

    word

    2

    vba

    5

    Cat2

    intranet

    10

    excel

    40

    lisp

    40

    Cat3

    html

    10

    access

    40

    apl

    25


    e-commerce

    25










    Select A2:A4 , go to the Name Box on the Formula Bar, type Categories, and hit enter.
    Select B2:B5, name the selection Cat1 following the foregoing procedure.
    Select B2:C5 and name the selection Cat1List.
    Select D2:D4 and name the selection Cat2.
    Select D2:E4 and name the selection Cat2List.
    Select F2:F4 and name the selection Cat3.
    Select F2:G4 and name the selection Cat3List.

    Activate now another worksheet and name it Main.
    Activate the option Toolbars|Forms.
    In B4 create a combobox.
    Activate Format Control.
    Enter as Input range

    Categories

    and as Cell link

    B4.

    Activate the option Insert|Name|Define.
    Enter Current as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =INDIRECT(INDEX(Categories,Main!$B$4))

    Create another combobox in cell E4.
    Activate Format Control.
    Enter Current as Input range.
    Enter E4 as Cell link.

    In H4 enter:

    =VLOOKUP(INDEX(INDIRECT(INDEX(Categories,B4)),E4),INDIRECT(INDEX(Categories,B4)&"List"),2)

    This formula retrieves the course duration that is selected from the second combobox which is a subcategory of the category of course, selected from the first combobox.

    Aladin




    [ This Message was edited by: Aladin Akyurek on 2002-04-13 15:01 ]

    [ This Message was edited by: Aladin Akyurek on 2002-04-13 15:02 ]

    [ This Message was edited by: Aladin Akyurek on 2002-04-13 15:03 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dear Aladin,
    This is exactly what i need to do! Thank you soooo...much for ur big help. You are such a genius!!
    Thank you for sharing your knowledge to others!
    Take Care & Have a nice day!
    =) edith

  5. #5
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    858
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    April 14th
    View a collection of recent Excel articles in the Excel Daily News

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thank you very much for ur previous, it was great. However, there are still another problem and hope u could help me.

    When I follow your steps in creating the combox, it works and the two combox able to link together. However, problem comes out when creating several row of these kind of combo box.

    (refer to ur prevoious data)

    I have created a combobox in next row B5.
    Activate Format Control.
    Enter as Input range "Categories".
    and as cell link B5.

    Activate the option Insert/Name/Define.
    Entered "Current1" as name in workbook box.
    Enter as formula in the Refer to box:

    =INDIRECT(INDEX(Categories,Main!$B$5))

    Create another combobox in cell E5.
    Activate Format Control.
    Enter "Current1" as Input range.
    Enter E5 as Cell Link.

    *****However, an ERROR MESSAGE OF "REFERENCE IS NOT VALID" comes out****

    Do u know what is the reason?

    Please help me to solve this problem, hope to receive ur reply soon.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-24 22:17, edith168 wrote:
    thank you very much for ur previous, it was great. However, there are still another problem and hope u could help me.

    When I follow your steps in creating the combox, it works and the two combox able to link together. However, problem comes out when creating several row of these kind of combo box.

    (refer to ur prevoious data)

    I have created a combobox in next row B5.
    Activate Format Control.
    Enter as Input range "Categories".
    and as cell link B5.

    Activate the option Insert/Name/Define.
    Entered "Current1" as name in workbook box.
    Enter as formula in the Refer to box:

    =INDIRECT(INDEX(Categories,Main!$B$5))

    Create another combobox in cell E5.
    Activate Format Control.
    Enter "Current1" as Input range.
    Enter E5 as Cell Link.

    *****However, an ERROR MESSAGE OF "REFERENCE IS NOT VALID" comes out****

    Do u know what is the reason?

    Please help me to solve this problem, hope to receive ur reply soon.
    In row 5 I created another pair of linked comboboxes, but I couldn't replicate the problem you describe. Re-check format control properties of the comboboxes you created. I can send you a copy of the WB if you want to.

    Aladin

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi! Aladin,
    It will be grate if u will able to send me the example. my email address is edith168@hotmail.com
    thank you so much!
    Edith

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi! Aladin,
    It will be grate if u will able to send me the example. my email address is edith168@hotmail.com
    thank you so much!
    Edith


    On 2002-04-24 23:05, Aladin Akyurek wrote:
    On 2002-04-24 22:17, edith168 wrote:
    thank you very much for ur previous, it was great. However, there are still another problem and hope u could help me.

    When I follow your steps in creating the combox, it works and the two combox able to link together. However, problem comes out when creating several row of these kind of combo box.

    (refer to ur prevoious data)

    I have created a combobox in next row B5.
    Activate Format Control.
    Enter as Input range "Categories".
    and as cell link B5.

    Activate the option Insert/Name/Define.
    Entered "Current1" as name in workbook box.
    Enter as formula in the Refer to box:

    =INDIRECT(INDEX(Categories,Main!$B$5))

    Create another combobox in cell E5.
    Activate Format Control.
    Enter "Current1" as Input range.
    Enter E5 as Cell Link.

    *****However, an ERROR MESSAGE OF "REFERENCE IS NOT VALID" comes out****

    Do u know what is the reason?

    Please help me to solve this problem, hope to receive ur reply soon.
    In row 5 I created another pair of linked comboboxes, but I couldn't replicate the problem you describe. Re-check format control properties of the comboboxes you created. I can send you a copy of the WB if you want to.

    Aladin

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi! Aladin,
    It will be grate if u will able to send me the example. my email address is edith168@hotmail.com
    thank you so much!
    Edith

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com