Data Validation - Custom

TheSubject

New Member
Joined
Feb 16, 2016
Messages
18
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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
927
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=COUNTIF(AK2:BG501,">58")=0
 

TheSubject

New Member
Joined
Feb 16, 2016
Messages
18
Hi, thanks for your reply! However, I'm still not getting necessary results!

bookings.xlsx
YZAAABACADAEAFAG
2List
3NameDateHeadcountArrivalDeparture (if given)Departure (default)EmailNumberTable Count
4Tom20/04/2021615:0017:0017:001
5****20/04/2021615:0021:0021:001
6Harry20/04/2021516:0018:001
7Lazarus20/04/2021519:0021:001
8Danny20/04/2021620:0022:001
9Ella20/04/20218716:0018:001
10Jane17/08/2021414:0018:0018:001
11David20/04/2021518:0020:001
12Tim15/04/2021611:0013:001
13DARREN20/04/2021515:0017:001
14JAMES20/04/2021415:0017:001
15Tony20/04/2021515:0017:001
16Eliza20/04/2021315:0017:001
17james20/04/2021415:0017:001
18Jim20/04/2021615:0017:001
19sarah20/04/2021215:0017:001
20liz20/04/2021115:0017:001
21gerard20/04/2021315:0017:001
22hubert20/04/2021315:0017:001
Sheet1
Cell Formulas
RangeFormula
AD4:AD22AD4=IF(Y4="","",IF(AC4="",AB4+(2/24),AC4))
AG4:AG22AG4=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:

Cell Formulas
RangeFormula
BG1BG1=SUM(BG2:BG501)
AJ2AJ2=TODAY()
AK2:AK21AK2=SUMIFS($AA:$AA,$Z:$Z,$AJ2,$AB:$AB,AK$1)
AL2:BF21AL2=SUMIFS($AA:$AA,$Z:$Z,$AJ2,$AB:$AB,AL$1)+AK2-SUMIFS($AA:$AA,$Z:$Z,$AJ2,$AD:$AD,AL$1)
BG2:BG21BG2=COUNTIF(AK2:BF2,">58")
AJ3:AJ21AJ3=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

Board Regular
Joined
Jul 30, 2020
Messages
197
Office Version
  1. 365
Platform
  1. Windows
I guess it's because of absolute reference, try:
Excel Formula:
=COUNTIF($AK$2:$BG$501,">58")=0
or
Excel Formula:
=MAX($AK$2:$BG$501)<=58
 
Last edited:
Solution

TheSubject

New Member
Joined
Feb 16, 2016
Messages
18
I guess it's because of absolute reference, try:
Excel Formula:
=COUNTIF($AK$2:$BG$501,">58")=0
or
Excel Formula:
=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.

1618483016691.png


Cell Formulas
RangeFormula
BI2:CD22BI2=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

Board Regular
Joined
Jul 30, 2020
Messages
197
Office Version
  1. 365
Platform
  1. Windows
Thank you so much - the first works perfectly, but I am still getting trouble with the second Data Validation.

View attachment 36750

Cell Formulas
RangeFormula
BI2:CD22BI2=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.
 

Forum statistics

Threads
1,148,154
Messages
5,745,097
Members
423,922
Latest member
Taconumber

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