Creating a drop down menu that excludes certain values

Post Deleted, Solution not compatible with the version of Excel used by OP

While it can be done, I feel that it would be easier to just use conditional formatting to highlight selections that are on leave, as modeled in rows 9:11, so that they can be quickly changed.
The CF rule is: =COUNTIFS($G3:$H3,B9) applied to $B$9:$E$11

To get drop downs per date as modeled in rows 3:5
1. Use the following formula to make lists as modeled in columns M:O

Formula:

=IFERROR(INDEX($L$3:$L$20,AGGREGATE(15,6,(ROW($L$3:$L$20)-ROW($L$2))/($L$3:$L$20<>INDEX($G$3:$G$5,COLUMNS($M3:M3)))/($L$3:$L$20<>INDEX($H$3:$H$5,COLUMNS($M3:M3))),ROWS(M$3:M3))),"")

2. Produce a named range for each date column
3. Use the following source for the drop downs: =INDIRECT("_"&SUBSTITUTE(TEXT($A3,"m/d/yyy"),"/","_"))
Let us know if you have any questions.

Hi. Thanks for the reply. That doesn't quite work as well as I would like but it is much better than before. Thank you for taking the time to explain it !

I'm not sure I've interpreted this correctly. In fact I may have duplicated JeteMc's efforts, but I don't think so.

In the attached please find these helper columns in M:O

	L	M	N	O
1
2	Intials	Shift 1	Shift 2	Shift 3
3	KC	SH	KC	KC
4	SH	DS	DS	SH
5	DS	SCP	JKY	DS
6	SCP	ALS	ALS	SCP
7	JKY	AT	AT	JKY
8	ALS	TH	TH	ALS
9	AT	OSE	OSE	OSE
10	TH	FAD	FAD	FAD
11	OSE	RB	RB	RB
12	FAD	JK	JK	JK
13	RB	KK	KK	KK
14	JK	GA	GA	GA
15	KK	WMO	WMO	WMO
16	GA	JW	JW	JW
17	WMO	SCX	SCX	SCX
18	JW	JM	JM	JM
19	SCX
20	JM

The formula in M3 is

Formula:

=IFERROR(INDEX($L$3:$L$20,AGGREGATE(15,6,(ROW($L$3:$L$20)-MIN(ROW($L$3:$L$20))+1)/NOT(ISNUMBER(MATCH($L$3:$L$20,INDEX($G$3:$H$5,MATCH(M$2,$B$2:$D$2,0),),0))),ROWS(M$3:M3))),"")

Copy across and down until you get blanks.

For the drop downs in B2 the list formula is

Formula:

=M$3:M$18

Copy B2 across to D2. Your drop downs should automatically adapt.

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #2 about thread duplication.

I am closing this thread, but you may continue here in the other thread: https://www.excelforum.com/commercia...in-values.html

Thread closed in favour of the Commercial Services version.