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]

thanks for pointing that out Fluff, I have spent much time now on 365 - i just forget thats needed now

 

[welshgasman]

Yes, that is what I am trying to do based on your formula.
Somehow I am missing a step here?

This is what I am doing.
Copying your latest formula and pasting that simply into M2 as you have it. Then copying M2 down a few rows to see the result.

 

[etaf]

so something is not right - based on that XL2BB example

But you are pulling names in column A - and not shown that in the XL2BB

can you post the full spreadsheet sample in XL2BB

 

[welshgasman]

For 2007 the formula will need to be confirmed with Ctrl Shift Enter, rather than just Enter.

Wow, I was going to mention that, but did not see the {} ?

I did try that with no difference.

Following these instructions
Enter an array formula

1. Select the cells where you want to see your results.
2. Enter your formula.
3. Press Ctrl+Shift+Enter. Excel fills each of the cells you selected with the result.

OK, I now have more, but same as first cell?

GCD.xlsm
	C	D	E	F	G	H	I	J	K	L	M
1	First Name	List	Blue Badge	Miles	Has My Number	Notes	SortOrder1	SortOrder2	Title	Current	Active Passengers
2	Joyce	Mrs Ace, Tafarn Y Piod	No	3	No		Ace, Mrs	Tafarn Y Piod	Mrs	Yes	Mrs Ace
3	Graham	Mr Alcock, Bryn Terrace	No	2	No	Folding wheelchair	Alcock, Mr	Bryn Terrace	Mr	Yes	Mrs Ace
4	Betty & Gina	Mrs Alcock, Einon Ct	Yes	2	Yes	Gina has my number	Alcock, Mrs	Einon Ct	Mrs	Yes	Mrs Ace
5		Mr Angel, Frampton Rd	No	2	No		Angel, Mr	Frampton Rd	Mr	Yes	Mrs Ace
Passengers

