Dependent drop-down lists based off table

Hi all,

I have a spreadsheet that looks a little like this:

Header 1	Header 2	Header 3	Header 4
X	A	1	x
X	A	1	y
X	A	2	x
X	A	2	y
X	B	1	x
X	B	1	y
Y	A	1	x

I'm looking to do some funky data validation where there is a drop-down list for each header, and in each of those drop-down lists are the unique values in that header's column, but where each list is only those values that occur based on the previous list.

So for the above example, list 1 is 'X or Y'; select X, then list 2 becomes 'A or B'; select A and list 3 is '1 or 2' - if you select B list 3 is '1' only. And so on...

Now I can do this with a whole load of manual name management, creation of tables which only have unique values etc. - but in my actual spreadsheet, I've got about 160 unique values for Header 3 and 7,500 for Header 4, so that's looking like a daunting task.

So I'm hoping you fine folks on this forum will be able to help - is it possible to make dependent drop-down lists based off a single table with only unique values shown?

I'm on excel 2016, so don't have access to the newest functions, but will happily use Macros if that makes life simpler.

Many thanks!

T.

Edit: added an attached sample spreadsheet - the table is what I'm looking to validate, and I've put in example boxes showing a possible flow through the 4 drop-down lists.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Macro in sheet "Data": right-click on tab and view code

Please Login or Register  to view this content.

in Standard module

Please Login or Register  to view this content.

"Location" is named range "Location_DV": "Person/Cars" DVs are generated by code

Columns L:O is filtered list based on DV selection

See attached which a formula-based solution

in R3

Formula:

Please Login or Register  to view this content.

in S3

Formula:

Please Login or Register  to view this content.

Enter with Ctrl+Shift+Enter (CSE)

in T3

Formula:

Please Login or Register  to view this content.

Enter with Ctrl+Shift+Enter (CSE)

All the above are dynamic named ranges.

I have left the VBA which generates the filtered data: if not required, then delete code from sheet "Data".