Forum Discussion

Victor Kelvin Martínez Delgado's avatar
Aug 11, 2018

HOW TO DO A CONDITIONAL DROP DOWN MENU??

HELLO PEOPLE

 

I would like to have somebody helps in an little issue.

 

I would like to create a conditional drop down menu. Let me try to explain what I looking for. I have two columns (lets named it A and B) in where I will have a drop down menus in each column, but the list of items that will appear in, let say, column B will depend in the option that the user select in column A.

 

So I need to create a conditional formula in where the table change the list of items that will appear in the drop down menu of column B, based on the options that the user select in column A drop down menu.

 

Hope I explain myself clear. And I will really appreciate if someone can give an idea of how to achieve this.

 

Thanks in advanced for you help and cooperation to help me in this issue..

 

Regards

 

VKM 

 

  • Philip West's avatar
    Philip West
    Steel Contributor

    Give this a go.


    Decide what your first menu will have as options. I've going to pretend I have a menu with the options:

    • Colours
    • Cars

    Now create a table, with a column for each option (using Ctrl+t) and give it a name, i'm going to call mine 'options'. So now you should have an 'Options' table that looks like this:

     

     Colours

    Cars

     Red Ford
     Blue Volvo
     Yellow 
     Green 

     

    For your B menu, you want to set up your validation list as normal, but when it asks for the Source, you want to enter this formula:

    =indirect("options[" & A1 & "]")

     

    Assuming that your first menu is in A1.

     

    What this does is when you select an option in the first menu, it loads the list from the options table in to the second menu.

     

    I've attached a workbook with the above working.

    • Victor Kelvin Martínez Delgado's avatar
      Victor Kelvin Martínez Delgado
      Copper Contributor

      Hello Philip

       

      First apologize for not answer you before. Thank you so much for your suggestions. They are pretty close to what i looking for. 

       

      The situation is that instead of having a first menu with only two options i have at list four options for the first menu and for the second menu i have more options. To give you an example i'm attaching a test sheet name "Prueba" with the menus i would like to appear.

       

      In my example, what i'm looking for is when i select a product from the column name "Portafolio" then the second menu display the list of products that appears under that "Portafolio" i.e. if i choose "Plantas" from the portafolio menu, then in the column name "Producto" the list of items under the "Plantas" menu should be display, but if i choose from the "Portafolio" menu "Paneles", then all the list of items under the column name "Paneles" should be display in the second menu under the column "Producto", and so on. 

       

      That is what i try to achieve. As i said, your approch is very close to what i looking for. I´ve experimenting different type of ways to use your suggestion, however if you can give me a more finnish it idear will be much more appreciate.

       

      Thank you so much for the time that you dedicate to this solution, and appreciate all the cooperation and help in this matter. 

      • Philip West's avatar
        Philip West
        Steel Contributor

        Hia,

        try the attached. I think you were pretty close, but the bit's I've changed are:

        I've made the options table just one table, you had 5 (1 for menu option). In order for it to work you need a single table where the column heading matches the option choices.

         

        I changed the formula in the validation list, you were pointing it to A1, but your 'first choice' menu was in I3, so I pointed it to that.

    • Victor Kelvin Martínez Delgado's avatar
      Victor Kelvin Martínez Delgado
      Copper Contributor

      Hello Philip

       

      First apologize for not answer you before. Thank you so much for your suggestions. They are pretty close to what i looking for. 

       

      The situation is that instead of having a first menu with only two options i have at list four options for the first menu and for the second menu i have more options. To give you an example i'm attaching a test sheet name "Prueba" with the menus i would like to appear.

       

      In my example, what i'm looking for is when i select a product from the column name "Portafolio" then the second menu display the list of products that appears under that "Portafolio" i.e. if i choose "Plantas" from the portafolio menu, then in the column name "Producto" the list of items under the "Plantas" menu should be display, but if i choose from the "Portafolio" menu "Paneles", then all the list of items under the column name "Paneles" should be display in the second menu under the column "Producto", and so on. 

       

      That is what i try to achieve. As i said, your approch is very close to what i looking for. I´ve experimenting different type of ways to use your suggestion, however if you can give me a more finnish it idear will be much more appreciate.

       

      Thank you so much for the time that you dedicate to this solution, and appreciate all the cooperation and help in this matter. 

Resources