Generating List based on matching Criteria

**jj72uk** · 06-13-2010, 11:07 AM

Hi All,

I've done a little searching and tried a few things out but have come to dead ends so am wondering if any of you have some ideas.

I'm using comboboxes from the control toolbar and all is going fine. I have been able to set my pre defiend lists and feed from there.

So far I've created 1 dynamic list that uses IF statements based on the selection from 1 of the combo boxes.

I'm trying to make two more combo boxes - Brand.. - Product

But to generate a list for these is proving difficult as the brand and product can change from month to month ( have months as a combo box already).

Is a way to create this generated list by use of formulas.. or at the last resort a macro..

Attached is a workbook for help.

**pb71** · 06-13-2010, 12:24 PM

Does the attached example work for you. I have added a combo box called Prod that populates with unique values from column A when the workbook opens. It also updates off a worksheet change event if you add to column A.

**contaminated** · 06-13-2010, 01:13 PM

HI jj72UK
In P2 you can use this formula
=INDEX($A$2:$A$1380,SMALL(IF(($H$3=$E$2:$E$1380)*($G$1=$C$2:$C$1380)*($I$3=$D$2:$D$1380),ROW(INDIRECT("1:"&ROWS($E$2:$E$1380)))),ROW(A1)))

And in Q2 this one
=INDEX($B$2:$B$1380,SMALL(IF(($H$3=$E$2:$E$1380)*($G$1=$C$2:$C$1380)*($I$3=$D$2:$D$1380),ROW(INDIRECT("1:"&ROWS($E$2:$E$1380)))),ROW(A1)))

Both formulaes are array formulas, so you must confirm them as an array formulae with Ctrl+Shift+Enter. The copy them down as far as needed.

HTH

**jj72uk** · 06-13-2010, 02:30 PM

Hi Contaminated,

Thanks for your suggestions, though it doesn't seem to work, all i get is a value return of 1?

@pb71,

This is great and seems to work flawlessly. How would i then adapt this for brand as it will have to feed off all areas plus the newly created one? If you could breifly explain how you did it so I can try to understand?

EDIT:

pb71..

While the combo box seems to fill it doesnt seem to produce the correct list... so for example if I choose the manager to be sarah, site teesside, then under product i expect udner the current list only Motor and Home to show..

**pb71** · 06-13-2010, 05:58 PM

Sorry, didn't read the comments in your workbook properly. The first example does not feed off the other selections. I have now updated the attached workbook so that the Prod combo box populates with unique items when you change the value in the Site combo box. The Prod combo box is populated based on the values in cells G3, H3 and I3.

EDIT:

While the combo box seems to fill it doesnt seem to produce the correct list... so for example if I choose the manager to be sarah, site teesside, then under product i expect udner the current list only Motor and Home to show..

Check your list value in column O ("teesside") for Sarah versus your table value ("Teesside") in column E. If you match the text in the corresponding ListFillRange for the Site combo box the Prod combo box fills with Motor and Home. I have reattached the List Example2.xls workbook with this change.

**pb71** · 06-14-2010, 02:12 PM

jj72uk,

I've attached a workbook (based on your example workbook) that updates the successive combo box with unique values when the preceding combobox value is changed.