Help with Data Validation on a cell containing a formula

Try

Formula:

Please Login or Register  to view this content.

Sorry, no luck. Thank you though.

It works with the original formula (COUNTIF($C$2:$C$2000,C2) if I type in a duplicate value manually, but I need it to detect the value of the formula automatically.
E.g. I typed in 'Adams J45244' in C6, and the error message popped up. However it didn't come up when I entered the formula A6&B6 into C6 (which should have given the same result.

When I enter the same COUNTIF formula in a cell in the spreadsheet, it works correctly to produce 'TRUE' or 'FALSE'. It's just that the 'FALSE' doesn't trigger a data validation error.

Any other workaround you can think of?

Using your original formula, the message appears if I enter "=A6 & B6" or "=A5&B5" with A6/B6 empty and duplicate in C5

I couldn't get your validation to work yesterday.

I'd use this in the helper column and not bother with the validation. You can set text colour to red.

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)>1,"Duplicate!","")

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.