Listas Desplegables Dinámicas

Aquí encontraras como obtener una listas desplegable que se actualice automáticamente al incluir o quitar datos.

En una lista desplegable, ya sea por rango o a partir de datos, se mantiene estática y se debe actualizar manualmente - o hacer unos truquitos - para que incluya los nuevos datos. 

En el caso que sean datos fijos, se deberán escribir las nuevas opciones separadas con coma (o el separador de argumentos que tengamos definido). 

Si fuera por rango:
 - Se debe actualizar el mismo incluyendo o quitando lo que corresponda; 

 - Podemos eliminar las celdas que deseemos quitar, o insertar celdas/filas entre la primera y última celda del rango.

 - Si existirá un límite máximo podemos seleccionar tantas como este sea, el inconveniente será que las celdas vacías aparecerán como opciones en blanco. 

Si bien estas opciones nos pueden ser útiles no son óptimas, si actualizamos muchos datos nos llevará mucho tiempo e incluso nos puede generar errores, especialmente si estamos preparando una planilla para un cliente, ni hablar si este no conoce de Excel.

  

Necesitaremos de dos funciones:

  

Función DESREF

Devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas. La referencia devuelta puede ser una celda o un rango de celdas. Puede especificar el número de filas y el número de columnas a devolver.

Esto quiere decir que podemos definir un rango a partir de una celda.

Función CONTARA 

La función CONTARA cuenta la cantidad de celdas que no están vacías en un intervalo.

La función DESREF nos permitirá establecer el rango donde se encuentran los datos, mientras que la función CONTARA nos indicará cuantas opciones tenemos disponibles.

El primer paso es establecer donde se encontrarán las opciones, dentro de la misma hoja, otra hoja o si esto se obtendrá de una tabla, etc. 

Dentro de la Misma Hoja

=DESREF(H2,0,0,CONTARA(H:H),1)

Para entender mejor como completamos los datos de DESREF iremos argumento por argumento.

• Referencia. Es la celda que servirá de referencia para definir el rango. En este caso H2 ya que debe ser la primer opción.

• Filas. Es el número de filas, a partir del primer argumento, al que se desea hacer referencia. En este caso es 0, ya que necesitamos que nuestro rango comience con la primer opción.

• Columnas. Es el número de columnas, a partir del primer argumento, al que se desea hacer referencia. En este caso es 0, ya que necesitamos que nuestro rango comience con la primer opción.

• Alto.  Es el número de filas que comprenderá el rango. Este es el dato que nos irá cambiando y el meollo de la cuestión. Aquí anidaremos la función CONTARA.

• CONTARA. El único argumento que necesitamos aquí es la columna donde completaremos las opciones. En este CASO H:H para que comprenda toda la columna. 

• Ancho. Es el número de columnas que comprenderá el rango. Solo podrá optarse por 1 ya que es el límite que nos establece las listas desplegables.

En una Hoja Diferente

=DESREF(Hoja2!A1,0,0,CONTARA(Hoja2!A:A),1)

Aquí lo único diferente al caso anterior es que debemos recordar completar el nombre de la hoja y el símbolo ! en la fórmula. 

En este caso las opciones comienzan en la celda A1 de la Hoja 2, por ende el CONTARA se estableció en la columna A:A

En una Hoja Diferente con Título

=DESREF(Hoja3!A2,0,0,CONTARA(Hoja3!A:A)-1,1)

Aquí las opciones comienzan en A2, ya que en A1 esta el título. Al definir como argumento de referencia A2 no nos incluirá el título como opción, pero si en CONTARA ya que es una celda no vacía dentro del rango que establecimos.

Para mitigar esta celda de mas le restamos 1. 

IMPORTANTE

Si hubiera celdas vacías entremedio de las opciones deberán quitarse ya que nos aparecerán vacíos en la lista desplegable y perderemos las últimas opciones de la columna. 

Post de utilidad:

• Listas Desplegables 

• Listas Desplegables Dependientes 

• Eliminar Celdas Vacías

• Función CONTARA