Dependent data validation list: non-VBA

Factotum

Board Regular
Joined
May 14, 2015
Messages
115
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.

StateCityAttraction
CaliforniaSan DiegoSan Diego Zoo
CaliforniaAnaheimDisneyland
California
San FranciscoAlcatraz
CaliforniaSan FranciscoGolden Gate Bridge
New YorkManhattanEmpire State Building
New YorkBrooklynBrooklyn Bridge

<tbody>
</tbody>

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

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

william_man

New Member
Joined
Oct 31, 2019
Messages
19
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.
 

william_man

New Member
Joined
Oct 31, 2019
Messages
19
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:
Code:
=UNIQUE(SORTBY(A2:C7,A2:A7,1,B2:B7,1,C2:C7,1))
Enter this formula into cell I2 to create the City table:
Code:
=UNIQUE(SORTBY(A2:B7,A2:A7,1,B2:B7,1))
And this into L2 to create the State table:
Code:
=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:
Code:
=data!L2#
For City, this formula locates the relevant cities if a state is selected, otherwise it shows all cities:
Code:
=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:
Code:
=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,499
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

.. 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
 

Factotum

Board Regular
Joined
May 14, 2015
Messages
115

ADVERTISEMENT

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

New Member
Joined
Oct 31, 2019
Messages
19
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

Board Regular
Joined
May 14, 2015
Messages
115
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,814
Messages
5,574,487
Members
412,597
Latest member
Timtec
Top