1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Combo box filter

Discussion in 'Business Applications' started by Preston1, Aug 7, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Preston1

    Preston1 Thread Starter

    Joined:
    Dec 7, 2006
    Messages:
    28
    Hello!

    I hope somebody is able to help me...


    I have an excel spreadsheet that is using combo boxes on the page.

    What i would like to do is have one combo box selection change another combo boxes choices.
    I assume this is possible, I have tried several times and have been unable to get the second combo box contents to change.


    I have attached an example spreadsheet of what i need to do (apologies as I dont have the actual unit here on this pc).

    The scenario is if a user selects Test 5 from the first combo box, the second combo box content (list options) should change to different content such as colours instead of days of the week and so on.

    I have tried to do this with a formula, which i have been unable to do...I have also tried VBA coding, but I'm afraid my VBA isnt that great..

    So if anyone could help I would apreciate it.


    Thanks in advance.
     

    Attached Files:

  2. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Is this fine for you:
     

    Attached Files:

    • Test.xls
      File size:
      19 KB
      Views:
      2,240
  3. Preston1

    Preston1 Thread Starter

    Joined:
    Dec 7, 2006
    Messages:
    28
    AJ

    Yes thanks that is perfect.

    Just a couple of questions...

    What exactly have you had to do to make this work?
    I have looked at the formula but i am un certain still how it works...could you break it done for me how it works, as what i sent was an example and i need to add it to different worksheet


    Also when you change the first combo box, it changes the second ones content, but if the second one has a pre selection in it i.e. Blue, and i choose a different option in combo one the blue entry is still present until i select the combo box again..is there any way of clearing it...or selecting the first choice of the new entries.


    Besides that, its all good thank you!!
    Thank you very much!
     
  4. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    I named ranges on your worksheet, so the could be identified by names! After that next to each value in the range that goes to combobox 1, I just typed the name of the range that should be on the second combobox2, when this value is selected in first combo.


    On the second combo, via Data\Validation I put this formula:
    Code:
    =INDIRECT(VLOOKUP($F$13,$B$7:$C$15,2,FALSE))
    Vlookup part will look for the cell on the right of the range of the first combo, and the Indirect part will return the range that has the name specified in the cell returned by the lookup part!

    As for the second part, it can be done, but I think for this is needed a macro! If it's alright for you it ti be done by a macro then I can do it for you ( it's a very simple one)!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/737849