Looking for Alternative Ideas for my Situation... dynamically filtering comboboxes?

OK, so I'm trying to implement the concept of dynamically filtering comboboxes based on any one of them changing. Imagine you have 4 comboboxes on a sheet (currently using ActiveX ComboBoxes so they can be typed into directly). Each of the comboboxes shows values from a different column on a "Materials List" sheet. The user should be able to select a value from any of the comboboxes and the other 3 should be filtered to only show the remaining distinct values from their respective column in the "Materials List" sheet.

So, I have a plan, sort of... and I know how to implement the technical details...I'm looking for alternatives because I don't think this is going to perform good enough to be usable. The idea is each combobox on-change will gather the values in all four comboboxes (whether empty or set), pass those into a sub that will first filter the materials list based on the values, then kick the resulting visible rows into an array, and then the array will be parsed into distinct values in the 4 columns. So, if any of the fields changes, all four are sent into the function to apply the filter and parse the visible rows that remain.

The problem with this is that this sheet is divided into sections and each section has a different number of criteria fields. Also, each section has at least 4 rows and up to 10 rows. So, I will have to have specific events for each control on each row. To top it off, the user can dynamically add and remove rows, so I'll have to have the code to write to the code project the event to call all of these features. That's doable, but definitely tedious and probably inefficient.

At this point, I'm trying to figure out if there is some way to do the part where one combobox results in the others being filtered, where all are based on the same data in a "Materials List" sheet. Any ideas are welcome. I tried proposing a pop-up so I could do all the filtering in a Form, but the business user didn't like that idea, though implementation would be incredibly easier, though still not easy. I've tried data validation drop-downs but you can't type directly into them and you have to assign a range formula to set the values. Since there can be dozens of these drop-downs all pulling from the same sheet, that's not feasible. I've tried a Forms combobox but you can't type directly into it and you also have to add items using a range (afaik)...

Hopefully someone will have a better idea.

Thanks

Rocky

Hi

Can you put up an example file. Having trouble trying to work out what your structure may really look like.

Put in the comboboxes etc.

rylo

ExcelForumDemo 1.xlsx

OK, I put together a quick sample of what I'm looking to do... I have a very simple materials list with ID, Description, Category, and Amount. On the "Filter Sheet" what I would like is for the user to be able to either select a description, which would filter the Category and Amount fields, or select a Category and have it filter the Description field to those materials with the selected category. The idea is that the Description field doesn't HAVE to be unique (even though it is in my example). I want either combobox to be used to filter the other (and other non-combo fields).

Hope that helps illustrate what I'm looking to do.

Thanks

Rocky

Hi

Something like

Please Login or Register  to view this content.

rylo

I had thought of that (using a filter and then copying the range to another location and populating the fill range from there)... the problem is that I may have a few rows of the filter drop-downs and the user can add additional filter rows as needed. I believe (though untested) if I do the method you're referencing, the drop-downs will automatically reset to whatever is in the copytorange... so if the user filters the first row and the drop-downs are set... then they filter the 2nd row, and the 2nd row dropdowns are set... I believe the first row drop-downs will reset as well.

Make sense? Maybe I'm wrong, I need to test that.

Hi

Went back to the original post, and am wondering (as you have multiple discrete blocks) if maybe you should use the autofilter function on the block. So if a user selects a cell in a block, then presses a "button" somewhere consistent, then that blocks has the autofilters applied. That way they could do single filters or multiple ones as required.

Unless you aren't really trying to filter the data, but build some form of dependent dropdowns - in which case I'm not sure your example is complete enough, or you need to give steps that would be taken and exactly what would be the result you want from those steps.

rylo