Data Validation using selective lookup data

[DingDango]

Hello, I have a situation where I have a named range of data that looks like this:
ColA ColB
Q1 Al
D1 Andrew
C3 Bob
T3 Fred
Z1 Joe
Q1 Ted
Column A is a classification and column B is the users name.
The list is sorted by column B so the names would show up alphabetically in the data validation list.
So far so good.

Now the part that is messing me up: I want to have a data validation dropdown specific to each classification.
For example, I want a data validation dropdown that displays from this list only the users with a C3 classification.
I want another data validation dropdown that displays from this list only the users with a D1 classification and so on for each classification.
If there are five classifications, I'll have five data validation dropdown lists.
The question is how do I create a formula that I can put in the data validation source field that will filter the named range that is the above list?

Thanks for your help!!

 

[Smitty]

Welcome to the Board!

Check out Dependent Data Validation.

HTH,

 

[DingDango]

Smitty, I reviewed that but I don't have the luxury of having distinct lists of each classification as the list I described is dynamic and being updated every time the spreadsheet is opened. The update comes from a SQL database in the form I showed.

What I need is something akin to a vlookup that would pull out of the list just the users that had a specific classification.

Thanks

 

[Smitty]

You'll probably have to use VBA. Off the top of my head, maybe apply Data Filter to create each list, then copy just the visible cells to what can be a static location, which you can then use for the validation.