I'm trying to create data validation to prevent users from entering duplicate
rows in Excel 2000. I tried this also with conditional formatting to turn the
newly entered line red, which didn't work either.
This is a name and address list where I want to check the first name(B),
last name(C), address line 2(F), address line 1(G), and zip(J) for being a
duplicate row.
I highlighted the col., data/validation/settings/custom/and entered formula
For col. B the formula is
OR(COUNTIF(C:C,B2)0,COUNTIF(F:F,B2)0),COUNTIF(G: G,B2)0,COUNTIF(J:J,B2)0)
For col. C the formula is
OR(COUNTIF(B:B,C2)0,COUNTIF(F:F,C2)0),COUNTIF(G: G,C2)0,COUNTIF(J:J,C2)0)
For col. F the formula is
OR(COUNTIF(B:B,F2)0,COUNTIF(C:C,F2)0),COUNTIF(G: G,F2)0,COUNTIF(J:J,F2)0)
For col. F the formula is
OR(COUNTIF(B:B,G2)0,COUNTIF(C:C,G2)0),COUNTIF(F: F,G2)0,COUNTIF(J:J,G2)0)
For col. F the formula is
OR(COUNTIF(B:B,J2)0,COUNTIF(C:C,J2)0),COUNTIF(F: F,J2)0,COUNTIF(G:G,J2)0)
Checking for 1 had the same result, entering anything returns the error
message.
Is it considering blank lines as the duplicates? This will be added to on a
regular basis so there's no range limit wanted. Please Help and Thanks!