Data Validation - Custom

[TheSubject]

Hi, I'm hoping someone can advice, I am getting unexpected and useless results on a data validation.
I've tried several various attempts, and they either seemingly do nothing (ie, allow any entry regardless), or they reject all entries even if they shouldn't.

I'm trying to input data into column AA, where data validation stops new entries or edits that will raise any cell between AK2:BF501 above 58.
To this end, I'd tried a direct formula in the data validation: =countif(AK2:BG501,"<58") which allows anything at all, yet =countif(AK2:BG501,">58") rejects everything, in both these cases I used test numbers below 10 and in the 90s, so in eitherformula, only one of the test numbers should have been allowed.

In a work-around attempt, I have set BG2:BG501 with formula: =COUNTIF(AK#:BF#,">58") where # is the cells row from between 2 and 501, and then in cell BG1 =sum(BG2:BG501), effectively any number but 0 in BG1 means there are cells in the range containing anything above 58, but as long as BG1=0 then I know there are no offending numbers, and based on this I used the formula =BG1=0, which when in a cell returns TRUE or FALSE depending on BG1 as I'd expect, but when I copy that into data validation custom formula bar, it doesn't prevent any entries at all, again having tried numbers above and below 58.

Long story short, where am I going wrong? I've never used custom data validation before and so far it just doesn't seem to operate as I'd expect. Googling it gives a lot of info on dropdown menus based on other formula, or some quite specific examples of isnumber formulas or istext or no duplicates, but nothing I can find relevant to what seems like quite a simple request.

Any help would be really appreciated!

 

[6StringJazzer]

You are having problems using logic. You want an expression that will be TRUE to allow data entry, and FALSE to prohibit data entry.

Here is the problem with the two expressions you tried:

=countif(AK2:BG501,">58")
will be TRUE (i.e., >0) when there is at least one value >58. Therefore it will be FALSE when there are no values over 58, and not allow entry, which is the opposite of what you want.

=countif(AK2:BG501,"<58")
will be TRUE when there is at least one value <58. There could be 1 value under 58 and hundreds over 58, and the result will be TRUE and entry is allowed. Which is not what you want either.

You want this:

=COUNTIF(AK2:BG501,">58")=0

 

[TheSubject]

Hi, thanks for your reply! However, I'm still not getting necessary results!

bookings.xlsx
	Y	Z	AA	AB	AC	AD	AE	AF	AG
2	List
3	Name	Date	Headcount	Arrival	Departure (if given)	Departure (default)	Email	Number	Table Count
4	Tom	20/04/2021	6	15:00	17:00	17:00			1
5	****	20/04/2021	6	15:00	21:00	21:00			1
6	Harry	20/04/2021	5	16:00		18:00			1
7	Lazarus	20/04/2021	5	19:00		21:00			1
8	Danny	20/04/2021	6	20:00		22:00			1
9	Ella	20/04/2021	87	16:00		18:00			1
10	Jane	17/08/2021	4	14:00	18:00	18:00			1
11	David	20/04/2021	5	18:00		20:00			1
12	Tim	15/04/2021	6	11:00		13:00			1
13	DARREN	20/04/2021	5	15:00		17:00			1
14	JAMES	20/04/2021	4	15:00		17:00			1
15	Tony	20/04/2021	5	15:00		17:00			1
16	Eliza	20/04/2021	3	15:00		17:00			1
17	james	20/04/2021	4	15:00		17:00			1
18	Jim	20/04/2021	6	15:00		17:00			1
19	sarah	20/04/2021	2	15:00		17:00			1
20	liz	20/04/2021	1	15:00		17:00			1
21	gerard	20/04/2021	3	15:00		17:00			1
22	hubert	20/04/2021	3	15:00		17:00			1
Sheet1

Cell Formulas
Range	Formula
AD4:AD22	AD4	=IF(Y4="","",IF(AC4="",AB4+(2/24),AC4))
AG4:AG22	AG4	=NOT(ISBLANK(Y4))*1

Column AA has data validation copied and pasted from your suggestion.

The range it is checking is as follows (I've not done all the rows, but it's just more dates dragged down:

bookings.xlsx
	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG
1	date	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30	18:00	18:30	19:00	19:30	20:00	20:30	21:00	21:30	4
2	15/04/2021	6	6	6	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	16/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	17/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	18/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	19/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	20/04/2021	0	0	0	0	0	0	0	0	48	48	140	140	98	98	11	11	16	16	16	16	5	5	4
8	21/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	22/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	23/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	24/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	25/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	26/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	27/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	28/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	29/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	30/04/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	01/05/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	02/05/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	03/05/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	04/05/2021	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
BG1	BG1	=SUM(BG2:BG501)
AJ2	AJ2	=TODAY()
AK2:AK21	AK2	=SUMIFS($AA:$AA,$Z:$Z,$AJ2,$AB:$AB,AK$1)
AL2:BF21	AL2	=SUMIFS($AA:$AA,$Z:$Z,$AJ2,$AB:$AB,AL$1)+AK2-SUMIFS($AA:$AA,$Z:$Z,$AJ2,$AD:$AD,AL$1)
BG2:BG21	BG2	=COUNTIF(AK2:BF2,">58")
AJ3:AJ21	AJ3	=AJ2+1

And Colums AB - AD have Data Validation based on the same formula you gave, except modified to another range BI2:CD501 and the max being 13 (the number of physical tables)

In either case, there is no error displayed and the data is allowed, bringing my headcount above 58 and my tables above 13.....

Thanks for for the help so far

 

[Habtest]

I guess it's because of absolute reference, try:

=COUNTIF($AK$2:$BG$501,">58")=0

or

=MAX($AK$2:$BG$501)<=58

 

[TheSubject]

I guess it's because of absolute reference, try:

=COUNTIF($AK$2:$BG$501,">58")=0

or

=MAX($AK$2:$BG$501)<=58

Thank you so much - the first works perfectly, but I am still getting trouble with the second Data Validation.

bookings.xlsx
	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD
1	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30	18:00	18:30	19:00	19:30	20:00	20:30	21:00	21:30
2	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	0	0	0	0	0	0	1	1	13	13	13	13	2	2	2	2	3	3	3	3	1	1
8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
BI2:CD22	BI2	=SUMIFS($AG:$AG,$Z:$Z,$AJ2,$AB:$AB,BI$1)+BH2-SUMIFS($AG:$AG,$Z:$Z,$AJ2,$AD:$AD,BI$1)

Where the data validation is:
=COUNTIF($BI$2:$CD$501,">13")=0

Again, any help would be really appreciated! As you can see in the picture it's triggering an error with a new booking at 11am, when the bar is otherwise empty.

 

[Habtest]

Thank you so much - the first works perfectly, but I am still getting trouble with the second Data Validation.

View attachment 36750

bookings.xlsx
	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD
1	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30	18:00	18:30	19:00	19:30	20:00	20:30	21:00	21:30
2	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	0	0	0	0	0	0	1	1	13	13	13	13	2	2	2	2	3	3	3	3	1	1
8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
BI2:CD22	BI2	=SUMIFS($AG:$AG,$Z:$Z,$AJ2,$AB:$AB,BI$1)+BH2-SUMIFS($AG:$AG,$Z:$Z,$AJ2,$AD:$AD,BI$1)

Where the data validation is:
=COUNTIF($BI$2:$CD$501,">13")=0

Again, any help would be really appreciated! As you can see in the picture it's triggering an error with a new booking at 11am, when the bar is otherwise empty.

I think it's working as designed. The challenge is, data validation calculates each time a cell is filled and blocks entry, even when a line item is not complete yet. One workaround is to automate all other dependent cells (as you did for most cells in column AD) so as to limit manual entry to one cell only.