creating VB code using advanced search criteria with multiple data validation lists?

Hi,

I've been reading through many forums and looking at many examples but still cannot find what I'm looking for. The closest I came to was one from Contextures - DataValSelectName.

I'm trying to make a commercial invoice sheet where you can easily select particular brands, then in the second list, select a product from that brand. The problem I have is the list gets updated every week and it contains a thousand products, so I require something similar to how the DataValSelectName.xml works, but with multiple entries. Here is a link to the file in question here .

I've included what I'm currently working on with comments to best help people work through what I've already done. Here is the code itself:

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsSD As Worksheet
Dim wsS As Worksheet
Set wsSD = Sheets("StockList")
Set wsS = Sheets("Commercial Invoice")

If Target.Address = "$A$3" Then
  wsS.Range("B3").Value = "" 'Empties cell B3 when cell A3 is selected
  wsSD.Range("ProductList").AdvancedFilter _ 'Uses the named range "ProductList" for the range of the search
    Action:=xlFilterCopy, _
    CriteriaRange:=wsS.Range("A2:A3"), _ 'uses "Type" as the search column, then searches for whatever is in cell B3
    CopyToRange:=wsSD.Range("AA1"), Unique:=True 'Copies the search results to cell AA1 in the stocklist sheet
End If

End Sub

The first line in the commercial invoice works, but I can't seem to get the other lines working. Thank you in advance for anyone reading this.

hi, Jonathan, please check attachment, I've added validation list for Product Name that depends on the Type selected, VB is used to clear Product Name after changing Type, as to the green-coloured part I'm not sure I understood where the data should come from. If you can provide a bit more clear explanation, I would look at it.

Thank you Watersev, that's precisely what I need. I'll quickly redo the sheet and post the finished result for future reference. I'm trying to understand what you did though, the code is so short

Thank you again!