Forum Discussion

LindsayP's avatar
LindsayP
Copper Contributor
Feb 21, 2019

Dependent Drop-down List- Formula for only showing cells with data rather than blanks

Hello, 

 

I am creating a template that contains multiple, dependent drop-down lists. If you choose a certain thing in the first list, it will only show options related to that choice in the second drop-down, and so on. The items in my 3rd list all contain a different number of populated cells. When I set up the formula, it captures the whole table rather than just showing the cells with data based on what column it is. I end up with blanks as choices in the 3rd drop down.  Based on what is chosen in the 2nd drop-down, how do I only see those choices in the 3rd drop-down rather than blanks as well?

 

Here is my current formula and the example is attached. 

=INDEX('Drop-Down Backup'!$E$4:$S$51,,MATCH($B$5,'Drop-Down Backup'!$E$3:$S$3,0))

 

Thank you! 

excel
Formulas and Functions

5 Replies

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      Feb 22, 2019

      Hello Lindsay, 

      Hereto attached is the file with the dynamic dependent drop-down list you need. Aside from your given data, I added Beverages as a category, along with the corresponding subcategories and items, to the Data sheet. 

      Being dynamic, you can add (or delete) categories, subcategories, and items in the Data sheet without any required modification in the formula. Download it and inform me of your thoughts about it.

      Cheers, 

      Twifoo

      • LindsayP's avatar
        LindsayP
        Copper Contributor
        Feb 22, 2019

        Hello, 

         

        I'm still having trouble getting it to work with how my data is set up. I tried removing the tables so the backup data was just text, but the formula still isn't working. 

Resources