I am trying to make condition for data vlidation in Google Sheets but i am stuck that how it can be achieve.

Let me explain it. Data validation will be applied on Column A based on adjacent 4 columns value.

I have achieved one of them that if mentioned columns are not empty then you will be able to put a value in column A but i want that if that condition achieved then one can only put Date like DD/MM/YYYY in such format

=AND($B$3:$B$700<>"",$C$3:$C$700<>"",$D$3:$D$700<>"",$E$3:$E$700<>"")

But i need to create one more condition that if Column B is not empty then we can add only "P" into Column A.

Any help will be much appreciated.

Result Required like in columnA

CodePudding user response：

=ARRAYFORMULA(IF($B2<>"",IF(AND($B2:$E2<>""),ISDATE($A2),$A2="P")))

If B2 is not empty,

• and if B2:E2 is not empty,
  • check if A2 is a date
  • else check if A2="P"

If you want to enforce strict dd/mm/yyyy, use REGEX:

=ARRAYFORMULA(IF($B2<>"",IF(AND($B2:$E2<>""),REGEXMATCH(""&$A2,"[0-9]{2}/(0[1-9]|1[1-2])/\d{4}"),$A2="P")))

Apply to:

A2:A

Data validation formula, like conditional formatting gets applied to all the cells as if it's drag filled.