Create dependent drop-down lists in Excel 365 using table column headers as the dropdown selection values

Question

I have struggled finding an explanation on how to create dependent dropdown lists when the values in the array are functioning as a text "checkbox" rather than actually including the value we want displayed in the dropdown list.

As an extremely simplified example:

Rather than the source data looking like this

Fruit	Color
Apple	Red
Apple	Green
Orange	Orange
Kiwi	Green
Kiwi	Brown
Apple	Yellow
Banana	Yellow

My data looks like this

	Red	Green	Orange	Yellow	Brown
Apple	X	X		X
Orange			X
Kiwi		X			X
Banana				X

Where the "fruits" down the Y column would be the originating dropdown menu and the "Options" on the X axis would be the dependent dropdown's potential values, with the valid options being determined by whether or not that Option column contains any text in the row determined by that source "fruit" dropdown.

The reason the source data is being managed this way is because these tables are on the order of 50+ rows x 25-150 columns, where the row and column counts are subject to increase or decrease. It's much easier to maintain a lot of changing combinations by adding/removing a few checkboxes/rows/columns than creating thousands of combination listings that could be more easily subject to mistaken adds/deletes/duplicates.

Is there a way to do this using formulas to create the named ranges and/or in the data validation to output the column header(s) as the dropdown list choices when that column contains text in the row specified by the fruit choice in the originating dropdown list?

The only thing I've been able to manage so far is the creation of the named table of data, and a separate named table/range containing the fruit options.

I have yet been unable to figure out how to manipulate INDEX() and MATCH() data validation formulas so I can return the color column headers.

Answer 1

If you're using 365, could you make use of the FILTER function?

Assuming a table called tblData, you could nest a couple of filters. The first would extract your data row:

=FILTER(tblData,tblData[Fruit]=SelectedFruit)

where SelectedFruit is a named range referencing the cell containing the fruit that the user seleceted.

And the second would extract the colours:

=FILTER(tblData[#Headers],A20#="x")

where A20# is the cell address of the first filter.

If you nested the filters, the formula would simply be:

=FILTER(tblData[#Headers],FILTER(tblData,tblData[Fruit]=SelectedFruit)="x")

Unfortunately you can't write this formula directly into a data validation list source. There are a number of ways around this. Simplest is to write the formula into a blank cell somewhere and just reference that spill range in your list source. In this example, I wrote it in cell A7 and just created a named range for that:

So your Selected Fruit data validation would look like this:

and your Selected Colour would look like this: