Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Sammanfattning
För att inte tillåta inmatning som innehåller en av många saker kan du använda en anpassad datavalideringsregel baserat på SÖK-funktionen.
I det visade exemplet är datavalidering tillämpad på B5: B11:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Förklaring
Datavalideringsregler utlöses när en användare lägger till eller ändrar ett cellvärde.
Denna formel använder SÖK-funktionen för att testa användarinmatning för varje värde i namnet "lista". Söklogiken är "innehåller" - när ett värde från "lista" hittas, returnerar SÖK värdets position som ett tal. Om det inte hittas returnerar SEARCH ett fel.
ISNUMBER-funktionen omvandlar sedan siffror till SANT och fel till FALSKA, och den dubbla negativa operatören ändrar SANT FALSKA värden till 1s och nollor. Eftersom det namngivna intervallet "lista" innehåller 5 värden, får vi tillbaka 5 resultat i en sådan matris:
(0; 0; 0; 0; 0)
SUMPRODUCT summerar sedan objekten i matrisen och resultatet testas mot noll. Så länge alla objekt är noll returnerar SUMPRODUCT noll och valideringen lyckas. Om SUMPRODUCT returnerar ett annat nummer (dvs. när ett objekt i "lista" hittas) returnerar formeln FALSE och valideringen misslyckas.
Obs! Cellreferenser i datavalideringsformler är relativa till den övre vänstra cellen i det intervall som valts när valideringsregeln definieras, i detta fall B5.
