Home

Custom Data Validation

%3CLINGO-SUB%20id%3D%22lingo-sub-738359%22%20slang%3D%22en-US%22%3ECustom%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738359%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20quite%20an%20experienced%20Excel%20programmer%2C%20but%20I'm%20facing%20a%20problem%20with%20data%20validation%20I%20cannot%20understand.%20I%20got%20a%20custom%20data%20validation%20for%20cell%20'K8'.%20The%20following%20formula%20works%20perfectly%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EK5%3DUND(--RECHTS(TEXT(K8%3B%220000%22)%3B2)%26lt%3B60%3BRECHTS(TEXT(K8%3B%220000%22)%3B2)%2F15%3DGANZZAHL(RECHTS(TEXT(K8%3B%220000%22)%3B2)%2F15)%3B%24O8%26lt%3B%3DWENN(Dienstverh%C3%A4ltnis%3D%22Lehrling%22%3BZEIT(8%3B0%3B0)%3BZEIT(10%3B0%3B0))%3BK8%3DGANZZAHL(K8)%3BK8%26gt%3B%3D0%3BK8%26lt%3B%3D2400)%3C%2FPRE%3E%3CP%3EThe%20following%20doesn't%20work%3A%3C%2FP%3E%3CPRE%3EK5%3DUND(--RECHTS(TEXT(K8%3B%220000%22)%3B2)%26lt%3B60%3BRECHTS(TEXT(K8%3B%220000%22)%3B2)%2F15%3DGANZZAHL(RECHTS(TEXT(K8%3B%220000%22)%3B2)%2F15)%3B%24O8%26lt%3B%3DWENN(Dienstverh%C3%A4ltnis%3D%22Lehrling%22%3BZEIT(8%3B0%3B0)%3BZEIT(10%3B0%3B0))%3BODER(K8%3DGANZZAHL(K8)%3BH8%26lt%3B%26gt%3B%22%22)%3BK8%26gt%3B%3D0%3BK8%26lt%3B%3D2400)%26nbsp%3B%3C%2FPRE%3E%3CP%3EThe%20only%20difference%20in%20these%202%20formulas%20is%20at%20the%20end%3C%2FP%3E%3CPRE%3E%3BK8%3DGANZZAHL(K8)%3B%3CBR%20%2F%3E%3BODER(K8%3DGANZZAHL(K8)%3BH8%26lt%3B%26gt%3B%22%22)%3B%3C%2FPRE%3E%3CP%3E%26nbsp%3B%20GANZZAHL%20is%20german%20for%20INT%20and%20ODER%20is%20german%20for%20OR.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20put%20these%202%20formulas%20in%20separate%20cells%20they%20show%20correctly%20TRUE%20and%20FALSE.%20E.g.%20if%20I%20write%20in%20K5%20'805'%20it%20should%20not%20work.%20With%20the%20first%20formula%20the%20data%20validation%20fires%2C%20in%20the%20second%20formula%20not.%20Putting%20the%202%20formulas%20in%20separate%20cells%20both%20show%20FALSE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20character%20limit%20or%20something%3F%20The%20data%20validation%20dialogue%20doesn't%20show%20any%20error...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20very%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3EAndi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-738359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edata%20validation%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738379%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738379%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371157%22%20target%3D%22_blank%22%3E%40smart-IT-up%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20as%20a%20workaround%20I%20put%20the%20validation%20formula%20in%20a%20hidden%20column%20and%20referenced%20in%20the%20data%20validation%20to%20it.%20That%20works%2C%20allthough%20it's%20not%20the%20clean%20way...%3C%2FP%3E%3C%2FLINGO-BODY%3E
smart-IT-up
New 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...