Unable to create a dynamic and dependent dropdown!

[Syam4]

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,

[#2 · Accepted solution]

@Syam4 

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

[#3]

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,

[#4]

@Syam4 

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

[#5]

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.