Formule générique
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Sommaire
Pour interdire l'entrée qui contient l'un des nombreux éléments, vous pouvez utiliser une règle de validation de données personnalisée basée sur la fonction RECHERCHE.
Dans l'exemple illustré, la validation des données appliquée à B5: B11 est:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Explication
Les règles de validation des données sont déclenchées lorsqu'un utilisateur ajoute ou modifie une valeur de cellule.
Cette formule utilise la fonction RECHERCHE pour tester l'entrée utilisateur pour chaque valeur de la plage nommée «liste». La logique de recherche est "contient" - lorsqu'une valeur de "liste" est trouvée, SEARCH renvoie la position de la valeur sous forme de nombre. S'il n'est pas trouvé, SEARCH renvoie une erreur.
La fonction ISNUMBER convertit ensuite les nombres en TRUE et les erreurs en FALSE, et l'opérateur double négatif change les valeurs TRUE FALSE en 1 et zéros. Puisque la plage nommée "list" contient 5 valeurs, nous obtenons 5 résultats dans un tableau comme celui-ci:
(0; 0; 0; 0; 0)
SUMPRODUCT résume ensuite les éléments du tableau et le résultat est testé par rapport à zéro. Tant que tous les éléments sont à zéro, SUMPRODUCT renvoie zéro et la validation réussit. Si SUMPRODUCT renvoie un autre nombre (c'est-à-dire lorsqu'un élément de la "liste" est trouvé), la formule renvoie FALSE et la validation échoue.
Remarque: les références de cellule dans les formules de validation de données sont relatives à la cellule supérieure gauche de la plage sélectionnée lorsque la règle de validation est définie, dans ce cas B5.
