Custom Data Validation Not Working

[masouder]

I am attempting to create data validation that prevents users from entering daily vacation hours that exceed their current vacation balance. Users enter vacation hours for each day of the week. If the sum of all hours entered exceeds the balance then I want data validation to kick in and prevent the last entry which pushed the sum over the balance. See the below sheet capture.

I have created a Custom data validation with the following Formula:

=SUM($E$5:$I$5)<=$C$5

In the example below total vacation hours entered equals the balance (32). If I increase any of the daily hours then the data validation works and prevents the last entry/change. However, if any of the cells in E5:I5 are blank then the data validation does not work. For example, if I delete the 1 from cell I5 (now the total hours is 31) and then change cell G5 from 7 to 9 for a total of 33 hours then the data validation does not kick in.

Does anyone have idea what I may be doing wrong or if there a workaround to this limitation?

Timesheet Data Entry Test 08232023.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3
4					M	T	W	TH	F
5		Vacation Balance	32		8	8	7	8	1
6
7
11
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E5:I5	Custom	=SUM($E$5:$I$5)<=$C$5

 

[dreid1011]

I can't seem to replicate the issue you are having. No matter which cell I delete or change to 0, the validation still triggers when I enter a value to large in any other cell. Not sure why it would behave that way. My only thought is to try this to skip blanks:

=SUMIF($E$5:$I$5,"<>")<=$C$5

 

[masouder]

Awesome! It worked, thank you. Great idea.

 

[dreid1011]

Awesome, glad I could help and thanks for the feedback.

 

[masouder]

One other note: when I replaced the $E$5:$I$5 reference with a name that references the same range then it did not work again. Very odd.

 

[dreid1011]

One other note: when I replaced the $E$5:$I$5 reference with a name that references the same range then it did not work again. Very odd.

Hmm, yes, now I see the issue. You might just have to use 0 as a placeholder in any empty cell.