Drop down list from a table

[AnthK]

Hi,
I have a query in regards to data validation lists.
I have a Table (Table1)

Material	Supplier A	Supplier B	Supplier C	Supplier D
Material 1	PN:14231	PN: M431		PN:AL09
Material 2	PN:14232			PN:AM04
Material 3	PN:14765		A1S1D1	PN:BD09
Material 4		PN: R172		PN:CA13
Material 5		PN: L981	B42312	PN:DF43
Material 6	PN:65426	PN: K009		PN:KL67

I also have a cell with a drop down box to select Material 1 - Material 6 with Data Validation =INDIRECT("Table1[Material]").

With a material selected - I would like my next cell to have a dropdown list with the Supplier A, Supplier B etc but only where there is a valid material number listed in the table.
For example:
Material 1 would result in a list Supplier A, Supplier B, Supplier D,
Material 5 would result in a list Supplier B, Supplier C, Supplier D,

I can get get the list of headers using =INDIRECT("Table1[#Headers]") but this list contains "Material" as well as Suppliers who do not have a valid part number.
Is there any thoughts as to how this can be done in Excel 2016?

Thanks,

Anthony

 

[Legacy 143009]

Hi,

There is a very useful tutorial about dependent drop down lists. I think each column must be a separate table. It is too long to describe at here so I am leaving the link. Have a nice day!

 

[AnthK]

Thanks for the reply Flashbond.
I have looked through those examples as part of my efforts. I think the main issue is that I am only using Excel 2016 and so don't have access to the dynamic array functions. With those I could do what I need to but its sort of useless until everyone at work is upgraded.

I guess the other way would be to use VBA to achieve the functionality but its not a very robust method.

Thanks again,

Anthony