Forum Discussion
Solved
Unable to create dependency drop down List
Hello All,
After a long search I still could not figure out how to create this dependency drop down list in excel.
What should I input in Data validation:List?
May I have your help on it:
My target is to create drop down list in below Main column. It should be dynamic.
Expected result:For apple, it should be able to select Green or Red. Orange can select Orange
I will add more rows on sheet Main and sheet second if there is more combination.
Seems this is simple logic but already spent hours on it :(
In sheet: Main
| Composition column | Drop down list in this column |
| Apple | |
| Apple | |
| Orange |
In sheet: Second
| Fruit | Drop down list |
| Apple | Green |
| Apple | Red |
| Orange | Orange |
| Pineapple | Yellow |
Thank you.
Create Data Validation of type List with formula
=OFFSET(Second!$B$1, XMATCH(A2, Second!$A$2:$A$1000), 0, COUNTIF(Second!$A$2:$A$1000, A2), 1)
See the attached demo workbook.
Hello,
I did the same OFFSET function for more mixed data however it gives wrong results. Such Small example data is as follows:
Type Short Name Factory A Company Customer B Company Factory C Company Customer D Company if you use offset or indirect, it doesnot help unfortunately, which i have trying to solve this for hours now. Very sad on this. can you guys help please?
Thanks Hans
Hi Hans,
Thank you so much, it works.
May I ask one more: the pre-requisite of this setup is all rows need to in order in sheet:Second.
If it is not in order, error occurs as below.
Can the drop down list still works for this case?
Thank you.
No, the rows for one item should be adjacent. Otherwise it becomes very complicated.
Create Data Validation of type List with formula
=OFFSET(Second!$B$1, XMATCH(A2, Second!$A$2:$A$1000), 0, COUNTIF(Second!$A$2:$A$1000, A2), 1)
See the attached demo workbook.
