Hide Used Items in Drop Down List

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
129
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    79.3 KB · Views: 26

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Assuming using 365 & not 2016, you could try this.
Set up the 'EMPLOYEES' sheet like this, Adjusting ranges to suit.

dinkss.xlsm
AB
1Employee nameDVList
2Emp 1Emp 1
3Emp 2Emp 3
4Emp 3Emp 4
5Emp 4Emp 7
6Emp 5Emp 9
7Emp 6
8Emp 7
9Emp 8
10Emp 9
11
EMPLOYEES
Cell Formulas
RangeFormula
B2:B6B2=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
DEFGHIJKLMN
3TEAMTEAMTEAM
4
5Emp 8
6Emp 2Emp 5
7Emp 6
8
9
10TEAMTEAMTEAM
11
12
13Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT
Cells with Data Validation
CellAllowCriteria
D4:D9List=EMPLOYEES!$B$2#
I4:I9List=EMPLOYEES!$B$2#
N4:N9List=EMPLOYEES!$B$2#
D11:D16List=EMPLOYEES!$B$2#
I11:I16List=EMPLOYEES!$B$2#
N11:N16List=EMPLOYEES!$B$2#


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

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

dinkss.xlsm
AB
1Employee nameDVList
2Emp 1Emp 1
3Emp 2Emp 3
4Emp 3Emp 4
5Emp 4Emp 7
6Emp 5Emp 9
7Emp 6
8Emp 7
9Emp 8
10Emp 9
11
EMPLOYEES
Cell Formulas
RangeFormula
B2:B6B2=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
DEFGHIJKLMN
3TEAMTEAMTEAM
4
5Emp 8
6Emp 2Emp 5
7Emp 6
8
9
10TEAMTEAMTEAM
11
12
13Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT
Cells with Data Validation
CellAllowCriteria
D4:D9List=EMPLOYEES!$B$2#
I4:I9List=EMPLOYEES!$B$2#
N4:N9List=EMPLOYEES!$B$2#
D11:D16List=EMPLOYEES!$B$2#
I11:I16List=EMPLOYEES!$B$2#
N11:N16List=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?
 
Upvote 0
I actually have Office 2016,
Does that mean this needs updating?
1631093664537.png


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?
 
Upvote 0
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.
 
Upvote 0
Then you can try something like this.

dinkss.xlsm
AB
1Employee$B$2:$B$4
2Emp 1Emp 3
3Emp 2Emp 4
4Emp 3Emp 9
5Emp 4 
6Emp 5 
7Emp 6 
8Emp 7 
9Emp 8 
10Emp 9 
11
EMPLOYEES
Cell Formulas
RangeFormula
B1B1=CELL("address",B2)&":"&CELL("ADDRESS",OFFSET(B1,COUNTIF(B2:B10,"?*"),0))
B2:B10B2=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
DEFGHIJKLMN
3TEAMTEAMTEAM
4Emp 8
5
6Emp 2Emp 5
7Emp 6
8
9
10TEAMTEAMTEAM
11Emp 1
12
13Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT
Cells with Data Validation
CellAllowCriteria
D4:D9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N11:N16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I11:I16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
D11:D16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N4:N9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I4:I9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
 
Upvote 0
Then you can try something like this.

dinkss.xlsm
AB
1Employee$B$2:$B$4
2Emp 1Emp 3
3Emp 2Emp 4
4Emp 3Emp 9
5Emp 4 
6Emp 5 
7Emp 6 
8Emp 7 
9Emp 8 
10Emp 9 
11
EMPLOYEES
Cell Formulas
RangeFormula
B1B1=CELL("address",B2)&":"&CELL("ADDRESS",OFFSET(B1,COUNTIF(B2:B10,"?*"),0))
B2:B10B2=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
DEFGHIJKLMN
3TEAMTEAMTEAM
4Emp 8
5
6Emp 2Emp 5
7Emp 6
8
9
10TEAMTEAMTEAM
11Emp 1
12
13Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT
Cells with Data Validation
CellAllowCriteria
D4:D9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N11:N16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I11:I16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
D11:D16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N4:N9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I4:I9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I will try this,

thanks very much
 
Upvote 0
Then you can try something like this.

dinkss.xlsm
AB
1Employee$B$2:$B$4
2Emp 1Emp 3
3Emp 2Emp 4
4Emp 3Emp 9
5Emp 4 
6Emp 5 
7Emp 6 
8Emp 7 
9Emp 8 
10Emp 9 
11
EMPLOYEES
Cell Formulas
RangeFormula
B1B1=CELL("address",B2)&":"&CELL("ADDRESS",OFFSET(B1,COUNTIF(B2:B10,"?*"),0))
B2:B10B2=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
DEFGHIJKLMN
3TEAMTEAMTEAM
4Emp 8
5
6Emp 2Emp 5
7Emp 6
8
9
10TEAMTEAMTEAM
11Emp 1
12
13Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT
Cells with Data Validation
CellAllowCriteria
D4:D9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N11:N16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I11:I16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
D11:D16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N4:N9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I4:I9List=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?
 

Attachments

  • Capture.PNG
    Capture.PNG
    14.6 KB · Views: 14
  • Capture2.PNG
    Capture2.PNG
    78.9 KB · Views: 13
Upvote 0
Then you can try something like this.

dinkss.xlsm
AB
1Employee$B$2:$B$4
2Emp 1Emp 3
3Emp 2Emp 4
4Emp 3Emp 9
5Emp 4 
6Emp 5 
7Emp 6 
8Emp 7 
9Emp 8 
10Emp 9 
11
EMPLOYEES
Cell Formulas
RangeFormula
B1B1=CELL("address",B2)&":"&CELL("ADDRESS",OFFSET(B1,COUNTIF(B2:B10,"?*"),0))
B2:B10B2=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
DEFGHIJKLMN
3TEAMTEAMTEAM
4Emp 8
5
6Emp 2Emp 5
7Emp 6
8
9
10TEAMTEAMTEAM
11Emp 1
12
13Emp 7
14
15
16
NIGHT SHIFT TEAM MANAGEMENT
Cells with Data Validation
CellAllowCriteria
D4:D9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N11:N16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I11:I16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
D11:D16List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
N4:N9List=INDIRECT("EMPLOYEES!"&EMPLOYEES!$B$1)
I4:I9List=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
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top