Cascading dependent dropdowns based on table/range of accepted combinations

[AOB]

I'm trying to create a dynamic / dependent set of dropdowns across multiple columns, where each of the dropdowns on a particular row are populated based on the selections in each of the dependent columns.

I have a table/range with all of the acceptable combinations :

Region	Country	State/Province	City
Europe	Ireland	Leinster	Dublin
Europe	Ireland	Munster	Cork
Europe	Ireland	Munster	Limerick
Europe	Ireland	Ulster	Belfast
Europe	UK	Greater London	London
Europe	UK	Lancashire	Manchester
America	USA	California	Los Angeles
America	USA	California	San Francisco

So what I am trying to achieve is a series of dropdowns per row, where as each cell value is selected, the dropdown options for the dependent cells are reduced such that only acceptable combinations are available.

So if the user selects "Europe" from the Region column, the dropdown in the Country column reduces to just "Ireland" or "UK" (USA is no longer an option), the dropdown in the State/Province column reduces to just "Leinster", "Munster", "Ulster", "Greater London" or "Lancashire" (California is no longer an option) and the dropdown in the City column reduces to just "Dublin", "Cork", "Limerick", "Belfast", "London" or "Manchester" (Los Angeles and San Francisco are no longer options)

If the user then selects "UK" from the Country column, the State/Province column options reduce to just "Greater London" or "Lancashire" and the City column options reduce to just "London" or "Manchester" and so on.

There can be no requirement on the user to select options in any particular order (i.e. they don't have to choose Region, then Country, then State, then City; they need to be able to select options in any arbitrary order)

I presume I will need a series of dynamic named ranges involving SORT, UNIQUE and FILTER but I'm struggling to define those ranges in such a way that I can then apply them to the data validations for each cell.

Any suggestions?

 

[AOB]

Just as some WIP...

This is the closest I can come up with but it's not really fit for purpose...

"User Entry" section is a table called tblUserEntry, with columns named "Region", "Country", "State" & "City"

The acceptable combinations are in a separate table which I'm just calling tblGeo, similarly with columns named "Region", "Country", "State" & "City"

To achieve the necessary behaviour for a single row, I can add the following formulas in separate columns outside the table (I'm using columns K through N), on the same row, to dynamically create a filtered list for each column, which "spills" into the column below.

=SORT(UNIQUE(FILTER(tblGeo[Region],(tblGeo[Country]=tblUserEntry[@Country])*(tblGeo[State]=tblUserEntry[@State])*(tblGeo[City]=tblUserEntry[@City]),"*"),FALSE,FALSE))

=SORT(UNIQUE(FILTER(tblGeo[Country],(tblGeo[Region]=tblUserEntry[@Region])*(tblGeo[State]=tblUserEntry[@State])*(tblGeo[City]=tblUserEntry[@City]),"*"),FALSE,FALSE))

=SORT(UNIQUE(FILTER(tblGeo[State],(tblGeo[Region]=tblUserEntry[@Region])*(tblGeo[Country]=tblUserEntry[@Country])*(tblGeo[City]=tblUserEntry[@City]),"*"),FALSE,FALSE))

=SORT(UNIQUE(FILTER(tblGeo[City],(tblGeo[Region]=tblUserEntry[@Region])*(tblGeo[Country]=tblUserEntry[@Country])*(tblGeo[State]=tblUserEntry[@State]),"*"),FALSE,FALSE))

I can then create dynamic named ranges referencing the results of these formulas :

RegionList :

=OFFSET(Sheet1!$K$2,0,0,COUNTA(Sheet1!$K:$K),1)

CountryList :

=OFFSET(Sheet1!$L$2,0,0,COUNTA(Sheet1!$L:$L),1)

StateList :

=OFFSET(Sheet1!$M$2,0,0,COUNTA(Sheet1!$M:$M),1)

CityList :

=OFFSET(Sheet1!$N$2,0,0,COUNTA(Sheet1!$N:$N),1)

Finally I can add these named ranges to the Data Validation for each of the columns in the User Entry table

As each option is selected, the formulas in K to N are updated based on the selected options, the named ranges are resized to the filtered options and the other dropdowns are suitably updated.

But - obviously this is only good for one row. If the User Entry table is going to have an arbitrary number of rows, I need to be able to adjust this for each row (and I can't add these formulas to the table because the output is an array of cells - the array of filtered options - so they will just spill into the next table row and cause errors)

So - and this is where it falls over, obviously - why not refer to the active cell in the formulas rather than simply the value on the corresponding row?

=SORT(UNIQUE(FILTER(tblGeo[Region],(tblGeo[Country]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[Country]))))*(tblGeo[State]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[State]))))*(tblGeo[City]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[City])))),"*"),FALSE,FALSE))