Cell Formulas
Range	Formula
I2:I5	I2	=IF(A1="","",TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99))&", "&LEFT(TRIM(A2),LEN(TRIM(A2))-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99)))))
J2:J5	J2	=IF($A2="","",TRIM(B2))
K2:K5	K2	=LEFT(A2,FIND(" ",A2))
D2:D5	D2	=A2&","&CHAR(10)&B2
M2:M5	M2	=IFERROR(INDEX($A$2:$A$200,SMALL(IF($L$2:$L$200="Yes",ROW($K$2:$K$200)),ROW(1:1))-1,1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Passengers!$A$1:$H$196	M2:M5, I2

Cells with Data Validation
Cell	Allow	Criteria
G1:G5	List	Yes,No
L1:L5	List	Yes,No
E1:E5	List	No,Yes

 

[welshgasman]

so something is not right - based on that XL2BB example

But you are pulling names in column A - and not shown that in the XL2BB

can you post the full spreadsheet sample in XL2BB

No, sorry, that is as much as I can do with real data.

I will modify the names and post it then.

 

[etaf]

=IFERROR(INDEX($A$2:$A$200,SMALL(IF($L$2:$L$200="Yes",ROW($K$2:$K$200)),ROW(1:1))-1,1),"")
the
INDEX($A$2:$A$200
means pull the names from the column A

the rest is just to get a list based on YES

so effectively its like filter in later versions

Filter ( column A , based on column L = YES )

 

[etaf]

No, sorry, that is as much as I can do with real data.

I will modify the names and post it then.

OK

Try in a new sheet
copy my XL2BB
in and then see - if it works with the very simplified data
and using {}

if it works we know its OK on 2007 version you have

then its just applying to the real data

 

[Fluff]

Select the cells where you want to see your results.

Just enter the formula into M2 using Ctrl Shift Enter, then drag down.

I have spent much time now on 365 - i just forget thats needed now

Me too.

 

[welshgasman]

Sheet is too large as Mini sheet?, same with table

Is this any better?

Mr Excel GCD.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Address	First Name	List	Blue Badge	Miles	Has My Number	Notes	SortOrder1	SortOrder2	Title	Current	Active Passengers
2	2 Name	2 Address	Joyce	2 Name, 2 Address	No	3	No		Name, 2	2 Address	2	Yes	2 Name
3	3 Name	3 Address	Graham	3 Name, 3 Address	No	2	No	Folding wheelchair	Name, 3	3 Address	3	Yes	2 Name
4	4 Name	4 Address	Betty & Gina	4 Name, 4 Address	Yes	2	Yes	Gina has my number	Name, 4	4 Address	4	Yes	2 Name
5	5 Name	5 Address		5 Name, 5 Address	No	2	No		Name, 5	5 Address	5	Yes	2 Name
6	6 Name	6 Address		6 Name, 6 Address	No	2	No		Name, 6	6 Address	6	Yes	2 Name
7	7 Name	7 Address	Margaret	7 Name, 7 Address	No	2	No	Needs help	Name, 7	7 Address	7	Yes	2 Name
8	8 Name	8 Address	Judith	8 Name, 8 Address	Yes	1	Yes		Name, 8	8 Address	8	Yes	2 Name
9	9 Name	9 Address		9 Name, 9 Address	No	2	No		Name, 9	9 Address	9	Yes
10	10 Name	10 Address	Richard	10 Name, 10 Address	No	2	No		Name, 10	10 Address	10	Yes
11	11 Name	11 Address	Fay & Jean	11 Name, 11 Address	No	2	No		Name, 11	11 Address	11	Yes
12	12 Name	12 Address	Avril	12 Name, 12 Address	Yes	2	Yes		Name, 12	12 Address	12	Yes
13	13 Name	13 Address		13 Name, 13 Address	No	1	No		Name, 13	13 Address	13	Yes
14	14 Name	14 Address		14 Name, 14 Address	No	1	No	£19.20 if with Howells to Mary Cave	Name, 14	14 Address	14	Yes
15	15 Name	15 Address	Jean	15 Name, 15 Address	No	2	No		Name, 15	15 Address	15	Yes
16	16 Name	16 Address	Jean	16 Name, 16 Address	No	1	No		Name, 16	16 Address	16	Yes
17	17 Name	17 Address		17 Name, 17 Address	No	2	No		Name, 17	17 Address	17	Yes
18	18 Name	18 Address	Jeff	18 Name, 18 Address	No	1	No		Name, 18	18 Address	18	Yes
19	19 Name	19 Address	David	19 Name, 19 Address	No	2	No		Name, 19	19 Address	19	Yes
20	20 Name	20 Address	Dennis & Glenys	20 Name, 20 Address	Yes	2	No		Name, 20	20 Address	20	Yes
21	21 Name	21 Address	Brian	21 Name, 21 Address	Yes	2	Yes	Needs help, bad eyes	Name, 21	21 Address	21	Yes
22	22 Name	22 Address	Mary	22 Name, 22 Address	No	1	No		Name, 22	22 Address	22	Yes
23	23 Name	23 Address	Alan	23 Name, 23 Address	No	2	No		Name, 23	23 Address	23	Yes
24	24 Name	24 Address	Alan	24 Name, 24 Address	No	2	No		Name, 24	24 Address	24	Yes
25	25 Name	25 Address		25 Name, 25 Address	No	3	No		Name, 25	25 Address	25	Yes
26	26 Name	26 Address		26 Name, 26 Address	No	2	No		Name, 26	26 Address	26	Yes
27	27 Name	27 Address		27 Name, 27 Address	No	2	No		Name, 27	27 Address	27	Yes
28	28 Name	28 Address	Mwrddyn & Maureen	28 Name, 28 Address	No	4	Yes		Name, 28	28 Address	28	Yes
29	29 Name	29 Address	George	29 Name, 29 Address	Yes	1	Yes		Name, 29	29 Address	29	Yes
30	30 Name	30 Address	Ken	30 Name, 30 Address	Yes	2	No		Name, 30	30 Address	30	Yes
31	31 Name	31 Address		31 Name, 31 Address	No	1	No		Name, 31	31 Address	31	Yes
32	32 Name	32 Address	Anne	32 Name, 32 Address	No	2	No		Name, 32	32 Address	32	Yes
33	33 Name	33 Address	Maureen & Mwrddyn	33 Name, 33 Address	No	4	Yes	Very fit	Name, 33	33 Address	33	Yes
34	34 Name	34 Address	Doris	34 Name, 34 Address	Yes	2	Yes	Wheelchair, sons Andrew & Peter.	Name, 34	34 Address	34	Yes
35	35 Name	35 Address	Rita	35 Name, 35 Address	No	1	No	Was Alexandra Rd	Name, 35	35 Address	35	Yes
36	36 Name	36 Address	Yvonne	36 Name, 36 Address	Yes	2	No		Name, 36	36 Address	36	Yes
37	37 Name	37 Address		37 Name, 37 Address	Yes	2	No		Name, 37	37 Address	37	Yes
38	38 Name	38 Address	Elaine	38 Name, 38 Address	Yes	2	Yes		Name, 38	38 Address	38	Yes
39	39 Name	39 Address	Sheila	39 Name, 39 Address	No	1	No		Name, 39	39 Address	39	Yes
40	40 Name	40 Address	Valerie	40 Name, 40 Address	No	2	No		Name, 40	40 Address	40	Yes
41	41 Name	41 Address	Diana	41 Name, 41 Address	Yes	2	Yes		Name, 41	41 Address	41	Yes
42	42 Name	42 Address	Mary	42 Name, 42 Address	No	5	No		Name, 42	42 Address	42	Yes
43	43 Name	43 Address	Pat & Hilary	43 Name, 43 Address	No	2	No		Name, 43	43 Address	43	Yes
44	44 Name	44 Address	Pam	44 Name, 44 Address	No	2	No		Name, 44	44 Address	44	Yes
45	45 Name	45 Address	Roy	45 Name, 45 Address	No	1	No		Name, 45	45 Address	45	Yes
46	46 Name	46 Address	Nancy	46 Name, 46 Address	No	1	Yes		Name, 46	46 Address	46	Yes
47	47 Name	47 Address	Babs	47 Name, 47 Address	No	2	No		Name, 47	47 Address	47	Yes
48	48 Name	48 Address	Babs	48 Name, 48 Address	No	2	No		Name, 48	48 Address	48	Yes
49	49 Name	49 Address	Sarina	49 Name, 49 Address	No	1	Yes		Name, 49	49 Address	49	Yes
50	50 Name	50 Address	Evelyn	50 Name, 50 Address	Yes	3	No	Wheelchair	Name, 50	50 Address	50	Yes
51	51 Name	51 Address		51 Name, 51 Address	Yes	1	Yes	Wheelchair	Name, 51	51 Address	51	Yes
52	52 Name	52 Address	Joan	52 Name, 52 Address	No	3	No		Name, 52	52 Address	52	Yes
53	53 Name	53 Address	Lyn	53 Name, 53 Address	No	1	No		Name, 53	53 Address	53	Yes
54	54 Name	54 Address	Clive	54 Name, 54 Address	No	2	Yes		Name, 54	54 Address	54	Yes
55	55 Name	55 Address		55 Name, 55 Address	Yes	2	No		Name, 55	55 Address	55	Yes
56	56 Name	56 Address	Dorothea	56 Name, 56 Address	No	1	No		Name, 56	56 Address	56	Yes
57	57 Name	57 Address	Enid	57 Name, 57 Address	Yes	2	No		Name, 57	57 Address	57	Yes
58	58 Name	58 Address	Marcia	58 Name, 58 Address	No	2	No		Name, 58	58 Address	58	Yes
59	59 Name	59 Address	Molly	59 Name, 59 Address	No	1	No		Name, 59	59 Address	59	Yes
60	60 Name	60 Address	Maureen & Carol	60 Name, 60 Address	Yes	1	No	Blind	Name, 60	60 Address	60	Yes
61	61 Name	61 Address	Warren	61 Name, 61 Address	No	2	No	Needs recipt	Name, 61	61 Address	61	Yes
62	62 Name	62 Address	Pamela	62 Name, 62 Address	Yes	2	No		Name, 62	62 Address	62	Yes
63	63 Name	63 Address	Margaret	63 Name, 63 Address	Yes	2	No		Name, 63	63 Address	63	Yes
64	64 Name	64 Address	Linda	64 Name, 64 Address	No	1	No		Name, 64	64 Address	64	Yes
65	65 Name	65 Address		65 Name, 65 Address	No	1	No		Name, 65	65 Address	65	Yes
66	66 Name	66 Address	Patricia & Malcolm	66 Name, 66 Address	No	1	Yes		Name, 66	66 Address	66	Yes
67	67 Name	67 Address	Patricia	67 Name, 67 Address	No	1	No		Name, 67	67 Address	67	Yes
68	68 Name	68 Address		68 Name, 68 Address	No	3	No		Name, 68	68 Address	68	Yes
69	69 Name	69 Address	Felix & Jean	69 Name, 69 Address	No	1	Yes	Jean has my number	Name, 69	69 Address	69	Yes
70	70 Name	70 Address	Hugh & Denise	70 Name, 70 Address	No	1	Yes	Both have my number	Name, 70	70 Address	70	Yes
Passengers

Cell Formulas
Range	Formula
A2:A70	A2	=ROW() &" Name"
B2:B70	B2	=ROW() & " Address"
I2:I70	I2	=IF(A1="","",TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99))&", "&LEFT(TRIM(A2),LEN(TRIM(A2))-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99)))))
J2:J70	J2	=IF($A2="","",TRIM(B2))
K2:K70	K2	=LEFT(A2,FIND(" ",A2))
D60:D70,D26:D58,D2:D24	D2	=A2&","&CHAR(10)&B2
D25,D59	D25	=A25&","& CHAR(10)&B25
M2:M8	M2	=IFERROR(INDEX($A$2:$A$200,SMALL(IF($L$2:$L$200="Yes",ROW($K$2:$K$200)),ROW(1:1))-1,1),"")
M9	M9	=IFERROR(INDEX($A$2:$A$200,SMALL(IF($L$2:$L$200="Yes",ROW($K$2:$K$200)),ROW(8:8))-1,1),"")
M10:M70	M10	=IFERROR(INDEX($A$2:$A$200,SMALL(IF($L10:$L$200="Yes",ROW($K$2:$K$200)),ROW(9:9))-1,1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Passengers!$A$1:$H$196	M2:M8, I2

Cells with Data Validation
Cell	Allow	Criteria
G1:G70	List	Yes,No
L1:L70	List	Yes,No
E1:E70	List	No,Yes

As table

 

[welshgasman]

Just enter the formula into M2 using Ctrl Shift Enter, then drag down.


Me too.

That has cracked it.

Thank you @etaf for the solution and thank you @Fluff for the additional help.
I have now removed the status from the sort and sorted as before and the column works great.

I am now going to change the ranges to dynamic named ranges as this list will grow, but now when I change the validation to ActivePassengers I will get exactly what I was asking for.
Thank you both again. Great solution!