Remove Blanks from cascading (dependent) drop-down list

Question

So I've got a table with two drop-down lists; the first is a drop-down list of categories, the second is a list of projects within those categories. The way it works is simple: you pick a category, and then the second list gives you the appropriate projects. The lists are based on this table:

The problem I'm facing is that the number of projects in each category isn't the same, so while it works fine for categories like Graphic Design (Column D), the list for categories like 3D animation (Column B) are filled with blank spaces.

Is there any simple way of getting rid of the spaces?

Ideally by having excel ignore blanks, because we add new projects from time to time, so Id like the range to stay the same, and not have to fiddle with it too much when adding new projects.

Thanks :)

Answer 1

Here is a simplified illustration on one way to achieve what you want. Note I am applying this in Sheet2.

Let's assume that your lists are in a table called Table1.

Let's assume that your initial dropdown is in E2, and that options must correspond with the header names in Table1.

Firstly we will create a name called DepDD_All, as follows:

=INDEX(Table1,0,MATCH(Sheet2!$E$2,Table1[#Headers],0))

This will generate a sub-list for your dependent dropdown, including blank lines.

To create lists based only on the actual line count, I will use a secondary name, called DepDD_Dyn, as follows:

=INDEX(DepDD_All,1,1):INDEX(DepDD_All,COUNTA(DepDD_All),1)

Reference this name in your dropdown to display only lists with relevant number of items.