Dependent data validation list: non-VBA

[Factotum]

I've found plenty of sites talking about dependent data validation drop down lists, but none that I've been able to apply to my situation. I need three data validation lists, each dependent on the one before it - all three lists will come from the same table. It would also be nice if the user could start typing a name into the cell and have it shorten the list to show only matching names. I've done this before with a Userform in VBA, but this one is going to be so widely distributed that I want to keep VBA out of it if possible.

Here's an example:
A table like the one below (but 13,000 rows long) will be in a hidden sheet. On the main tab, a user will have three drop down lists. If they already know the attraction, they could go straight to that box and type it in, or drop the menu down and click on the one they want. If they need help narrowing down the list in order to find the attraction, I want them to be able to click the first drop down and choose from California or New York. Say the user chooses California, then I want the next drop down list to only show San Diego, Anaheim, and San Francisco. If they choose San Francisco, then the third drop down list would only show Alcatraz and Golden Gate Bridge. Essentially a VLookup that returns an entire list of matching values.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]City[/TD]
[TD]Attraction[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]San Diego[/TD]
[TD]San Diego Zoo[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]Anaheim[/TD]
[TD]Disneyland[/TD]
[/TR]
[TR]
[TD]California
[/TD]
[TD]San Francisco[/TD]
[TD]Alcatraz[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]San Francisco[/TD]
[TD]Golden Gate Bridge[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Manhattan[/TD]
[TD]Empire State Building[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Brooklyn[/TD]
[TD]Brooklyn Bridge[/TD]
[/TR]
</tbody>[/TABLE]

Is this even possible with Data Validation, or is VBA the only answer?

 

[william_man]

Which version of Excel are you using? I have a template that does exactly this, but it makes use of dynamic array formulas, which only work with Excel 365.

 

[Factotum]

I've got Excel 365

 

[william_man]

Just realised I can't post attachments on this forum so this might be quite a long post...

As your list has 3 levels (state, city, attraction) the first step is to use the UNIQUE and SORTBY dynamic array formulas to create tables that have the unique values for each level arranged in a hierarchical layout.

With your source table in columns A:C, enter this formula into cell E2 to create the Attraction table:

=UNIQUE(SORTBY(A2:C7,A2:A7,1,B2:B7,1,C2:C7,1))

Enter this formula into cell I2 to create the City table:

=UNIQUE(SORTBY(A2:B7,A2:A7,1,B2:B7,1))

And this into L2 to create the State table:

=UNIQUE(SORTBY(A2:A7,A2:A7,1))

You should end up with something like this, but your formulas need to be extended to cover the entire 13,000 rows:

Now that the options are laid out in this way it is just a case of setting up the data validation on the user selection tab.
My example is laid out like this with the data validation lists in A2, B2 and C2

The data validation list for State is straightforward as the options are static. Enter this in the data validation list source:

=data!L2#

For City, this formula locates the relevant cities if a state is selected, otherwise it shows all cities:

=IF(A2="",data!J2:J6,OFFSET(data!I1,MATCH(A2,data!I:I,0)-1,1,COUNTIF(data!I:I,A2),1))

Attraction is a similar formula:

=IF(B2="",data!G2:G7,OFFSET(data!F1,MATCH(B2,data!F:F,0)-1,1,COUNTIF(data!F:F,B2),1))

Do note that there are some limitations to this solution, but this should give you an idea of the basic concept. Let me know how you get on!

 

[Peter_SSs]

.. the first step is to use the UNIQUE and SORTBY dynamic array formulas ..

Noting that as yet, those functions are only available to "a portion of Office Insiders" & certainly not to all 365 subscribers

 

[Michael M]

You could have a look here for a tutorial on how to....

https://www.contextures.com/xlDataVal02.html

 

[Factotum]

Thank you all for your input and ideas!

william_man - unfortunately, I don't have those formulas yet like Peter mentioned. I'll definitely take note of your response for when I get that upgrade - it looks like an elegant solution to my problem.

Michael M - I had visited your link before, but had written it off without trying it. I went through all the steps this morning and it does work exactly the way I need it to. The only problem is the time involved in setting up the tables. If it was a one-time setup, I wouldn't mind investing the time, but my lists will be changing daily/weekly for the next little while as we're doing a major restructuring. I would end up with dozens of lists I think. I've got 5 Areas in the main list, and each of those 5 areas have around 10 Regions, and each of those Regions have 10-12 Divisions. Maintaining a list like that could be very challenging, unless I can somehow structure a pivot table into the correct format. I think I'll give that a try.

If that doesn't work, I may have to dust off an old VBA project and modify it for this - unless anyone has other suggestions? Thanks again!!

 

[william_man]

Apologies, I didn't even know I was an Insider!

@Factotum - you could use pivot tables to replicate the steps with the UNIQUE and SORTBY formulas. It would require an extra refresh step to update if the source data changed, but everything else should still work in the same way.

 

[Factotum]

I've been trying to become an Insider for a while, but my organization doesn't allow it currently. Thanks again for the suggestions - I'll give the pivot table idea a try.