Excel data validation

[Haveesha]

Hi all. I need a help on this. When I select a role in 2nd table (ex:A12 - Admin) then all the values against selected value should be listed in a drop down (ex:on B12) to select as I need. How can I achieve something like this? 

[#2 · Accepted solution]

@Haveesha 

Sort the list of names/roles by Role then by Name.

For B12, set up Data Validation of type List with source

=OFFSET($A$1,MATCH($A12,$B$2:$B$7,0),0,COUNTIF($B$2:$B$7,$A12),1)

See the attached sample workbook.

[#3]

Oh thank you so much for this solution. I will use this one.

[#4]

But can we do this for large set of data?

[#5]

@Haveesha 

Yes, I've used it for lists with thousands of rows.

[#6]

Any idea on how I should ignore blanks in the list itself?

[#7]

@Haveesha 

The source list shouldn't have blanks.