Hide Used Items in Drop Down List

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
76
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: 10

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,721
Office Version
  1. 365
Platform
  1. Windows
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
 

dinkss

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,721
Office Version
  1. 365
Platform
  1. Windows
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?
 

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
76
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,721
Office Version
  1. 365
Platform
  1. Windows
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)
 

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
76
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

dinkss

Board Regular
Joined
Aug 25, 2020
Messages
76
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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: 5
  • Capture2.PNG
    Capture2.PNG
    78.9 KB · Views: 4

dinkss

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

Forum statistics

Threads
1,147,562
Messages
5,741,848
Members
423,691
Latest member
Fahad987

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
Top