Data Validation with multiple criteria

[reynold]

Hello Friends,

This is my first post so please let me know if my query is not clear to understand.
I am novice user of excel.

I am creating data validation i.e. drop down list for the service "Spa"

1)Select Specialist Gender (Lets select Male)
2)Select Specialist (this should only show names of Spa specialist who are male)

I am struggling to populate the list, please help. Below are the two tables giving information on Specialist and their gender.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Hair Specialist[/TD]
[TD]Make up Specialist[/TD]
[TD]Spa Specialist[/TD]
[TD]Pedicure Specialist[/TD]
[/TR]
[TR]
[TD]Sophia[/TD]
[TD]Hannah[/TD]
[TD]Robert[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Michael[/TD]
[TD]Anna[/TD]
[TD]James[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Male[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Sophia[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Olivia[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]Hannah[/TD]
[/TR]
[TR]
[TD]Michael[/TD]
[TD]Anna[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 600"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]speciality1[/TD]
[TD]spa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sex[/TD]
[TD]male[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of name[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Robert[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD]sex[/TD]
[TD]speciality1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]male[/TD]
[TD]hair[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]male[/TD]
[TD]pedicure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]male[/TD]
[TD]spa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Michael[/TD]
[TD]male[/TD]
[TD]make up[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sophia[/TD]
[TD]female[/TD]
[TD]hair[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Olivia[/TD]
[TD]female[/TD]
[TD]make up[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hannah[/TD]
[TD]female[/TD]
[TD]make up[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]female[/TD]
[TD]spa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]with a pivot table you enter spa and male[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]and it will list all names that match[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]the speciality and provider gender[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

You may also find this useful.