2 Data Validations Q

[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?

 

[carlo]

Try this

=AND(J10<>"",J10>0.01,J10<=40000)

hth

Carlo

 

[Sean]

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

 

[Stefi]

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:

 

[Sean]

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

 

[Stefi]

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:

 

[Sean]

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)

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi


„Sean†ezt írta: