EXCEL: Validación de datos para permitir valores que no existen en una lista

Nota: Excel tiene reglas de validación de datos integradas para listas desplegables. Esta página explica cómo crear una regla de validación personalizada cuando desea * evitar * que un usuario ingrese un valor en una lista.
Para permitir solo valores que no existen en una lista, puede usar la validación de datos con una fórmula personalizada basada en la función CONTAR.SI. En el ejemplo que se muestra, la validación de datos aplicada a B5: B9 es:

=CONTAR.SI(list,B5)=0

donde "lista" es el rango con nombre D5: D7.

Cómo funciona esta fórmula

Las reglas de validación de datos se activan cuando un usuario agrega o cambia un valor de celda.
En este caso, la función CONTAR.SI es parte de una expresión que devuelve VERDADERO cuando un valor no existe en una lista definida. La función CONTAR.SI simplemente cuenta las ocurrencias del valor en la lista. Mientras el recuento sea cero, la entrada pasará la validación. Si el recuento no es cero (es decir, el usuario ingresó un valor de la lista) la validación fallará.
Nota: Las referencias de celda en las fórmulas de validación de datos son relativas a la celda superior izquierda en el rango seleccionado cuando se define la regla de validación, en este caso B5.