30$ US : Modify a dependent dropdown list based on multiple criteria

  • I am working on a product configurator sheet.
    All the items need for the final product are selected in column E. To select items I have created a filtered drop down menu base on tag in column D. My sheet as near 100 dropdowns menus and 83 filters tags. Some items are incompatible with other items. I need help to take care of incompatibilities in the items drop down menu, I don’t want to see incompatible items.
    On sheet Optionlist, I create 6 more column to add incompatible items based on Item_Nb. If Item 3 is incompatible with item 5, in the item 5 row,column Incomp_1, I will input 3.
    Thanks
    PA


    10% payment done [FONT="Calibri"]31L548694T657664L[/FONT]

  • Let's say I chose Sequoia in the drop down menu "Modèle de Maison". In the OptionList sheet "Sequoia" is Item_nb 4.
    after in the "Toiture" menu, I want to be able to select all items under "toiture" exept "toiture jaune" because in the column Incomp_1 it's marked 4 which is incompatible with "toiture jaune".
    Maybe one of the choice in "brique" will be incompatible with one choice in "Toiture" or "Pierre" and so on for each dropdown menu in the ProdConfig sheet.
    Does that make sense?
    I updated the xls file which is included.
    Thanks![ATTACH=JSON]{"data-align":"none","data-size":"full","title":"photo3.jpg","data-attachmentid":1215193}[/ATTACH]

  • What I asked was a bit overkill, I will lower my demand to :
    On sheet ProdConfig:
    Cell E8 as an influence on E16,E33,E34,E35,E37,E55,E257,E258,E259
    E66 as an influence on E79


    If I had some competance in VBA I will try something like this:
    Create a WorkSheet change event on cell E8 and E66 which triggers autofilter:
    Range: tblOptionList
    Field = Column G to L
    criterial: <> Item_nb in ProdConfig.column F (New column)