Data Validation List (Subset)

This is driving me mad, please can somebody help me as I have not been able to find any answers to this problem.

I have created a duty sheet which tells me which employees are working at any given hour of the day.

The sheet makes use of an employee list and a vlookup to populate their names. This employee list contains both workers and supervisors.

I am trying to find a way of using this employee list, but the supervisors only in a data validation list, for when leave, etc is authorised.

I know that a simple way is to put the supervisors in another column and name it, but this means that I have to maintain both lists and I’m lazy. So using the simple table below as an example, I want to create a validation list containing employees B and C only.


Employee Role
A worker
B supervisor
C supervisor
D worker

Thank you.

AlexRoberts,

Try:

Dependent validation lists. Debra has a neat little tutorial here.
http://www.contextures.com/xlDataVal02.html

Data Validation > Drop-Down Lists - Dependent
http://www.bettersolutions.com/excel...E229212022.htm

Cascading queries
http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

http://www.contextures.com/xlDataVal05.html

Excel Data Validation - Add New Items
http://www.contextures.com/excel-dat...ation-add.html

Here's a workbook showing how i do the very thing you're trying to do.

Two-column data base of supervisors and employees. Be sure to sort by the supervisor so it puts all the rows together by supervisor, sorted ascending. This allows the dynamic "range" formula to see where the range of employees for each supervisor starts and ends.

Then a dynamic DV formula grabs all the employees and displays them based on the supervisor already selected.