Data Validation Drop down list - dependent on two criteria

[taostep1]

I have one worksheet with a table related to "Service" and "Rank" details with their applicable Job Descriptions.

In a separate worksheet, I am trying to create a data validation list that's smart enough to list all of the relative "Job Descriptions" (from the other sheet) if the Rank and Service criteria match, accordingly.

Per the example below, is there a data validation formula or method to list out the appropriate Job Descriptions from the other source table if the Service and Rank conditions are met? Please see the 2nd table - 3rd column as to what I am referring to.

Any help would be great!


[TABLE="width: 500"]
<tbody>[TR]
[TD]Service[/TD]
[TD]Rank[/TD]
[TD]Job Description[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACA[/TD]
[TD]04[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TPA[/TD]
[TD]11[/TD]
[TD]STAFF 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TPA[/TD]
[TD]11[/TD]
[TD]STAFF 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POF[/TD]
[TD]52[/TD]
[TD]Contractor[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACA[/TD]
[TD]04[/TD]
[TD]Senior[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TPA[/TD]
[TD]11[/TD]
[TD]STAFF 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Service[/TD]
[TD]Rank[/TD]
[TD]Job Description (Data Validation List)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACA[/TD]
[TD]04[/TD]
[TD]Show Partner and Senior in Dropdown list[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TPA[/TD]
[TD]11[/TD]
[TD]Show STAFF 1, STAFF 2 , STAFF 3 in dropdown list[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TPA[/TD]
[TD]11[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Show STAFF 1, STAFF 2 , STAFF 3 in dropdown list[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POF[/TD]
[TD]52[/TD]
[TD]Show Contractor in dropdown list[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACA[/TD]
[TD]04[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Show Partner and Senior in Dropdown list[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TPA[/TD]
[TD]11[/TD]
[TD]Show STAFF 1, STAFF 2 , STAFF 3 in dropdown list[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]