Unable to create a dynamic and dependent dropdown!

Copper Contributor


Hello all,


I have an excel workbook with two work sheets 'Material Register' and 'Lists'. Sheet 'Lists' has a table named 'MaterialData' which will capture name of supplier and brand names. in other sheet named 'Material Register' I have a table named 'MaterialRegister' where column 'Supplier Name' and 'Brand name' is available. My requirement is when I select a supplier name in the second sheet, column brand name should give me a dyanamic dropdown related to only selected supplier. The subject document shall be accessed from here - Excel Workbook 


Looking forward to support from this expert group.!


Thank you,

4 Replies
best response confirmed by Syam4 (Copper Contributor)


See the attached version. I created a named range SupplierList and used that in the data validation formula.

Brilliant..! Thanks a lot Hans :)

Using the same logic I tried to connect between column 'Brand Name' and 'Item Name/Description' of sheet 'Material Register' which did NOT work.! I used the named range 'ItemList' for the same and below is the formula I used -

=OFFSET(Lists!$AF$2, MATCH($C4, ItemList, 0), 0, COUNTIF(ItemList,$D4), 1)

Could you please advice and help me fix this too.?

Thanks in advance,


Can the same brand name occur more than once in the Brand Name column of MaterialData, for different supplier names?

Yes it can (Supplier 1 and Supplier 2 can sell the same Brand and Product). Our requirement is to list Brand Name relevant to selected Supplier first and then Items relevant to selected Brand Name.