Excel Combo Boxes and Filtering a Data Set

**dec789** · 09-10-2012, 08:28 PM

Not sure this is even possible but....
I would like to have a series of 4 combo boxes "link" to each other (Not sure if link is the correct term here) in a way that mimics filtering a data set in an Excel spreadsheet.

After the user chooses a value in combo-box 1 (column A)
I would like to "filter" the options in combo-box 2 (column B) to only values in (column B) that correspond with values in the (column A) = selection in combo-box 1.

This "Filtering" of data that populates each combo box would flow down through to the third and fourth combo boxes.

Do I need to make lists of all the possible filtering combinations or can the filtering be done dynamically?

The attached form has an example of the 4 combo boxes and data I'm working with.

For my example combo boxes I used data validation cells. Maybe I need to use an Active X or Form comb box - not sure about the differences here.

Thank you!

**benishiryo** · 09-10-2012, 10:45 PM

hi dec789, you can find some good reads here:
http://chandoo.org/wp/2008/11/25/adv...-spreadcheats/

the steps to do the ActiveX or Form Control are similar. it's just that because both of them are not cell-based (floating in the spreadsheet), they have an external cell link you have to based your formula on.

do up a list of your values you wish to see if you need our help

**dec789** · 09-11-2012, 08:58 AM

benishiryo,

Thank you for your response. I looked at the link you attached - it seems like (and I could be wrong here) all the examples of "linking" combo boxes in Excel are based on having multiple lists of values that the combo boxes are directed to point at. If that is the only way to do this then I can live with that. I will need to create lists of data of all the possible filtering combinations my data set might have.

In my original post I attached an Excel workbook that has two worksheets. The first worksheet "Menu" has the 4 combo boxes one for each column of data in my data set. The second worksheet "DATA" is my data set from which I would like the combo boxes populated.

The 4 combo boxes "SECTOR", "UNIT", "DEPARTMENT", "CONTROL CENTER" are currently linked to each of the 4 columns in the data set. What I would like to happen is if the user selects a SECTOR from the first combo box then the only values presented to him in the second combo box, UNIT, would be the UNITS that belong to the SECTOR chosen in the first combo box. In an Excel spread sheet this would be exactly like filtering on a value in the column "SECTOR". Ultimately this filtering would continue so that when the user got to the third combo box, DEPARTMENT, the only values presented to him would be DEPARTMENTS that belong to the UNITS showing in the second combo box and the SECTOR in the first combo box.

Again - not sure if this is possible this "filtering" in combo boxes. If it is not then I can develop code that will create multiple lists of pre-filtered data.

Thank you again.

**dec789** · 09-11-2012, 09:02 AM

Sorry posted Wrong Excel Workbook. Here is the correct one.

**benishiryo** · 09-11-2012, 10:10 PM

yes, you are right. i have done up the 4 lists for you. you can do up the 4 lists easily with Advanced Filter or in 2007 & above, Remove Duplicates. with that list, i then use the OFFSET formula to determine from where i should pick up the selection. hence, the list must be sorted so that all the related Sectors for eg, are together. then use this OFFSET formula in the Named Range & apply it to the validation.

**dec789** · 09-12-2012, 08:18 AM

benishiryo,

Thank you sir for your help! Your solution is straight forward and much more elegant than the path I was heading down. *

Thank you again for time I really appreciate your help with this.

Take care,
Don