I have a list of about 38,000 products from over 200 different vendors. I am trying to create dependent drop downs where if they select the vendor in one box, the second will be limited only to that vendors products. I know this can be done without VBA using the indirect function in the validation tool - if there is a separate list for each vendor. I need to avoid having to maintain over 200 separate lists.

There are a lot of other things going on in the workbook but I've created an abridged version of this issue and attached it for reference.Testing Dropdowns.xlsm

Is this possible with VBA? Any suggestions or thoughts would be much appreciated.

Thanks in advance.