Data Validation - Complex Q

[seanryanie]

I am trying to create a Data Validation on entry which will only allow an entry if certain parameters are met, these parameters are based on values in certain cells. What I am try is for entries in Cell H10, allow an entry IF:-

1) If value in K10 = "C/D", then (2) below does not apply
2) A value is already entered firstly in J10
3) The value being entered in H10 has a max of 2 decimal places, eg user can enter 1000 or 1000.12, but not 1000.123

I have a part of the validation required, as detailed below, but I can figure how where to place the parameter (1) above

=AND(J10<>"",H10>=0.01,H10<=40000,LEN((IF(ISERROR(RIGHT(H10,LEN(H10)-FIND(".",H10))),0,RIGHT(H10,LEN(H10)-FIND(".",H10)))))<=2)

 

[Claus Busch]

Hi Sean,

Am Sat, 2 Feb 2013 03:15:12 -0800 (PST) schrieb (e-mail address removed):

1) If value in K10 = "C/D", then (2) below does not apply
2) A value is already entered firstly in J10
3) The value being entered in H10 has a max of 2 decimal places, eg user can enter 1000 or 1000.12, but not 1000.123

try:
=AND(OR(LEN(J10)>0,K10="C/D"),LEN(H10)<=LEN(INT(H10))+3)


Regards
Claus Busch

 

[seanryanie]

Bingo! Thanks works perfectly