Hide Used Items in Drop Down List

[dinkss]

Hi there,

I'm trying to get drop down lists in different cells with ability to hide used names (names of employees that were picked already).
I want to have drop down lists in every column with name TEAM - once name is selected I want it to dissapear from other drop down lists. Is it possible?

Names of employees are in new sheet called EMPLOYEES.

Please find attached workbook. Is there anyone that could help me with this?

Thanks in advance.

 

[Peter_SSs]

Assuming using 365 & not 2016, you could try this.
Set up the 'EMPLOYEES' sheet like this, Adjusting ranges to suit.

dinkss.xlsm
	A	B
1	Employee name	DVList
2	Emp 1	Emp 1
3	Emp 2	Emp 3
4	Emp 3	Emp 4
5	Emp 4	Emp 7
6	Emp 5	Emp 9
7	Emp 6
8	Emp 7
9	Emp 8
10	Emp 9
11
EMPLOYEES

Cell Formulas
Range	Formula
B2:B6	B2	=FILTER(A2:A10,ISERROR(SEARCH("|"&A2:A10&"|","|"&TEXTJOIN("|",1,FILTER('NIGHT SHIFT TEAM MANAGEMENT'!A4:N40,'NIGHT SHIFT TEAM MANAGEMENT'!A3:N3="TEAM",""))&"|")))
Dynamic array formulas.

Then in the Shift sheet set up the Data validation like this.

dinkss.xlsm
	D	E	F	G	H	I	J	K	L	M	N
3	TEAM					TEAM					TEAM
4
5						Emp 8
6	Emp 2					Emp 5
7	Emp 6
8
9
10	TEAM					TEAM					TEAM
11
12
13						Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT

Cells with Data Validation
Cell	Allow	Criteria
D4:D9	List	=EMPLOYEES!$B$2#
I4:I9	List	=EMPLOYEES!$B$2#
N4:N9	List	=EMPLOYEES!$B$2#
D11:D16	List	=EMPLOYEES!$B$2#
I11:I16	List	=EMPLOYEES!$B$2#
N11:N16	List	=EMPLOYEES!$B$2#

The result is that when a user goes to pick another name they see ..

 

[dinkss]

Assuming using 365 & not 2016, you could try this.
Set up the 'EMPLOYEES' sheet like this, Adjusting ranges to suit.

dinkss.xlsm
	A	B
1	Employee name	DVList
2	Emp 1	Emp 1
3	Emp 2	Emp 3
4	Emp 3	Emp 4
5	Emp 4	Emp 7
6	Emp 5	Emp 9
7	Emp 6
8	Emp 7
9	Emp 8
10	Emp 9
11
EMPLOYEES

Cell Formulas
Range	Formula
B2:B6	B2	=FILTER(A2:A10,ISERROR(SEARCH("|"&A2:A10&"|","|"&TEXTJOIN("|",1,FILTER('NIGHT SHIFT TEAM MANAGEMENT'!A4:N40,'NIGHT SHIFT TEAM MANAGEMENT'!A3:N3="TEAM",""))&"|")))
Dynamic array formulas.

Then in the Shift sheet set up the Data validation like this.

dinkss.xlsm
	D	E	F	G	H	I	J	K	L	M	N
3	TEAM					TEAM					TEAM
4
5						Emp 8
6	Emp 2					Emp 5
7	Emp 6
8
9
10	TEAM					TEAM					TEAM
11
12
13						Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT

Cells with Data Validation
Cell	Allow	Criteria
D4:D9	List	=EMPLOYEES!$B$2#
I4:I9	List	=EMPLOYEES!$B$2#
N4:N9	List	=EMPLOYEES!$B$2#
D11:D16	List	=EMPLOYEES!$B$2#
I11:I16	List	=EMPLOYEES!$B$2#
N11:N16	List	=EMPLOYEES!$B$2#

The result is that when a user goes to pick another name they see ..

View attachment 46476

I actually have Office 2016, any chance to get this working in my Office?

 

[Peter_SSs]

I actually have Office 2016,

Does that mean this needs updating?

The task is considerably more difficult with 365.
Can you confirm that there are only 3 'Team' columns on the 'NIGHT SHIFT TEAM MANAGEMENT' sheet?

 

[dinkss]

Does that mean this needs updating?
View attachment 46491

The task is considerably more difficult with 365.
Can you confirm that there are only 3 'Team' columns on the 'NIGHT SHIFT TEAM MANAGEMENT' sheet?

I have Office 2016 Professional.

I can confirm there are only 3 team columns on the Night shift management team.

 

[Peter_SSs]

Then you can try something like this.

dinkss.xlsm
	A	B
1	Employee	$B$2:$B$4
2	Emp 1	Emp 3
3	Emp 2	Emp 4
4	Emp 3	Emp 9
5	Emp 4
6	Emp 5
7	Emp 6
8	Emp 7
9	Emp 8
10	Emp 9
11
EMPLOYEES

