Roster staff drop down list who meet criteria for shift

[Justmegan93]

Hi

I am trying to create a roster sheet which will make it easier to select staff from a drop down list

'Vehicles' and 'Routes' are selected from the drop down list made from named ranges in 'Data' sheet

'Required' is then populated from the vehicle selected in Column B

When selecting the staff they must have competency of the vehicle and have the route knowledge to the appear in a drop down list in column K

Does anyone know a way I could achieve this? Whether it is using formulas or vba

Or if you know any web pages that could help can you please link below?

I have attached a sample of the workbook

Thanks
Megan

[CAntosh]

I have to imagine that there's a more straightforward solution than what I came up with, but I started building and ended up with something that works, so here it is. I created a table on your data sheet that indicates whether each driver is qualified for the listed jobs. The table makes use of the formula below entered into N8, then filled down, then filled right as far as you need (I filled through up to 10 jobs in the attachment):

=COUNTIFS($G$8:$G$66,INDEX(Roster!$B$2:$B$21,MATCH(Data!N$7,Roster!$A$2:$A$21,0)),$H$8:$H$66,$M8)*COUNTIFS($J$8:$J$111,INDEX(Roster!$I$2:$I$21,MATCH(Data!N$7,Roster!$A$2:$A$21,0)),$K$8:$K$111,$M8)

Below the table, I use the following formula to translate the results into names - these will be the drivers qualified for the jobs as listed on the first sheet. This formula should be array-entered (with Ctrl + Shift + Enter instead of Enter) into N16, then filled down and right as far as needed.

=IFERROR(INDEX($M$8:$M$14,SMALL(IF(N$8:N$14=1,ROW(N$8:N$14)),ROW(1:1))-7),"")

You can then create a named range that I called 'Qualified' using the following formula:

=OFFSET(Data!$N$16,0,CEILING((ROW()-1)/4,1)-1,COUNTIF(OFFSET(Data!$N$8,0,CEILING((ROW()-1)/4,1)-1,8,1),1),1)

This named range can be used on the first sheet (Roster) for your data validation. It should return only the options that fit the job's selected vehicle and route. Everything should update automatically as you change the vehicle and route entries on the first sheet. It seems to work for me, but give the attachment a try and see if does the trick for you.