Filtering Data Validation List based on Offset Value

[Noodnutt]

Hi Team

I have seen a few posts regarding Dependent Lists and the use of Indirect, but they don't seem to fit what I am trying to achieve, either that it's likely I am not grasping the concept.

I tried the following combination:
=COUNTIF(OFFSET('List Ref.'!$C$3,,,COUNTA('List Ref.'!$C:$C)-1),EmpStatus="L")
=COUNTIF(EmpStatus,"L",OFFSET('List Ref.'!$C$3,,,COUNTA('List Ref.'!$C:$C)-1))

Essentially, I have a Dynamic Data Validation List which works as expected, but I am trying to expand it to filter the list based on the drivers EmpStatus = "L" (Live) so that any drivers with "X" do not appear in the list.

I vetted the following image to represent what I would like to achieve.

As always, TIA.

Regards
Mark.

 

[mumps]

If you selected "Driver A" from the drop down in column C, you would see only row 2. If you selected "Driver D" from the drop down in column C, you would not see any row. Is this correct?

 

[Fluff]

You can create a list in another column & use that like

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H
1	List
2	Driver 1		Driver 1					L
3	Driver 2		Driver 2					L
4	Driver 3		Driver 3					L
5	Driver 5		Driver 4					X
6	Driver 6		Driver 5					L
7	Driver 7		Driver 6					L
8	Driver 10		Driver 7					L
9	Driver 11		Driver 8					X
10			Driver 9					X
11			Driver 10					L
12			Driver 11					L
13
14
Master

Cell Formulas
Range	Formula
A2:A12	A2	=IFERROR(INDEX($C$2:$C$100,AGGREGATE(15,6,(ROW($C$2:$C$100)-ROW($C$2)+1)/($H$2:$H$100="L"),ROWS(A$2:A2))),"")

Named Ranges
Name	Refers To	Cells
Driver_List	=OFFSET(Master!$A$2,,,COUNTIF(Master!$A$2:$A$30,"?*"))	A2:A12

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Driver_List