Conditional Data Validation using one list

[welshgasman]

Hi all,
I have an Excel workbook that I use on my Android phone.
In that workbook I have a dropdown for a list of passengers that are on another sheet. This works great. I also have another dropdown for destinations.

Sadly however, as the people we take are elderly, several have recently passed away. Several have moved out of the area to residential homes.
So now I want to remove them from the current drop down, so I cannot inadvertently select them and they reduce the choices available. I cannot just delete them, as they are also present in a Master workbook, which keeps track of everything since I joined the Community Car Scheme. This workbook is my Monthly passenger trips sheet.

I can put in an extra column into the named range, which indicates Deceased/Moved Out of Area, anyone not current.
My dilemma is how do I use that to restrict the list that is presented in the dropdown?. I have googled Conditional Validation, but they all appear to have to use two lists and I want to keep it at just the one.?

If this was Access, then I would just use add the criteria to the combo source. How can I do something similar in Excel?

TIA

 

[etaf]

you are very welcome,
Apologies for not covering the Array entry - Control-shift-enter and copy down

so used to how array formulas in 365 version do all that for you - so i will try and pay more attention to that for older versions, as it will help others not to go through the steps you had to

 

[welshgasman]

Can I just confirm please?
It does not matter what colum I use in the Small(), where example is K. Could I even use A again?
The reason I ask, is I want to change to dynamic ranges and not create any more that I need to?
Edit: Works great on the dynamic named ranges and what column I use does not appear to make a difference, so I can reuse a named range.

 

[welshgasman]

you are very welcome,
Apologies for not covering the Array entry - Control-shift-enter and copy down

so used to how array formulas in 365 version do all that for you - so i will try and pay more attention to that for older versions, as it will help others not to go through the steps you had to

It does not appear viisble in your posts though? I get to see the {} when I do it?