=SORT(UNIQUE(FILTER(tblGeo[Country],(tblGeo[Region]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[Region]))))*(tblGeo[State]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[State]))))*(tblGeo[City]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[City])))),"*"),FALSE,FALSE))

=SORT(UNIQUE(FILTER(tblGeo[State],(tblGeo[Region]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[Region]))))*(tblGeo[Country]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[Country]))))*(tblGeo[City]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[City])))),"*"),FALSE,FALSE))

=SORT(UNIQUE(FILTER(tblGeo[City],(tblGeo[Region]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[Region]))))*(tblGeo[Country]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[Country]))))*(tblGeo[State]=INDIRECT(ADDRESS(CELL("row"),COLUMN(tblUserEntry[City])))),"*"),FALSE,FALSE))

So now, when you change any cell in the User Entry table, all those formulas refresh to the values specified on that specific row, all the dynamic named ranges resize to the relevant options and all the dropdowns are specific to that row.

But - of course - you have to change the cell in order for the CELL("row") reference to update, and everything else to cascade. It's not sufficient to simply click on a cell.

So from a UX perspective, this is terrible - when I create a new row, or move to a different row to make a change, I have to go into one of the dropdowns and select a wrong option, in order to update all the dropdowns so that I can then select the right option. Worse, initially all of those dropdowns will update to the filtered options for my wrongly selected option, so I really have to go through each of them and reset them in order to then go through them all again and make the correct selections.

(Before anybody points out how much easier this would be with VBA - believe me, I know - but VBA is not an option here, I need to try to create a template with no code if at all possible. I could knock up a VBA version that does all this in a couple of hours, easy, the problem isn't the construction of the code, it's the presence of it. And what I have above is really, really close to what I need to achieve, so if anybody can help me with any suggestions to refine it to utopia, it would be hugely appreciated!!)

 

[Akuini]

@AOB
I shared an example of 3 level dependent data-validation using the new function in Excel 365, without VBA.
Please, check post #30 in this thread:

3 or More Dependent Data Validation, with VBA, easy to set up

I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might...

www.mrexcel.com

 

[AOB]

@AOB
I shared an example of 3 level dependent data-validation using the new function in Excel 365, without VBA.
Please, check post #30 in this thread:

3 or More Dependent Data Validation, with VBA, easy to set up

I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might...

www.mrexcel.com

Thanks Akuini,

Lovely solution, thanks for sharing. I had already played around with something similar but ran into a couple of fairly fundamental issues with it.

My data table (from which the various combinations need to be validated) is considerably larger than your example, with approximately 20,000 rows of combinations across four columns (so I need a four level dependent dropdown rather than three - and every additional level means an order of magnitude more combinations). Your solution requires every combination to be available in the helper table, which means that helper column (in my case) needs to have 20,000 columns in it. Okay, it's the same formula generating all those combinations, but that's very intensive on the workbook calculation, and slows everything down considerably. Also, that data table is somewhat dynamic (it is populated with all of the current combinations of those attributes from Active Directory) so it may grow or shrink on each iteration. I don't have a static set of data. Again, that's not prohibitive - but it almost certainly requires some VBA to keep all those helper ranges in check.

Also, my users don't necessarily select left-to-right - they don't choose a region, then a country, then a state, then a city, then stop. They may just choose a region, and then jump to the city (and be "agnostic" towards the state and country). Or, they may choose a state, then look through all the corresponding cities, find the one they want, then remove the state again (back and forth). Or - well, they're users, they could do any number of things. Different users have different ways of looking at and manipulating their data.

The third problem (and this one really perplexes me!) is that I don't seem to have the TEXTSPLIT function available to me, even though I am using 365! I have TEXTJOIN, ARRAYTOTEXT etc. but TEXTSPLIT isn't there. No idea why!

I have managed to put together a solution that works really well, fast enough not to be cumbersome to use, but it does involve VBA, which was what I was originally trying to avoid. I've basically bitten the bullet now and accepted that VBA won't be avoidable for me. It uses the same SORT > UNIQUE > FILTER concept as yours, but uses VBA to define the base values based on which row of the table the user is working on at the time. This allows users to hop arbitrarily between columns (skipping, backwards etc.) and rows. And my helper table is static in the sense that no matter how many combinations are available, it just has one column for each attribute (four columns total) rather than an arbitrary number of columns for an arbitrary number of combinations.

But I do appreciate you pointing me towards your post, I will definitely review it in closer detail and I strongly suspect there will be some concepts in there that I can tweak for myself.

Thank you!

 

[Akuini]

with approximately 20,000 rows of combinations across four columns

Well, with that many combinations, you're right it will slows everything down.

I have managed to put together a solution that works really well, fast enough not to be cumbersome to use, but it does involve VBA,

Could you share a sample workbook? I'm really interested in different solutions to this problem. And I believe others might find it useful too.