Formula generica
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Sommario
Per non consentire l'input che contiene una delle tante cose, è possibile utilizzare una regola di convalida dei dati personalizzata basata sulla funzione SEARCH.
Nell'esempio mostrato, la convalida dei dati applicata a B5: B11 è:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Spiegazione
Le regole di convalida dei dati vengono attivate quando un utente aggiunge o modifica un valore di cella.
Questa formula utilizza la funzione RICERCA per testare l'input dell'utente per ogni valore nella "lista" dell'intervallo denominato. La logica di ricerca è "contiene": quando viene trovato un valore da "elenco", SEARCH restituisce la posizione del valore come numero. Se non viene trovato, SEARCH restituisce un errore.
La funzione ISNUMBER converte quindi i numeri in TRUE e gli errori in FALSE e l'operatore doppio negativo modifica i valori TRUE FALSE in 1 e zero. Poiché l'intervallo denominato "elenco" contiene 5 valori, otteniamo 5 risultati in un array come questo:
(0; 0; 0; 0; 0)
SUMPRODUCT quindi riassume gli elementi nell'array e il risultato viene verificato rispetto allo zero. Finché tutti gli elementi sono zero, SUMPRODUCT restituisce zero e la convalida ha esito positivo. Se SUMPRODUCT restituisce un altro numero (cioè quando viene trovato un elemento in "lista") la formula restituisce FALSE e la convalida non riesce.
Nota: i riferimenti di cella nelle formule di convalida dei dati sono relativi alla cella in alto a sinistra nell'intervallo selezionato quando viene definita la regola di convalida, in questo caso B5.
