SOLVED

Data Validation custom formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1452446%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20custom%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1452446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F469616%22%20target%3D%22_blank%22%3E%40Dromerolovo95%3C%2FA%3EIt%20looks%20like%20you%20have%201.0%20instead%20of%201%2C0%20which%20changes%20your%20offset%20from%20shifting%200%20columns%20and%20increasing%20your%20row%20range%20to%20shifting%20your%20columns%20off%20the%20page.%3C%2FP%3E%3CP%3Ealso%20a%20simpler%20formula%20could%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(C%3AC%2CC10%2CD%3AD%2CD10)%3D1%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1452370%22%20slang%3D%22en-US%22%3EData%20Validation%20custom%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1452370%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20apply%20this%20formula%20to%20data%20validation%20but%20it's%20not%20working.%20The%20formula%20works%20fine%20in%20a%20normal%20cell%20though%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLARGE((C10%3DOFFSET(C10%2C(ROW(C10)-ROW(%24C%243))*-1%2B1%2C0%2CROW(C10)-ROW(%24C%243)-1))*(D10%3DOFFSET(D10%2C(ROW(D10)-ROW(%24C%243))*-1%2B1%2C0%2CROW(D10)-ROW(%24C%243)-1))%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24C%243%20is%20the%20address%20for%20the%20table%20heading%3C%2FP%3E%3CP%3EC10%20is%20the%20address%20for%20a%20name%20in%20%22Nombres%3A%3C%2FP%3E%3CP%3ED10%20is%20the%20address%20for%20a%20date%20in%20%22Fecha%22%20(The%20table%20is%20below)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20basically%20what%20I'm%20trying%20to%20do%20is%20to%20validate%20(%3CSTRONG%3Ethe%20data%20in%26nbsp%3B%20%22Fecha%22%3C%2FSTRONG%3E)%20if%20there%20has%20been%20the%20same%20Name%20and%20Date%20before%20(%20%22Nombres%22%20stands%20for%20names%20and%20%22Fecha%22%20stands%20for%20date)%20to%20restrict%20the%20same%20input.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20retrieves%201%20if%20there%20has%20been%20the%20same%20registry%20before%2C%20considering%20Names%20and%20Dates.%3C%2FP%3E%3CP%3EYou%20can%20register%20the%20same%20name%2C%20but%20shouldn't%20register%20the%20same%20name%20and%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.JPG%22%20style%3D%22width%3A%20246px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197752i57FBADA14FF5B352%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Capture.JPG%22%20alt%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20the%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1452370%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

I'm trying to apply this formula to data validation but it's not working. The formula works fine in a normal cell though 

 

=LARGE((C10=OFFSET(C10,(ROW(C10)-ROW($C$3))*-1+1,0,ROW(C10)-ROW($C$3)-1))*(D10=OFFSET(D10,(ROW(D10)-ROW($C$3))*-1+1,0,ROW(D10)-ROW($C$3)-1)),1)

 

$C$3 is the address for the table heading

C10 is the address for a name in "Nombres:

D10 is the address for a date in "Fecha" (The table is below)

 

So basically what I'm trying to do is to validate (the data in  "Fecha") if there has been the same Name and Date before ( "Nombres" stands for names and "Fecha" stands for date) to restrict the same input. 

 

This formula retrieves 1 if there has been the same registry before, considering Names and Dates.

You can register the same name, but shouldn't register the same name and date. 

 

 

Capture.JPG

 

Appreciate the help!

 

1 Reply
Best Response confirmed by Dromerolovo95 (Occasional Contributor)
Solution

@Dromerolovo95It looks like you have 1.0 instead of 1,0 which changes your offset from shifting 0 columns and increasing your row range to shifting your columns off the page.

also a simpler formula could be:

=COUNTIFS(C:C,C10,D:D,D10)=1