Search code examples
datevalidationif-statementgoogle-sheetsgoogle-sheets-formula

Data validation to Put values in column based on condition


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

columnA columB columC columD columE
25/11/2022 256 MNO 256 POP
condition not met JKL POP
condition not met POP MNO MNO
25/11/2022 JKL POP 256 JKL
condition not met 257 MNO 257
condition not met JKL POP
P POP
condition not met JKL 257 JKL
P 258

Link to Sheet


Solution

  • try:

    =(ISDATE(A2)*(B2<>"")*(C2<>"")*(D2<>"")*(E2<>""))+((A2="P")*(B2<>""))
    

    enter image description here