Cell Formulas
Range	Formula
B1	B1	=CELL("address",B2)&":"&CELL("ADDRESS",OFFSET(B1,COUNTIF(B2:B10,"?*"),0))
B2:B10	B2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$10)/(ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!D$4:D$30,0))+ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!I$4:I$30,0))+ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!N$4:N$30,0))=3),ROWS(B$2:B2))),"")

dinkss.xlsm
	D	E	F	G	H	I	J	K	L	M	N
3	TEAM					TEAM					TEAM
4						Emp 8
5
6	Emp 2					Emp 5
7	Emp 6
8
9
10	TEAM					TEAM					TEAM
11											Emp 1
12
13						Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT

Cells with Data Validation
Cell	Allow	Criteria
D4:D9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N11:N16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I11:I16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
D11:D16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N4:N9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I4:I9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)

 

[dinkss]

Then you can try something like this.

dinkss.xlsm
	A	B
1	Employee	$B$2:$B$4
2	Emp 1	Emp 3
3	Emp 2	Emp 4
4	Emp 3	Emp 9
5	Emp 4
6	Emp 5
7	Emp 6
8	Emp 7
9	Emp 8
10	Emp 9
11
EMPLOYEES

Cell Formulas
Range	Formula
B1	B1	=CELL("address",B2)&":"&CELL("ADDRESS",OFFSET(B1,COUNTIF(B2:B10,"?*"),0))
B2:B10	B2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$10)/(ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!D$4:D$30,0))+ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!I$4:I$30,0))+ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!N$4:N$30,0))=3),ROWS(B$2:B2))),"")

dinkss.xlsm
	D	E	F	G	H	I	J	K	L	M	N
3	TEAM					TEAM					TEAM
4						Emp 8
5
6	Emp 2					Emp 5
7	Emp 6
8
9
10	TEAM					TEAM					TEAM
11											Emp 1
12
13						Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT

Cells with Data Validation
Cell	Allow	Criteria
D4:D9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N11:N16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I11:I16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
D11:D16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N4:N9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I4:I9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)

I will try this,

thanks very much

 

[dinkss]

Then you can try something like this.

dinkss.xlsm
	A	B
1	Employee	$B$2:$B$4
2	Emp 1	Emp 3
3	Emp 2	Emp 4
4	Emp 3	Emp 9
5	Emp 4
6	Emp 5
7	Emp 6
8	Emp 7
9	Emp 8
10	Emp 9
11
EMPLOYEES

Cell Formulas
Range	Formula
B1	B1	=CELL("address",B2)&":"&CELL("ADDRESS",OFFSET(B1,COUNTIF(B2:B10,"?*"),0))
B2:B10	B2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$10)/(ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!D$4:D$30,0))+ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!I$4:I$30,0))+ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!N$4:N$30,0))=3),ROWS(B$2:B2))),"")

dinkss.xlsm
	D	E	F	G	H	I	J	K	L	M	N
3	TEAM					TEAM					TEAM
4						Emp 8
5
6	Emp 2					Emp 5
7	Emp 6
8
9
10	TEAM					TEAM					TEAM
11											Emp 1
12
13						Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT

Cells with Data Validation
Cell	Allow	Criteria
D4:D9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N11:N16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I11:I16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
D11:D16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N4:N9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I4:I9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)

Just a question because I don't uderstand - do I need to have something filled in B column?
on EMPLOYEE sheet I only have this - attchaed photo.

Also I only need dropdown list and hide used names in sections marked on yellow.

Is that good enough explanation?

 

[dinkss]

Then you can try something like this.

dinkss.xlsm
	A	B
1	Employee	$B$2:$B$4
2	Emp 1	Emp 3
3	Emp 2	Emp 4
4	Emp 3	Emp 9
5	Emp 4
6	Emp 5
7	Emp 6
8	Emp 7
9	Emp 8
10	Emp 9
11
EMPLOYEES

Cell Formulas
Range	Formula
B1	B1	=CELL("address",B2)&":"&CELL("ADDRESS",OFFSET(B1,COUNTIF(B2:B10,"?*"),0))
B2:B10	B2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$10)/(ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!D$4:D$30,0))+ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!I$4:I$30,0))+ISNA(MATCH(A$2:A$10,'NIGHT SHIFT TEAM MANAGEMENT'!N$4:N$30,0))=3),ROWS(B$2:B2))),"")

dinkss.xlsm
	D	E	F	G	H	I	J	K	L	M	N
3	TEAM					TEAM					TEAM
4						Emp 8
5
6	Emp 2					Emp 5
7	Emp 6
8
9
10	TEAM					TEAM					TEAM
11											Emp 1
12
13						Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT

Cells with Data Validation
Cell	Allow	Criteria
D4:D9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N11:N16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I11:I16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
D11:D16	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N4:N9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I4:I9	List	=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)

All worked out perfectly, thank you so much, I only changed the desired locations and it covers everything.
You are fantastic.

Cheers

 

[Peter_SSs]

All worked out perfectly, thank you so much,

You're welcome. Thanks for the follow-up.