Data Validation Warning message based on multiple criteria

[Browneh89]

Hello,

I am attempting to created a formula within data validation to produce a information message if two adjacent cells meet the correct criteria..

in a standard IF formula it would look like this

=IF(AND(LEN(B4)>8,J4="Done"),"WARNING","NO WARNING")

but I can't get this to trigger an information message box with custom criteria

I'm using the below in data validation

=AND(LEN(B4)>9,J4="Done")

Can anyone help me with this? been banging my head against a wall for a while now!

 

[smozgur]

Assuming you are setting the same data validation for B4 and J4 cells.
Try this:

=NOT(AND(LEN($B$4)>9,$J$4="Done"))

Two points:

1. You need NOT() function because the validation passes when the formula returns TRUE. According to your sample formula, you want to see the WARNING when the result is TRUE. So you need to use NOT() to make it opposite for the data validation.
2. If you set the data validation at the same time by selecting two cells, then I believe you also need to use absolute references, $B$4 and $J$4.

 

[Zot]

Assuming you are setting the same data validation for B4 and J4 cells.
Try this:

=NOT(AND(LEN($B$4)>9,$J$4="Done"))

Two points:

1. You need NOT() function because the validation passes when the formula returns TRUE. According to your sample formula, you want to see the WARNING when the result is TRUE. So you need to use NOT() to make it opposite for the data validation.
2. If you set the data validation at the same time by selecting two cells, then I believe you also need to use absolute references, $B$4 and $J$4.

Based on this formula =IF(AND(LEN(B4)>8,J4="Done"),"WARNING","NO WARNING"), even if you put NOT it will not meet the condition.
The formula will give WARNING if both condition met but by putting NOT, the supposed to be WARNING will become NO WARNING.

Need to modify to this =OR(LEN(B4)<9,J4<>"Done") in Validation formula only to work, surprisingly ?

 

[Browneh89]

Based on this formula =IF(AND(LEN(B4)>8,J4="Done"),"WARNING","NO WARNING"), even if you put NOT it will not meet the condition.
The formula will give WARNING if both condition met but by putting NOT, the supposed to be WARNING will become NO WARNING.

Need to modify to this =OR(LEN(B4)<9,J4<>"Done") in Validation formula only to work, surprisingly ?

Thank you very much that formula worked perfectly for what I needed!

You have no idea how much I was racking my brain with this one.. I didn't think to write it using an OR or in this format at all.

 

[Zot]

Thank you very much that formula worked perfectly for what I needed!

You have no idea how much I was racking my brain with this one.. I didn't think to write it using an OR or in this format at all.

It's the first time I use formula and almost banged my head too ?

 

[smozgur]

Thank you very much that formula worked perfectly for what I needed!

You have no idea how much I was racking my brain with this one.. I didn't think to write it using an OR or in this format at all.

Glad to hear you got the solution, @Browneh89. That would be great if you could mark @Zot's post as the solution, so the future visitors would know this question has an answer.

Based on this formula =IF(AND(LEN(B4)>8,J4="Done"),"WARNING","NO WARNING"), even if you put NOT it will not meet the condition.
The formula will give WARNING if both condition met but by putting NOT, the supposed to be WARNING will become NO WARNING.

Actually, the two formulas are exactly the same and return the following results with the corresponding boolean variables as they are supposed to do.

OR vs NOT/AND
	A	B	C	D
1	OR VERSION	OR RESULT	NOT/AND VERSION	NOT/AND RESULT
2	OR(TRUE, TRUE)	TRUE	NOT(AND(FALSE, FALSE))	TRUE
3	OR(TRUE, FALSE)	TRUE	NOT(AND(FALSE, TRUE))	TRUE
4	OR(FALSE, TRUE)	TRUE	NOT(AND(TRUE, FALSE))	TRUE
5	OR(FALSE, FALSE)	FALSE	NOT(AND(TRUE, TRUE))	FALSE
Sheet1

I prefer your formula since it is shorter. What makes things a bit complicated in this particular question is the nature of how Data Validation works.