Data Validation issue

[andrewb90]

I am using a data validation to create a popup if K3 does not have the number 1-12 in it. Regardless of what seems to be in K3 the popup message always appears. K3 is auto filled in from a different sheet, and the cell will be protected and hidden normally. Am I doing something wrong here?


=IF(K3="",FALSE,IF(OR(K3<1,K3>12),FALSE,TRUE))

Thanks,

Andrew

 

[Jonmo1]

K3 is auto filled in from a different sheet

Do you mean that K3 is a formula referring to another sheet?
Data validation only applies when the cell is manually changed, not via formula.

You would need to apply the data validation on that formula's precedent cell.
Say if K3's formula is
=Sheet2!A1

then you would put the validation in Sheet2 A1, and adjust the cell references accordingly.

 

[Jonmo1]

Also, formula's in data validation are similar to formulas in conditional formatting.
You do not need to structure them with an IF, because it's already an if by default.
The entry in the cell is only allowed if the formula results in TRUE.
So You only need a formula that returns TRUE or FALSE..


Instead of
=IF(K3="",FALSE,IF(OR(K3<1,K3>12),FALSE,TRUE))

Try
=AND(K3>=1,K3<=12)

This is also easier, because we don't have to test for blank.
Clearly a blank is NOT between 1 and 12.


Also, even easier...
Instead of Custom - formula
use Whole Number - Between


Hope that all helps.

 

[andrewb90]

I changed my formula to include the original cell, but the popup still appears.

Also, I want the popup message to appear if the cell has been left blank as well, that's my issue. That cell along with a few others must be filled in for other formulas on other sheets to work, but if that cell is filled in incorrectly or not filled in at all, a popup needs to appear on my selected cells. That cell once it is filled in, will not change again, and it won't be clicked on again either.

=IF(Welcome!T100="",FALSE,IF(OR(Welcome!T100<1,Welcome!T100>12),FALSE,TRUE))

 

[Jonmo1]

I think you missunderstood me...

The Data validation needs to go on the Welcome Sheet, Cell T100
And the formula would b
=AND(T100>=1,T100<=12)
UNcheck "Ignore blanks"

 

[andrewb90]

That would mean the popup would appear after clicking on T100 (assuming it's incorrect data) right?

 

[Jonmo1]

That would mean the popup would appear after clicking on T100 (assuming it's incorrect data) right?

Yes, after entering an incorrect value in T100 on the Welcome sheet.

 

[andrewb90]

That doesn't entirely help, because there is a possibility that the cell might get skipped and not have anything typed in it at all, so I need a popup on the other sheet where they will be typing to appear if the cell has been missed.