Las listas desplegables nos permiten restringir las opciones que un usuario puede ingresar en una celda, lo cual es de mucha utilidad para validar los datos de nuestra hoja. Sin embargo, las opciones de una lista son determinadas al momento de crearla y en más de una ocasión queremos que esos valores de la lista aumenten o disminuyan automáticamente.
Si aún no sabes cómo crear una lista desplegable en Excel, te recomiendo leer el artículo Lista para validación de datos en Excel donde aprenderás tres métodos para a crear una lista. Uno de esos métodos es ingresar cada uno de los elementos de la lista separados por coma (o por punto y coma) en el recuadro Origen del cuadro de diálogo Validación de datos.
También aprenderás que podemos indicar el origen de la lista como un rango de celdas o como un rango nombrado y Excel obtendrá el valor de cada celda indicada y lo colocará como una opción de la lista.
Lo que nos interesa analizar en esta ocasión es el momento cuando necesitamos actualizar los valores de la lista, ya sea que quieres eliminar un elemento o agregar uno nuevo. Si has utilizado el método de ingresar los elementos en el recuadro Origen o has indicado un rango de celdas, para poder actualizar los elementos de la lista tendrás que abrir de nuevo el cuadro de diálogo Validación de datos y hacer la modificación manualmente.
La única opción de las que he mencionado anteriormente en donde no será necesario abrir el cuadro de diálogo Validación de datos para actualizar los elementos, serán aquellas listas cuyo origen fue indicado como un rango nombrado
Lista desplegable con nombre de rango
La gran ventaja de definir el origen de la lista como un rango nombrado, es que podrás tener varias listas en tu libro de Excel basadas en el mismo rango nombrado y eso nos permitirá actualizar todas las listas al mismo tiempo con solo modificar la definición del nombre.
Para este primer ejemplo supondremos un rango de datos como el mostrado en la siguiente imagen:
El primer paso para crear nuestra lista es crear el rango nombrado, así que seleccionaré el rango A1:A10 y en el recuadro de nombres colocaré el nombre “Vendedores” tal como se muestra a continuación:
Al pulsar la tecla Entrar habremos creado nuestro rango nombrado y en seguida seleccionaré la celda D1 y pulsaré el comando Datos > Validación de datos donde seleccionaré la opción Lista y colocaré como Origen el nombre recién creado.
Una vez creada la lista, la celda D1 mostrará todos los nombres contenidos en el rango nombrado.
Ahora viene la parte que nos interesa en este artículo y es la actualización de los elementos de la lista. Si quiero agregar un nuevo nombre a la lista debo insertarlo en la fila 11 de la hoja tal como se observa en la siguiente imagen:
Ahora debo ir a la ficha Fórmulas > Nombres definidos > Administrador de nombres y al abrirse el cuadro de diálogo debo pulsar el botón Editar para modificar el nombre “Vendedores” que recién acabamos de crear. Para incluir la nueva fila debemos modificar la referencia del rango nombrado.
Al hacer clic en Aceptar y cerrar la ventana del Administrador de nombres, la celda D1 mostrará una lista con el nuevo valor que acabamos de incluir en la definición del nombre.
Aunque este método no realiza una actualización automática, es de mucha utilidad en caso de tener varias listas basadas en los mismos datos ya que nos evitará el tener que modificar el origen de cada lista y solo tendremos que editar la referencia indicada en el nombre de rango.
Lista desplegable con una fórmula
El método que te mostraré a continuación hará una actualización automática de los elementos de una lista, ya sea que se agreguen nuevos elementos o que se eliminen. La única condición para que todo funcione correctamente es que se debe utilizar la columna de una hoja exclusivamente para colocar los elementos de la lista.
Se recomienda que esa columna especial no tenga encabezados de columna, ni tampoco celdas con contenido por debajo de los elementos de la lista. Esto se debe a que utilizaremos una fórmula que contará las celdas de dicha columna especial y por lo tanto necesitamos que solamente estén presentes los elementos de la lista.
Todo comienza con el uso de la función CONTARA, la cual nos ayuda a contar las celdas que tienen un valor dentro de un rango. Si queremos saber las celdas de la columna A que tienen un valor, podemos utilizar la siguiente fórmula:
=CONTARA(A:A)
La referencia A:A le pide a la función que considere todas las celdas de la columna A. Si utilizamos esta fórmula con nuestros datos de ejemplo, obtendremos el siguiente resultado:
La fórmula nos devuelve la cantidad exacta de celdas que tienen un valor en la columna A y ese dato lo podremos utilizar para crear una referencia al rango A1:A10. La fórmula que utilizaremos es la siguiente:
=DESREF(A1,0,0,CONTARA(A:A),1)
La función DESREF en Excel nos ayuda a definir un rango de celdas indicando el ancho y alto a partir de una celda inicial. En este caso, el primer argumento es la celda inicial y el tercero y cuarto argumentos son el alto y ancho del rango que deseamos obtener.
Lo importante de esta fórmula es el alto del rango, que es devuelto por la función CONTARA, ya que eso nos garantizará que siempre obtendremos el rango ocupado por las celdas de la columna A que a su vez serán los elementos de nuestra lista.
En seguida seleccionaré la celda D1 y pulsaré el comando Datos > Validación de datos y crearé una nueva lista indicando como Origen la fórmula que acabamos de crear:
Como resultado obtendremos una lista desplegable que tendrá todos los elementos de la columna A. Y lo mejor es que, al agregar un nuevo elemento en la columna A, será insertado automáticamente en la lista sin necesidad de realizar alguna acción.
Lista desplegable con nombre de rango dinámico
La tercera técnica que revisaremos, es una combinación de las dos anteriores, donde aprovecharemos lo mejor de ambos métodos. Por un lado, las listas creadas con rangos nombrados nos permiten actualizar el origen de datos en un solo lugar y como consecuencia todas las listas basadas en ese Nombre se actualizarán automáticamente.
Por otro lado, la fórmula nos da la oportunidad de obtener automáticamente el rango de celdas que contiene los valores dentro de una columna. Los pasos para crear una lista basada en un rango dinámico son los siguientes:
- Crear un rango nombrado basado en una fórmula.
- Crear una lista basa en el rango nombrado del paso anterior.
Para el primer paso iré a la ficha Fórmulas > Nombres definidos > Administrador de nombres y una vez que se abra el cuadro de diálogo, pulsaré el botón Nuevo para crear un nuevo nombre que estará basado en la fórmula que se muestra en la siguiente imagen:
Observa que el Nombre que he elegido es MiLista y el ámbito es el Libro, lo que quiere decir que el Nombre podrá ser utilizado en cualquier hoja del libro. La fórmula utilizada es la siguiente:
=DESREF(NombreDinamico!$A$1,0,0,CONTARA(NombreDinamico!$A:$A),1)
Esta fórmula necesita referencias absolutas porque queremos utilizar el nombre en cualquier hoja del libro. La referencia NombreDinamico!$A$1 le dice a Excel que debe ir a la celda A1 de la hoja NombreDinamico sin importar donde se encuentre.
Una vez que hemos creado el nombre de rango basado en una fórmula, podemos crear nuestra lista desplegable que tendrá como origen el rango nombrado que acabamos de crear. Solo debo ir a la ficha Datos > Validación de datos e ingresar la siguiente información:
De esta manera la lista de validación de datos se actualizará automáticamente al momento de ingresar nuevos valores bajo la columna A. En la siguiente imagen puedes observar que he agregado cinco nuevos nombres y la lista los despliega automáticamente.
Para probar este método, puedes crear listas desplegables en otras hojas del libro y observarás que se actualizan automáticamente al modificar las celdas de la columna A de la hoja NombreDinamico.
Lista desplegable con columna de Tabla
Uno de los métodos que más me gusta para agregar valores a una lista de manera automática, es utilizar la columna de una tabla de Excel como el origen de los datos.
Las tablas de Excel fueron introducidas en Excel 2007 y se refieren a un objeto que tiene un formato especial pero además, tienen una funcionalidad única. La siguiente imagen muestra nuestros datos de ejemplo después de haber creado una tabla con el comando Inicio > Dar formato como tabla.
Excel asigna de manera automática un nombre a la tabla y lo puedes confirmar al abrir el Administrador de nombres desde la ficha Fórmulas > Nombres definidos. La siguiente imagen nos muestra que el nombre de la tabla es Tabla1:
Para crear una lista basada en la columna Nombre de la Tabla1 utilizaremos la siguiente fórmula:
=INDIRECTO("Tabla1[Nombre]")
La función INDIRECTO nos devuelve una referencia a partir de una cadena de texto y en este caso, la referencia devuelta será a la columna Nombre de la Tabla1. Al momento de crear nuestra lista desplegable debemos colocar la fórmula de la siguiente manera.
La lista desplegable mostrará todos los valores de la columna Nombre de la tabla.
Al agregar nuevas filas a la tabla, la lista desplegable mostrará automáticamente los nuevos elementos de la columna Nombre. Ya que las tablas tienen un alcance en todo el libro, podrás crear este tipo de listas en cualquier hoja del libro y se actualizarán automáticamente al detectar cambios en los datos de la tabla.
Ahora conoces diferentes alternativas en Excel para cargar valores a una lista de manera automática. Descarga el libro de trabajo que he utilizado en este artículo y experimenta con la creación de este tipo de listas.