Forum Discussion

CelineFoong's avatar
CelineFoong
Copper Contributor
May 24, 2021

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...
  • HansVogelaar's avatar
    HansVogelaar
    May 24, 2021

    CelineFoong 

    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)

HansVogelaar's avatar
HansVogelaar
MVP

ChristianDeinzer 

It looks like data validation has to be refreshed when the workbook is opened.

The attached new version is now a macro-enabled workbook; you may have to allow macros when you open it. I hope this works for you.

Paul_UK86's avatar
Paul_UK86
Copper Contributor
Sep 05, 2023

HansVogelaar 

 

How do I make Drop down 2 dependant on the selection of Drop down 1 and Drop down 3 dependant on the selections of both?

 

Thank you in advance 

    • Paul_UK86's avatar
      Paul_UK86
      Copper Contributor
      Sep 06, 2023

      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

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Sep 06, 2023

        Paul_UK86 

        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.

Share

Resources