column validation syntax

Question

Trying to compare 4 fields (only if populated) against one another so that no data is duplicated? I've tried something like this but gets too long.

=IF(LEN(f1=1), IF(LEN(f2=1),f1<>f2,FALSE),FALSE)
=IF(LEN(f1=1), IF(LEN(f3=1),f1<>f3,FALSE),FALSE)
...

Can you show me a better way to do this?

Answer 1

The formula will be long and there is no way to escape it. If possible you should do this in an event receiver.

=IF(ISBLANK(f1),TRUE,IF(ISBLANK(f2),TRUE,IF(ISBLANK(f3),TRUE,IF(ISBLANK(f4),TRUE,IF((f1=f2)OR(f1=f3)OR(f1=f4)),TRUE,IF((f2=f3)OR(f2=f4),TRUE,IF(f3=f4,TRUE,FALSE)))))))

The logic is a follows

1. If any of the field is blank then return true
2. else
   • if f1 = f2 or f1 = f3 or f1 = f4 then return true
   • else if f2 = f3 or f2 = f4 then return true
   • else if f3 = f4 then return true
   • else return false