Transcripción del vídeo
Cuando creamos una lista desplegable en Excel y queremos que sus elementos se actualicen automáticamente, debemos modificar un poco la manera en que creamos la lista.
En esta ocasión revisaremos dos métodos que podemos utilizar para hacer que nuestras listas se actualicen automáticamente al agregar o quitar elementos en el origen de los datos.
El primer método es crear una lista basada en una tabla y el segundo método es crear la lista basada en un rango dinámico, es decir, con una fórmula.
Comencemos con el primer método y lo primero que debemos hacer es seleccionar una celda del rango de datos que contiene los elementos de la lista e ir a la pestaña Insertar y pulsar el botón Tabla.
Excel identificará el rango de datos y solo deberás asegurarte de que haya detectado correctamente el encabezado de columna.
Al pulsar el botón Acetar, habremos creado la tabla y en la pestaña diseño podrás ver su nombre y que en este ejemplo es Tabla1.
El siguiente paso es elegir la celda que tendrá la lista desplegable, así que seleccionaré la celda C2 e iré a la pestaña Datos y dentro del grupo herramientas de datos, pulsaré el botón Validación de datos y se abrirá el cuadro de diálogo.
Elegiré la opción Lista y en el Origen haré clic en botón de selección de rango. Ahora debemos utilizar el ratón para seleccionar la lista de elementos de la tabla sin incluir el encabezado.
Una vez que he hecho la selección, vuelvo a pulsar el botón de selección de rango y hago clic en el botón aceptar para crear la lista desplegable en la celda C2.
Observa que la nueva lista muestra correctamente las opciones indicadas en la tabla.
Ahora iré a la celda F7 e ingresaré la categoría Recetas.
Al pulsar Entrar, podrás ver que Excel mueve el borde de la tabla hacia abajo para incluir la nueva fila.
Al ir de nuevo a la celda C2, y abrir las opciones de la lista desplegable, podrás ver que la lista se ha actualizado automáticamente para incluir la nueva categoría.
La técnica que acabamos de utilizar solo funcionará para crear listas donde la tabla se encuentre en la misma hoja.
Si la tabla está ubicada en otra hoja, la lista no se actualizará automáticamente, pero afortunadamente existe una alternativa de solución.
Para mostrarte esta segunda técnica, iré a la Hoja3 donde tengo una copia de los datos de las categorías y los convertiré en una tabla.
Así que iré a la pestaña Insertar y pulsaré el botón Tabla y haré clic en Aceptar.
Observa que la ficha Diseño me dice que el nombre de esta tabla es Tabla2.
Ahora regresaré a la Hoja1 y seleccionaré la celda C3 e iré a la pestaña Datos y pulsaré el botón Validación de datos.
Elegiré la opción Lista y aquí es donde está la diferencia con la técnica anterior, ya que, en lugar de utilizar el ratón para seleccionar el rango, ingresaremos la siguiente fórmula.
Símbolo de igual, INDIRECTO, abro paréntesis, doble comilla, Tabla2, abro corchetes, Categorías, cierro corchetes, cierro también las dobles comillas y el paréntesis.
La función INDIRECTO se encarga de interpretar la cadena de texto y devolvernos una referencia a la columna Categorías de la Tabla2.
Haré clic en el botón Aceptar y se habrá creado la lista desplegable en la celda C3.
Recuerda que en la celda superior tengo una lista con las opciones de la Tabla1 que está en esta misma hoja, pero en la celda C3 veo las opciones de la Tabla2, así que iré a la Hoja3 y agregaré dos nuevas categorías: la categoría Recetas y la categoría Negocios.
De nueva cuenta Excel actualizará la tabla automáticamente por nosotros y al regresar a la Hoja1 y ver las opciones de la lista de la celda C3, veremos que se ha actualizado automáticamente con las nuevas categorías de la Tabla2.
Aunque la primera técnica que utilizamos con el ratón es muy rápida para crear la lista desplegable, generalmente recomiendo tomar algunos segundos adicionales para utilizar el método de la fórmula ya que con esto nos aseguraremos de que la lista se actualizará automáticamente sin importar la ubicación de la tabla.
Las técnicas que te acabo de mostrar, solo funcionan cuando existe una tabla de Excel de por medio, pero dichas tablas fueron incluidas a partir de Excel 2007.
Antes de esa versión, no era posible crear tablas, así que existía otro método para solucionar las actualizaciones automáticas y era a través de una fórmula.
La idea de la fórmula es la siguiente. La función CONTARA nos ayuda a obtener la cantidad de celdas no vacías dentro de un rango, así que la podemos utilizar para conocer la cantidad de elementos de una lista.
En la celda H1 ingresaré la fórmula CONTARA, paréntesis, F dos puntos F, y cierro paréntesis y como resultado obtendré el número 6 que es la cantidad de celdas con datos en la columna F.
Ahora agregaré el texto Recetas en la celda F7 y podrás observar que la fórmula se actualiza automáticamente.
Ahora ingresaré la categoría Negocios y obtendré la nueva cuenta en la celda H1.
Esta fórmula me devuelve el total de elementos de la columna F, pero en mis datos de ejemplo la celda F1 tiene el encabezado de columna, el cual no quiero incluir en la lista desplegable, así que debo modificar la fórmula de la celda H1 restando el número 1 para obtener la cuenta exacta de las categorías.
Si elimino las últimas dos celdas que acabo de ingresar, la fórmula se actualizará automáticamente para decirme la cantidad de categorías en el listado.
Además de la función CONTARA, nuestra fórmula utilizará la función DESREF la cual nos ayudará a crear una referencia a un rango de celdas tras indicar la cantidad de filas que necesitamos.
En un instante te mostraré esa fórmula, pero ahora eliminaré el contenido de la celda H1 porque solo lo he incluido para explicarte la función CONTARA.
Ahora crearemos nuestra lista desplegable y para eso iré a la celda C2 e iré a la pestaña Datos y pulsaré el botón Validación de datos.
Elegiré la opción Lista y en el cuadro de texto Origen, ingresaré la siguiente fórmula.
Símbolo de igual, DESREF, abro paréntesis, F2. Este primer argumento es para pedirle a la función DESREF que el rango que quiero obtener comenzará a partir de la celda F2.
Los siguientes dos argumentos de la función le indican algún tipo de desplazamiento, pero no lo necesito, así que ingresaré dos ceros y finalmente como cuarto argumento, le indicaré la cantidad de filas que necesito y que es precisamente el resultado de la función CONTARA.
Así que, ingresaré CONTARA, paréntesis, F dos puntos F, cierro paréntesis, y le resto uno para obtener la cantidad exacta de categorías y con esto he finalizado con la fórmula así que cierro el paréntesis.
Pulsaré el botón Aceptar y tendré la lista desplegable en la celda C2 con las categorías de la columna F.
Ingresaré la categoría Recetas y al terminar podrás ver que lista desplegable la ha incluido automáticamente.
Ya que este método no utiliza tablas, era la única alternativa disponibles para los usuarios de versiones anteriores a Excel 2007 pero ahora tú tienes la opción de elegir cualquiera de los métodos que acabamos de revisar para crear tus listas desplegables auto actualizables.