CÓMO ACTUALIZAR UNA LISTA DESPLEGABLE AUTOMÁTICAMENTE EN EXCEL

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.

Cuando creamos una lista desplegable en Excel, definimos un rango de celdas como el origen de los datos, y cada vez que necesitamos agregar o quitar elementos a la lista debemos modificar la referencia de dicho rango.

Esto hace que la actualización de los elementos de una lista sea un proceso manual que puede quitarnos tiempo. Sin embargo, existen un par de técnicas que podemos utilizar para automatizar la actualización de los elementos de una lista desplegable en Excel.

LISTA DESPLEGABLE BASADA EN UNA TABLA

El primer método, y el más simple de todos, es que nuestros datos de origen estén en una tabla. De esta manera, Excel se encargará de actualizar automáticamente los elementos de la lista desplegable. A continuación, muestro los pasos para crear este tipo de lista:

1. Una vez que has ingresado los datos de origen dentro de una hoja, deberás seleccionarlos y pulsar el comando Insertar > Tabla.

2. Excel identificará el rango de datos y mostrará un cuadro de diálogo para confirmar la creación de la Tabla. Deberás asegurarte de que Excel haya detectado adecuadamente el encabezado de los datos y pulsar Aceptar.

3. Ahora que la tabla ha sido creada, deberás seleccionar la celda, o celdas, donde se creará la lista desplegable e ir a la pestaña Datos > Herramientas de datos > Validación de datos.

4. En el cuadro de diálogo Validación de datos, deberás elegir la opción Lista y posteriormente hacer clic en el botón de selección de rango.

5. En seguida deberás seleccionar los elementos de la tabla recién creada, sin incluir el encabezado de columna.

6. Deberás terminar la selección del rango y hacer clic en el botón Aceptar para crear la lista desplegable.

7. Al agregar un nuevo elemento en la tabla, Excel actualizará automáticamente los elementos de la lista desplegable.

Aunque el método descrito anteriormente es el más sencillo para crear una lista desplegable basada en una tabla, tiene una restricción, y es que la tabla debe estar en la misma hoja donde se encuentra la lista desplegable.

Si queremos olvidarnos de las restricciones y evitar cualquier problema con la actualización de la lista, puedes seguir los mismos pasos anteriores y crear la lista utilizando la siguiente fórmula:

=INDIRECTO("Tabla1[Categorías]")

La función INDIRECTO se encargará de traducir la cadena de texto en una referencia hacia la columna Categorías de la Tabla1 y de esta manera la lista quedará vinculada a la tabla de nuestro ejemplo. Recuerda que al crear tu lista deberás indicar el nombre de tu tabla y columna. La siguiente imagen muestra la ubicación exacta donde debemos colocar esta fórmula:

Aunque esta segunda alternativa puede tomarte algunos segundos adicionales para ingresar la fórmula, funcionará correctamente sin importar la hoja donde se encuentre la tabla. Al implementar adecuadamente cualquiera de los dos métodos anteriores, la lista desplegable se actualizará automáticamente al momento de agregar o remover filas en la tabla.

LISTA DESPLEGABLE BASADA EN UNA FÓRMULA

Las tablas de Excel fueron introducidas desde la versión 2007, así que la solución que revisamos en la sección anterior no era posible en las primeras versiones de la hoja de cálculo.

Por esa razón, encontrarás varias publicaciones que hablan sobre la solución que se utilizaba antes de que existieran las tablas y que está basada en una fórmula que utiliza las funciones CONTARA y DESREF de la siguiente manera:

=DESREF(F2, 0, 0, CONTARA(F:F)-1)

El objetivo de la función CONTARA es devolver la cantidad de celdas que no están vacías en la columna F. De esta manera, si agregamos un nuevo elemento a la columna F, la función CONTARA incrementará automáticamente el número de celdas contadas.

En esta fórmula he restado el valor 1 al resultado de la función CONTARA porque necesito descontar la celda ocupada por el encabezado de columna. En caso de que no tengas un encabezado, deberás remover dicha resta.

La función DESREF se encarga de crear una referencia al rango que comenzará en la celda F2 y que incluirá el número de filas devuelto por la función CONTARA menos 1. Esta fórmula la colocamos como el origen de la lista desplegable:

De esta manera, la lista se actualizará cada vez que se agrega o se quita un elemento en la columna F. Debido al comportamiento de la función CONTARA, es indispensable que toda la columna donde se encuentran los elementos de la lista sea utilizada solamente para almacenar dicha información.

El beneficio de actualizar una lista desplegable en Excel de forma automática garantiza que los datos o valores que siga ingresando o modificando en las celdas de los datos, se visualizará inmediatamente en la celda donde se encuentre la lista, esto evita que no se tenga que actualizar de forma manual la información.

Artículo que te recomendamos leer:
CÓMO INSTALAR LOS COMPLEMENTOS DE BI PARA EXCEL Y POR QUÉ…

¿Qué te pareció nuestra blog? Coméntanos.