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]

are you still using excel version 2007?

 

[welshgasman]

are you still using excel version 2007?

Hi, thanks for the reply.
I am afraid so. It meets all of my needs at present.

 

[welshgasman]

For the moment, I have sorted the Passenger sheet with the current status first, before names and titles.
This at least put them at the bottom of the list.

I might well have to live with it like that. No big deal really, but if they can be removed from the list for selection all the better.

Thanks.

 

[etaf]

how about referencing a separate range for the data validation

if names in column I - which is the full list of names
then add another criteria in J
for example
Active
and the create another list for just active and use that for your data validation
you could even have an if as to which range to use - based on another cell

this formula should work OK in 2007 version
=IFERROR(INDEX($I$2:$I$22,SMALL(IF($J$2:$J$22="active",ROW($K$2:$K$22)),ROW(1:1))-1,1),"")

latest version 365 - has FILTER()

Book4
	I	J			M	N
1
2	name1	active				name1
3	name2	NON-Active				name3
4	name3	active				name5
5	name4	NON-Active				name7
6	name5	active				name9
7	name6	NON-Active				name11
8	name7	active				name13
9	name8	NON-Active				name15
10	name9	active				name17
11	name10	NON-Active				name19
12	name11	active				name21
13	name12	NON-Active
14	name13	active
15	name14	NON-Active
16	name15	active
17	name16	NON-Active
18	name17	active
19	name18	NON-Active
20	name19	active
21	name20	NON-Active
22	name21	active
Sheet1

Cell Formulas
Range	Formula
N2:N18	N2	=IFERROR(INDEX($I$2:$I$22,SMALL(IF($J$2:$J$22="active",ROW($K$2:$K$22)),ROW(1:1))-1,1),"")

 

[welshgasman]

That looks pretty neat.

What is in column K, and L which appear to be hidden?, as I need to know to translate to my sheet.?

 

[etaf]

filter function which is not available on your version
just to check the older version

here it is removed

Book7
	H	I	J	K	L	M	N
1
2		name1	active				name1
3		name2	NON-Active				name3
4		name3	active				name5
5		name4	NON-Active				name7
6		name5	active				name9
7		name6	NON-Active				name11
8		name7	active				name13
9		name8	NON-Active				name15
10		name9	active				name17
11		name10	NON-Active				name19
12		name11	active				name21
13		name12	NON-Active
14		name13	active
15		name14	NON-Active
16		name15	active
17		name16	NON-Active
18		name17	active
19		name18	NON-Active
20		name19	active
21		name20	NON-Active
22		name21	active
Sheet1

Cell Formulas
Range	Formula
N2:N18	N2	=IFERROR(INDEX($I$2:$I$22,SMALL(IF($J$2:$J$22="active",ROW($K$2:$K$22)),ROW(1:1))-1,1),"")

 

[welshgasman]

Ok, that Small has be baffled I admit.
However I am trying this, and whilst it populated the first cell, copying that to the next three did not produce anything?

I am using the word Yes in column L. ?

Cell M2 has this formula

=IFERROR(INDEX($A$2:$A$200,SMALL(IF($L$2:$L$200="Yes",ROW($K$2:$K$200)),ROW(1:1))-1,1),"")

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
4	Betty & Gina	Mrs Alcock, Einon Ct	Yes	2	Yes	Gina has my number	Alcock, Mrs	Einon Ct	Mrs	Yes
5		Mr Angel, Frampton Rd	No	2	No		Angel, Mr	Frampton Rd	Mr	Yes
6		Mrs Angel, Frampton Rd	No	2	No		Angel, Mrs	Frampton Rd	Mrs	Yes
Passengers

Cell Formulas
Range	Formula
I2:I6	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:J6	J2	=IF($A2="","",TRIM(B2))
K2:K6	K2	=LEFT(A2,FIND(" ",A2))
D2:D6	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),"")
M6	M6	=IFERROR(INDEX($A$2:$A$200,SMALL(IF($L6:$L$200="Yes",ROW($K$2:$K$200)),ROW(5:5))-1,1),"")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Passengers!$A$1:$H$196	M2, I2
Passenger	=OFFSET(Passengers!$D$2,0,0,COUNTA(Passengers!$D:$D),1)	M3

Cells with Data Validation
Cell	Allow	Criteria
G1:G6	List	Yes,No
L1:L6	List	Yes,No
E1:E6	List	No,Yes

 

[etaf]

i'm not 100% sure what you are doing with that example

I'm assuming pulling the names in column A - based on YES in column L

to create an Active List in M

But guessing

i have simplied and added what i think you mean

Small = SMALL Function
so it returns the next value based on position

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	x	y	z	a	b	c	d	e	f	g	Current	Active list
2	name1											yes	name1
3	name2											yes	name2
4	name3											no	name5
5	name4											no	name6
6	name5											yes	name7
7	name6											yes	name10
8	name7											yes	name11
9	name8											no	name12
10	name9											no	name15
11	name10											yes	name16
12	name11											yes	name17
13	name12											yes	name20
14	name13											no	name21
15	name14											no
16	name15											yes
17	name16											yes
18	name17											yes
19	name18											no
20	name19											no
21	name20											yes
22	name21											yes
Sheet1

Cell Formulas
Range	Formula
M2:M22	M2	=IFERROR(INDEX($A$2:$A$200,SMALL(IF($L$2:$L$200="Yes",ROW($K$2:$K$200)),ROW(1:1))-1,1),"")

 

[Fluff]

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