CODEWITHSUNDEEP
regexvalidationgoogle-sheetsgoogle-sheets-formulags-conditional-formatting
Baraka
Baraka

Reputation: 65

Google Sheets Data Validation not rejecting invalid input

I have a sheet where I control provided services with columns filled with execution and conclusion dates. These columns have data validation for invalid dates and also, for the user not to input weekend days or holidays (which is listed on another page of the same spreadsheet). So it has to be custom formula validation.

Validation formula: 

=AND(ISDATE(K2)=TRUE;K2>=J2;WEEKDAY(K2)<>1;WEEKDAY(K2)<>7;COUNTIF(Holidays!$A:$A;"="&K2)=0)

also tried

=AND(ISDATE(K2)=TRUE;K2>=J2;WEEKDAY(K2)<>1;WEEKDAY(K2)<>7;ISNA(MATCH(K2;Holidays!$A:$A;0))=TRUE)

and also tried using INDIRECT("Holidays!$A:$A") on both options

***Column K has the data validation and Conclusion date is the input. Column J has execution dates. And row 1 has titles.

The problem: data validation input rejection seems to work fine for the first couple of hours, sometimes a full day, but after this random period of time, it stops working. Actually it does work, but with the red flag, even though "Reject input" option is still checked.

My guess is that the problem resides on the reference being in another sheet, but I don't see any other way to do this, as including the holiday list to the main sheet would pollute it and hiding columns wouldn't be as practical since users update the list constantly.

Is there a way to make it work?

P.S. Conditional Formatting used to return error even when using INDIRECT for external reference but now Google seems to have fixed it.

Hope someone can help me.

Upvotes: 2

Views: 1869

Answers (2)

Jose
Jose

Reputation: 1

I find the issue happens when the user is copying and pasting into the cell, instead of typing in, as part of a larger section of information. This breaks up the data validation into "pieces" because the copy and paste doesn't come with the data validation. I'm not sure if this is the case with you, but it may mean training users to copy and paste values only or only hard keying the information.

Upvotes: 0

player0
player0

Reputation: 1

custom formula for data validation:

=(ISDATE(A1))*  
 (WEEKDAY(A1, 2)<>6)*  
 (WEEKDAY(A1, 2)<>7)*  
 (NOT(REGEXMATCH(TO_TEXT(A1), TEXTJOIN("|", 1, INDIRECT("Sheet2!H:H")))))

0

custom formula for conditional formatting (valid green):

=(ISDATE(A1))*  
 (WEEKDAY(A1, 2)<>6)*  
 (WEEKDAY(A1, 2)<>7)*  
 (NOT(REGEXMATCH(TO_TEXT(A1), TEXTJOIN("|", 1, INDIRECT("Sheet2!H:H")))))

custom formula for conditional formatting (invalid red):

=((ISDATE(A1))*  
 (WEEKDAY(A1, 2)<>6)*  
 (WEEKDAY(A1, 2)<>7)*  
 (NOT(REGEXMATCH(TO_TEXT(A1), TEXTJOIN("|", 1, INDIRECT("Sheet2!H:H")))))=0)*
 (A1<>"")

0

spreadsheet demo

Upvotes: 1

Related Questions