Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Resumé
For at afvise input, der indeholder en af mange ting, kan du bruge en brugerdefineret datavalideringsregel baseret på SEARCH-funktionen.
I det viste eksempel er datavalidering anvendt på B5: B11:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Forklaring
Datavalideringsregler udløses, når en bruger tilføjer eller ændrer en celleværdi.
Denne formel bruger SEARCH-funktionen til at teste brugerinput for hver værdi i det navngivne områdes "liste". Søgelogikken er "indeholder" - når en værdi fra "liste" findes findes, returnerer SØG værdien som et tal. Hvis den ikke findes, returnerer SEARCH en fejl.
ISNUMBER-funktionen konverterer derefter tal til SAND og fejl til FALSK, og den dobbelte negative operator ændrer SAND FALSK-værdier til 1s og nuller. Da det navngivne interval "liste" indeholder 5 værdier, får vi tilbage 5 resultater i en matrix som denne:
(0; 0; 0; 0; 0)
SUMPRODUCT opsummerer derefter elementerne i arrayet, og resultatet testes mod nul. Så længe alle varer er nul, returnerer SUMPRODUCT nul, og validering lykkes. Hvis SUMPRODUCT returnerer et andet nummer (dvs. når et element i "liste" findes), returnerer formlen FALSE, og validering mislykkes.
Bemærk: Cellereferencer i datavalideringsformler er i forhold til den øverste venstre celle i det valgte område, når valideringsreglen er defineret, i dette tilfælde B5.
