Forum Discussion
CelineFoong
May 24, 2021Copper Contributor
How can i create 3 dependent drop down list with the 3rd drop down depending on 1st and 2nd list?
Here is my list of data. For Dropdown3, I will want the dropdown to be dependent on BOTH Dropdown1 and Dropdown2. Kindly note that some of the values within Order Information/Shipping Information und...
- May 24, 2021
See the attached sample workbook.
Select C2 on the Data Entry sheet and click Data Validation on the Data tab of the ribbon to see the formula I used. It is
=OFFSET(Lists!$C$1,MATCH(1,(Lists!$A$2:$A$27=A2)*(Lists!$B$2:$B$27=B2),0),0,COUNTIFS(Lists!$A$2:$A$27,A2,Lists!$B$2:$B$27,B2),1)
SergeiBaklan
Sep 05, 2023MVP
If to google "excel dependent drop down list" lot of posts just on first page, e.g. How to make dependent dropdown lists in Excel | Exceljet. You may find more suitable in your case and your Excel version.
Paul_UK86
Sep 06, 2023Copper Contributor
SergeiBaklan thanks! I couldn't get it working using any of the guides online! I think the problem was that my data has multiple words and spaces and is very complicated against basic one word just a few items in a lot of the examples. I found a way to reduce the data down and cross reference using the Define Names - labelling each section of data as the simple name so it matches.
It is a very over complicated way to do it and I am sure any updates to the data will break it - but it works for now. Thanks
- SergeiBaklanSep 06, 2023MVP
In latest Excel it was improved, it ignores duplicates and blanks automatically. But all this is better to discuss on concrete sample if you have more questions.