help with dropdown list problem

[harshulz]

hello,
i am using office 365, and appreciate for indulging in this discussion.
in the above picture i am forming a data table for raw materials. basically i want excel automatically response to the selective dropdown to vendor one.

eg.

in data table 
say from dropdown in C6 I choose material as steel, then in D6 vendor i should get steel dropdown list accordingly.

[#2]

@harshulz 

Hi, I'm new here, not sure how to attach a file, but here's what you can do:

1. Give your table header range a name (Range P7:S7), e.g. "Header"
2. On cell C6, use the data validation dropdown list with the Source using "=Header"
3. On cell D6, use the dropdown list again, buat as a source use: "=INDIRECT("Table1["&C6&"]")"

Note: you can change "Table1" with any name for your table

Hope it helps

[#3]

@ericssan 

Another alternative could be using the filter formula.
=FILTER(Table6[[BOARD]:[STEEL]],C6=Table6[[#Headers],[BOARD]:[STEEL]])
and then in the data validation list select the cell where you are using this formula.
For example if that formula was in P12, in the data validation list use P12#