Data Validity List

[Cubaine]

In my application I have two fields of type EnumList called List 1 and List 2.
I want an error to show up if the data in list 1 contains the same data in list 2 and vice versa.

I am using the following formula in list 1:
NOT(IN([_THIS], [List 2]))

The expression result reads as follows:
This statement is false: …(The value of column ‘List 1:’) is one of the values in the list (The value of column ‘List 2:’)

This is exactly what I want and it does work, however it only works when I have one entry in list 1.

The validity error message will for example show up if I have the entry apples in both list 1 and list 2, but not if I have apples, oranges in list 1 and just apples in list 2.

What formula should I use to restrict entries that contain a specific word from list 2?

Thanks in advance for any help given.

[Aleksi]

So… do you want to check, if these both lists are exactly the same?

[Cubaine]

Hi Aleksi,

Yes I want to check if a list item from list 1 is already in list 2 and vice versa.

I’ll try to describe it better.

Say list 1 is an EnumList of favorite colors and list2 is an EnumList of unfavored colors. Both lists could have the same possible colors, but a error message should appear when I select the same color in both lists.

If I have the color blue as favorite then it should give an error message if I try to select blue in the list of unfavored colors.

Right now I have it working as follows:

If I select blue in my favorite colors list and blue or blue, red, etc in my unfavored colors list I get an error message which is good.
However if I select blue, red in my favorites list and blue or blue, red in my unfavored list I don’t get this error message I believe it might be due to the separator.

I know I could use reference columns instead, but I don’t want the user to leave the view when adding a new entry to the list that’s why I prefer EnumList in this case.

I hope my explanation was more clear.

[Cubaine]

Also I have used the CONTAINS formula instead of the IN formula first as this seemed the most logical choice, but this also hasn’t worked out for me.

[Cubaine]

I have it working now with the following formula: NOT(CONTAINS([_THIS], ANY([List 2:])))

[Aleksi]

I believe your formula will work if you choose your options in the same order (if you choose more than one option). Let’s say you have options “One, Two and Three” and you choose “Two, One” in your first EnumList and “One, Two” in your 2nd EnumList. Then your formula will be like NOT(CONTAINS(“One,Two”,“Two,One”)) which is TRUE where as NOT(CONTAINS(“One,Two”,“One,Two”)) is FALSE.

You need to handle this situation comparing lists, not text strings. You could write it like…
COUNT([EnumList#1]-[EnumList#2])=COUNT([EnumList#1]) and it should work what ever the order is.

[Steve]

IN() expects it first argument to be a single value, not a list. If given a list as the first argument, it will interpret it as a single Text value and try to match that single text value against each value in the list of the second argument.

CONTAINS() expects two Text arguments. If a non-Text value is given as an argument, it will be interpreted as a single Text value.