Data validation on holidays

[Zubair]

Hi Experts,

1. Selection is available for users to put Number of holidays in a week in cell B1 through data validation from C1:C2
2. User can also select Holiday 1 from drop down list from D1:D7
3. User can also select Holiday 2 from drop down same list

How to stop user

1. Wrongly repeating same day twice for example Holiday 1 = Sat & Holiday 2 = Sat
2. If B1 = 1 user can not select Holiday 2

Please help

No of holidays in a week	1​	1​	Sun
Holiday 1	Sat	2​	Mon
Holiday 2			Tue
			Wed
			Thu
			Fri
			Sat

 

[jasonb75]

Something like this maybe?
If you want more than 2 then it will be easier with a different method. Note that this will not prevent the user from changing Holiday1 after selecting Holiday2, I've kept it simple until we know if there are going to be additional criteria to consider.

Book1
	A	B	C	D	E
1	No of holidays in a week	2	1	Sun	Sun
2	Holiday 1	Sat	2	Mon	Mon
3	Holiday 2			Tue	Tue
4				Wed	Wed
5				Thu	Thu
6				Fri	Fri
7				Sat
Sheet2

Cell Formulas
Range	Formula
E1:E6	E1	=IF($B$2=D1,D2,D1)

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=IF($B$1=2,$E$1:$E$6,$E$7)

 

[Zubair]

Thanks Jasonb75,

Your given formula is working for Holiday 2.

For control purpose I need to restrict people for selecting duplicate days for holidays and if number of holidays in a week is 1, only option to select 1 day should available, if 2 than must select 2 holidays, hope more mind to put in this.

 

[jasonb75]

Sorry, I misread it and thought that you already had validation in the first one.

Book1
	A	B	C	D	E
1	No of holidays in a week	2		Sun	Sun
2	Holiday 1	Wed		Mon	Tue
3	Holiday 2	Mon		Tue	Thu
4	Holiday 3			Wed	Fri
5				Thu	Sat
6				Fri
7				Sat
Sheet1

Cell Formulas
Range	Formula
E1:E7	E1	=IFERROR(INDEX($D$1:$D$7,AGGREGATE(15,6,ROW($D$1:$D$7)/ISNA(MATCH($D$1:$D$7,$B$2:$B$3,0)),ROWS(E$1:E1))),"")

Cells with Data Validation
Cell	Allow	Criteria
B2:B4	List	=IF(ROWS(B$2:B2)>$B$1,$E$8,$E$1:$E$7)

 

[Zubair]

Sorry, I misread it and thought that you already had validation in the first one.

Book1
	A	B	C	D	E
1	No of holidays in a week	2		Sun	Sun
2	Holiday 1	Wed		Mon	Tue
3	Holiday 2	Mon		Tue	Thu
4	Holiday 3			Wed	Fri
5				Thu	Sat
6				Fri
7				Sat
Sheet1

Cell Formulas
Range	Formula
E1:E7	E1	=IFERROR(INDEX($D$1:$D$7,AGGREGATE(15,6,ROW($D$1:$D$7)/ISNA(MATCH($D$1:$D$7,$B$2:$B$3,0)),ROWS(E$1:E1))),"")

Cells with Data Validation
Cell	Allow	Criteria
B2:B4	List	=IF(ROWS(B$2:B2)>$B$1,$E$8,$E$1:$E$7)

Marvellous Jasonb75

its perfectly matches my requirement. Many thanks.