Jul 04 2019 07:03 AM
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
Jul 04 2019 07:11 AM
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...