2 Data Validations Q

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I have the following Data Validation within the Custom criteria under
formula. It disallows input in cell H if J10 is blank.

=J10<>""

How could I also include a criteria that will only accept decimal
inputs between 0.01 and 40000?
 
It kind of worked Carlo, but if I type in 0.001 it will accept it, it
should only accept between 0.01 and 40000
 
Select as many cells in column H as you need, say from H1 to H10, and apply
this custom validation formula:
=AND($J$10<>"",H1>=0.01,H1<=40000)
Uncheck Negligate blank cells (if I re-translate it well, it's the checkbox
on the right side)

Regards,
Stefi

„Sean†ezt írta:
 
Further tweak to =AND(J10<>"",H10>=0.01,H10<=40000). How do I limit
the user inputing more than 2 decimal places e.g 1050.125 should not
be allowed
 
Maybe there exists a simpler solution, I found that this works:
=MOD(100*H1,100)-INT(MOD(100*H1,100))=0
embedded:

=AND(J10<>"",H10>=0.01,H10<=40000,MOD(100*H1,100)-INT(MOD(100*H1,100))=0)

Regards,
Stefi

„Sean†ezt írta:
 
That works great Stefi, thank you

I picked =AND(J10<>"",H10>=0.01,H10<=40000,MOD(100*H10,100)-
INT(MOD(100*H10,100))=0)
 
Back
Top