Forum Discussion

Francis Cleofe's avatar
Francis Cleofe
Copper Contributor
Aug 17, 2017

Anyone know how to make a dropdown list dependent on the contents of 2 other dropdowns?

I'm trying to create a form with Province, City, District dropdowns being dependent on each other. I was able to create the dropdown for the provinces and make city dropdown only populate cities with...
Formulas & Functions
Tutorial
  • Francis Cleofe's avatar
    Francis Cleofe
    Copper Contributor
    Aug 18, 2017

    Hi Sergei!

     

    Thanks for the quick response! I tried this method but this doesnt seem scalable to large quantities of data as i would have to make multiple verticals per state. Im actually working on the entire philippines and have attached the sample data to help clarify would you happen to know of any alternative?

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 18, 2017

      Hi Francis,

       

      Okay, i see the point. Will play with this bit later.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 18, 2017

        Hi Francis,

         

        I continue with your data structure, the only uses your Tables instead of ranges like $A:$A - better at least from performance point of view.

        Instaed of Table1`,.. they are renamed as Province, ProvinceCity and AllData.

        Name of the used columns are added to list of names (see in Names Manager), references as here

         

         

        Thus for the first list (Province) the formula is

        =INDIRECT(ProvinceList)

        (to work with tables we have to use INDIRECT, structured references don't work within list formulas)

         

        Next one for the City which depends on Province is actually as yours, only in tables notation

        =OFFSET(Lists!$D$1,MATCH(A2,INDIRECT(PCProvince),0),1,COUNTIF(INDIRECT(PCProvince),A2),1)

        For the third list we shall filter selected Province and City what we do in MATCH with AND condition; and number of rows is calculated by COUNTIFS with two conditions (instaed of COUNTIF)

        =OFFSET(Lists!$G$1,MATCH(1,(INDIRECT(AllProvince)=A2)*(INDIRECT(AllCity)=B2),0),2,COUNTIFS(INDIRECT(AllProvince),A2,INDIRECT(AllCity),B2),1)

        The file is attached

         

Resources