+ Reply to Thread
Results 1 to 8 of 8

Filtered content in the drop-down list of Combobox

  1. #1
    Registered User
    Join Date
    11-03-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    13

    Filtered content in the drop-down list of Combobox



    Hi,

    I am desperate. I need to address the following challenge and donít grogress any further. I am asking for your kind help.

    Challenge: Say, there are 5 comboboxes; each combobox should only list a selected range of values, based on the previously made combobox selection.

    Example Illustration:

    Lookup Table:
    see attachment screenshot



    User Interface / User Action:
    1st combobox (related to Column1): User chooses AAA from the drop-down list
    2nd combobox (related to Column2): The drop-down list shall only offer 123 and XYZ (one time displayed only!), nothing else
    2nd combobox: User selects XYZ from the drop-down list
    3rd combobox (related to Column3): The drop-down list shall only offer 789 and 777, nothing else
    4th combobox, Ö..
    And so on Ö


    Any way I can do that?

    Kind regards,

    Sascha Graef
    Attached Images Attached Images

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Filtered content in the drop-down list of Combobox

    Hello,

    you can do that with normal data validation, no need for combo boxes. It is called "dependent data validation". You will need to do some prep work to arrange your data and create the range names, but it can be done. See here for a tutorial: http://www.contextures.com/xlDataVal02.html

  3. #3
    Registered User
    Join Date
    11-03-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Filtered content in the drop-down list of Combobox



    Dear Teylyn,

    first of all, thanks a big bunch for bringing me on the right track!!
    I am almost there!
    Now, I have another final challenge that made me crazy the last couple of days, that's why I did not respond earlier. Can you help me on that detail? Please have a look at the attached XLS file:

    CAHELLENGE 1:
    In the first sheet, there is a "Customer" cell, which can be populated with ethe validation list that refers to the corresponding range of the second sheet.
    QUESTION: How do I use the OFFSET function properly to make sure it will be a dynamically growing list, depending on the entries in the light-yellow comun of the second sheet? And how to eliminate the first row header in the validation list?

    CHALLENGE 2:
    In the second sheet, you can see those grey fields that are pre-defined with CUST001, CUST002, ..., CUST100. These should be like static placeholders or tags, to avoid that the user needs to again and again define the column header of the respective dependent lists.
    Now, the user can key in the "Customer Name" in the light-yellow column.

    In the first sheet, if the user chooses customer "BBB", then the next cell "Process" should only offer the range of "CUST0002", because "BBB" is related to "CUST002" (see red marked areas).

    Can you help me with the sample code for this field?
    Thank you very very much in advance!

    Kind regards,
    Sascha


    -----------------------------------------------------


    Quote Originally Posted by teylyn View Post

    Hello,

    you can do that with normal data validation, no need for combo boxes. It is called "dependent data validation". You will need to do some prep work to arrange your data and create the range names, but it can be done. See here for a tutorial: http://www.contextures.com/xlDataVal02.html
    Attached Files Attached Files

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Filtered content in the drop-down list of Combobox

    Hello,

    you can use a dynamic range name for CustomerName. Let it refer to

    Please Login or Register  to view this content.
    Then, use this formula for the range name CustomerID

    =OFFSET(CustomerName,0,-1)

    Manually set the ranges for each customer id column, i.e.

    Cust001 ='CUST-->PROCESS'!$C$2:$C$4
    Cust002 ='CUST-->PROCESS'!$D$2:$D$7
    Cust003 ='CUST-->PROCESS'!$E$2:$E$5

    Then on your data entry sheet use data validation with the list option and this formula:

    =INDIRECT(INDEX(CustomerID,MATCH($A$2,CustomerName,0)))

    see attached.

    Unfortunately, you can't make Cust001, Cust002 etc, dynamic as well, because using Indirect you can only refer to fixed, not dynamic, ranges.

    cheers
    Attached Files Attached Files

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Filtered content in the drop-down list of Combobox

    For an alternative approach see the attachment.

    To start: switch between Blad1 and Blad2 and back to the sheet containing the dropdownbox.
    No second combobox required.
    Attached Files Attached Files
    Last edited by snb; 11-08-2010 at 05:30 AM.



  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Filtered content in the drop-down list of Combobox

    snb,
    where is the dependent dropdown in your "alternative approach"? What does your file have in common with the question and the sample file provided by the OP?

    Why use code that does not work if a simple range name that works can do the trick?

  7. #7
    Registered User
    Join Date
    11-03-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    13

    [SOLVED] Re: Filtered content in the drop-down list of Combobox



    Hello Teylyn,

    great! Thank you 100 times!
    You really helped me getting the entire thing done.
    Never mind the dynamic range. I simply extended the column range to 200 rows down, which is way more than enough that I ever need.

    Again, a big THANK YOU!

    Regards,
    Sascha

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Filtered content in the drop-down list of Combobox

    FYI if you use an additional defined name and EVALUATE instead of INDIRECT, then you can use dynamic named ranges.
    Remember what the dormouse said
    Feed your head

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1