Custom Data Validation

Copper Contributor

Hi,

I'm quite an experienced Excel programmer, but I'm facing a problem with data validation I cannot understand. I got a custom data validation for cell 'K8'. The following formula works perfectly:

 

K5=UND(--RECHTS(TEXT(K8;"0000");2)<60;RECHTS(TEXT(K8;"0000");2)/15=GANZZAHL(RECHTS(TEXT(K8;"0000");2)/15);$O8<=WENN(Dienstverhältnis="Lehrling";ZEIT(8;0;0);ZEIT(10;0;0));K8=GANZZAHL(K8);K8>=0;K8<=2400)

The following doesn't work:

K5=UND(--RECHTS(TEXT(K8;"0000");2)<60;RECHTS(TEXT(K8;"0000");2)/15=GANZZAHL(RECHTS(TEXT(K8;"0000");2)/15);$O8<=WENN(Dienstverhältnis="Lehrling";ZEIT(8;0;0);ZEIT(10;0;0));ODER(K8=GANZZAHL(K8);H8<>"");K8>=0;K8<=2400) 

The only difference in these 2 formulas is at the end

;K8=GANZZAHL(K8);
;ODER(K8=GANZZAHL(K8);H8<>"");

  GANZZAHL is german for INT and ODER is german for OR.

 

When I put these 2 formulas in separate cells they show correctly TRUE and FALSE. E.g. if I write in K5 '805' it should not work. With the first formula the data validation fires, in the second formula not. Putting the 2 formulas in separate cells both show FALSE.

 

Is there a character limit or something? The data validation dialogue doesn't show any error...

 

Any help is very much appreciated!

 

Best regards

Andi

1 Reply

@smart-IT-up 

Well, as a workaround I put the validation formula in a hidden column and referenced in the data validation to it. That works, allthough it's not the clean way...