Generieke formule
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Samenvatting
Als u invoer wilt weigeren die een van de vele dingen bevat, kunt u een aangepaste regel voor gegevensvalidatie gebruiken op basis van de functie SEARCH.
In het getoonde voorbeeld is de gegevensvalidatie toegepast op B5: B11:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Uitleg
Regels voor gegevensvalidatie worden geactiveerd wanneer een gebruiker een celwaarde toevoegt of wijzigt.
Deze formule gebruikt de SEARCH-functie om gebruikersinvoer te testen voor elke waarde in het benoemde bereik "lijst". De zoeklogica is "bevat" - wanneer een waarde uit "lijst" wordt gevonden, geeft SEARCH de positie van de waarde terug als een getal. Indien niet gevonden, geeft SEARCH een foutmelding.
De ISGETAL-functie converteert vervolgens getallen naar WAAR en fouten naar ONWAAR, en de dubbele negatieve operator verandert de WAAR-ONWAAR-waarden in enen en nullen. Omdat het benoemde bereik "lijst" 5 waarden bevat, krijgen we 5 resultaten terug in een array zoals deze:
(0; 0; 0; 0; 0)
SUMPRODUCT somt vervolgens de items in de array op en het resultaat wordt tegen nul getoetst. Zolang alle items nul zijn, retourneert SOMPRODUCT nul en slaagt de validatie. Als SOMPRODUCT een ander getal retourneert (dwz wanneer een item in "lijst" wordt gevonden), retourneert de formule FALSE en mislukt de validatie.
Opmerking: celverwijzingen in formules voor gegevensvalidatie zijn relatief ten opzichte van de cel linksboven in het bereik dat is geselecteerd bij het definiëren van de validatieregel, in dit geval B5.
