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

[LindsayP]

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! 

[#2]

Please refer to my reply here for an idea of a dynamic dependent drop-down list:
https://techcommunity.microsoft.com/t5/Excel/Data-validation-lists-flow-chart/m-p/352133#M25722

[#3]

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

[#4]

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. 

[#5]

You just have to rename the Data sheet and the workbook name in me uploaded file. The layout of your data and drop-down list must be strictly in accordance with mine so that the formulas will deliver the expected results.

[#6]

Thank you!

I have also been looking for this solution for a long time.

I have the following questions:

1. How do I add more categories?
2. How do I change your input message?

Regards

Johnny