Jun 09 2020 04:56 PM - edited Jun 09 2020 07:04 PM
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.
Appreciate the help!
Jun 09 2020 05:52 PM
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
Jun 09 2020 05:52 PM
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