Lista de validación de datos en Excel que detecta nuevos elementos automáticamente

Una de las preguntas más socorridas en temas de Validación de datos es cómo podemos tener una lista de validación que aumente de tamaño automáticamente cada vez que añadimos nuevos elementos a la lista. La manera predeterminada en la que funcionan las listas de validación es tener un lista fija, y lo interesante es que esa lista no sea fija, sino dinámica.

Seguro te puede interesar estos tutoriales de Validación de datos en Excel.

3 maneras de tener listas de validación autoajustables

Como dije en la introducción, lo interesante de las listas de validación es cuando descubrimos la manera de tener listas dinámicas, es decir, que se adecúan automáticamente al tamaño de los datos. En este tutorial veremos 3 maneras en la que podremos lograr tener listas de validación autoajustables:

• Usando la función DESREF.
• Usando Nombres definidos.
• Valores desde una Tabla.

Usando la función DESREF

La función DESREF nos permitirá devolver un rango a partir de una celda de referencia, además de que podremos definir el alto y el ancho de dicho rango. El rango que nos devuelva la función DESREF lo usaremos como fórmula en la opción Lista de Validación de datos. Lo interesante de la función DESREF es que en el parámetro alto podremos definir el alto del rango de manera dinámica usando la función CONTARA que nos contará las celdas con valores.

• Elegimos la celda donde deseamos la lista.
• Nos vamos a la pestaña Datos > Validación de datos.
• En Permitir elegimos Lista.
• Ingresamos la fórmula: =DESREF(I1,1,0,CONTARA(I:I)-1)
• Aceptar.

Figura 1. Usamos la función DESREF para obtener una lista dinámica de valores.

Ver Video Lista de validación autoajustable en Excel

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Usando nombres definidos

La ventaja de los Nombres en Excel es que podemos guardarles una fórmula y después sólo hacer referencia al Nombre y no a la fórmula completa. Usaremos la misma fórmula del truco anterior para asignarla a un Nombre definido que llamaremos lstOffice. Seguimos los siguientes pasos para definir el Nombre y asignarle la fórmula usada anteriormente.

• Nos vamos a la pestaña Fórmulas > Asignar nombre.
• Como Nombre le definimos lstOffice.
• En la sección Se refiere a, ingresamos la fórmula =DESREF(I1,1,0,CONTARA(I:I)-1)

Figura 2. El Nombre lstOffice guardará la fórmula donde usamos la función DESREF.

• Nos vamos a Datos > Validación de datos.
• En Permitir elegimos Lista.
• En origen ingresamos =lstOffice.
• Aceptar.

Figura 3. Lista de validación autoajustable en Excel.

Valores de una Tabla

Como lo hemos visto en otro tutorial, el manejar datos en Tablas tienen varias ventajas, entre ellas, que cada vez que añadimos filas o columnas de valores el tamaño de la Tabla aumenta automáticamente considerando dichos valores. Por lo anterior, la tercer manera de tener una lista de validación autoajustable es obteniendo los valores directamente de la columna de una Tabla. Aunque directamente no podemos hacer referencia a la Tabla cuando usamos una Lista de Validación de datos, sino que deberemos de auxiliarnos de la función INDIRECTO que nos ayudará a hacer referencia a los datos de la Tabla usando dicha referencia como si fuera texto. Haremos lo siguiente:

• Elegimos el rango que se será la lista.
• Presionamos [Control] + [T] para convertir los valores a Tabla.
• Marcamos la opción La tabla tiene encabezados.
• Aceptar.
• La Tabla tendrá como nombre Tabla1 y su encabezado será OFFICE.

Ahora seguimos los siguientes pasos para usar los valores de la Tabla como Lista de Validación de datos.

• Nos vamos a Datos > Validación de datos.
• En Permitir elegimos Lista.
• En origen ingresamos la fórmula =INDIRECTO(“Tabla1[OFFICE]”)
• Aceptar.

Figura 4. Lista de validación con valore de una Tabla en Excel.

Así como en los dos ejemplos, con este ejemplo también tendremos una lista de validación que se ajustará automáticamente cuando ingresemos nuevos valores.

Descarga el archivo de ejemplo

 Listas de validación autoajustables – EXCELeINFO.zip

Si te gustó este tutorial por favor anótate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.