Cómo crear listas desplegables dependientes Excel 2019 y Excel 2016

Para gestionar nuestros datos, una de las aplicaciones que más utilizamos la mayoría de nosotros, es Excel. No cabe duda de que las posibilidades de estas hojas de cálculo son bastante amplias, por lo que esto nos ayuda a realizar diferentes funciones de manera sencilla gracias a la cantidad de fórmulas y opciones que ofrece.

 

 

Excel 2016 Y Excel 2019 nos ofrece una amplia gama de opciones gracias a sus múltiples funciones y fórmulas que nos permiten controlar datos de una forma sencilla pero profesional. Contamos con diversos recursos como gráficos, tablas dinámicas, pívots, pero una de las funcionalidades más completas son las listas desplegables ya que estas nos brindan la posibilidad de tener un control sobre los datos que los usuarios introducen.

 

 

En este caso tenemos los siguientes datos:

 

 

Esta hoja la hemos llamado Listas y es en esta hoja donde haremos las configuraciones necesarias para crear las listas desplegables dependientes en otras hojas de este libro.

 

 

1. Preparar datos Excel 2019 Y Excel 2016

Como vemos hemos creado una lista de países con ciudades de cada uno de ellos, por lo tanto, el primer paso consiste en crear una lista de países únicos y para ello haremos lo siguiente: Hemos copiado los datos de la columna A en otra columna, en este caso la columna E, y una vez allí los seleccionamos y vamos a la pestaña Datos, grupo Herramientas de datos y allí pulsamos el botón Quitar duplicados.

 

 

Ahora con los datos de países únicos crearemos una nueva lista llamada País. Para ello seleccionamos las celdas que contienen los datos e ingresamos el nombre en el cuadro de nombres ubicado en la parte superior.

 

 

A continuación hemos ordenado la columna A en orden ascendente para un mejor control:

 

 

A continuación asignaremos los rangos a las ciudades según su respectivo país. Para ello debemos seleccionar el rango de celdas de cada país y en el cuadro de nombres ingresar el respectivo país.

 

 

Debemos tener presente que el nombre del rango debe ser igual al país ya que estos son los vínculos entre ambas listas. Para ver los rangos que hemos creado podemos ir a la pestaña Formulas, grupo Nombres definidos y allí seleccionar la opción Administrador de nombres

 

 

En caso de presentar algún fallo, podemos usar la opción Modificar y realizar los ajustes necesarios.

 

2. Crear listas desplegables Excel 2019 Y Excel 2016

 

Para esto vamos a crear una nueva hoja dentro del libro y nos ubicaremos en la celda A2 y allí iremos a la pestaña Datos, grupo Herramientas de datos y seleccionamos la opción Validación de datos

 

 

Al seleccionar esta opción se desplegará el siguiente asistente donde debemos definir en el campo Permitir la opción Lista y en el campo Origen ingresaremos el nombre del rango que hemos creado en la hoja inicial con los datos de los países precedida del símbolo =. Una vez definidos estos valores pulsamos Aceptar

 

 

Podemos ubicarnos en la celda A2 y de forma automática se visualiza la lista desplegable y al pulsar sobre ella veremos las opciones disponibles:

 

 

A continuación vamos a crear la lista desplegable dependiente en la celda B2 y para ello seleccionamos esta celda y vamos a la pestaña Datos / Herramientas de datos / Validacion de datos y en el asistente desplegado elegimos nuevamente Lista en el campo Permitir y en el campo Origen ingresaremos lo siguiente:

=INDIRECTO(A2)

 

La función INDIRECTO es la encargada de obtener el rango de celdas en las cuales su nombre coincide con el valor de la celda A2. Por regla general al pulsar Aceptar se genere un mensaje con el siguiente error: “El origen actualmente evalúa un error ¿Desea continuar?”, esto es debido a que no hay ningún dato seleccionado en la celda A2.

 

Al momento de seleccionar algún país en la celda A2 veremos las opciones disponibles en la celda B2:

 

 

De este modo hemos creado una lista desplegable dependiente en Excel 2016.

 

3. Cómo limpiar la selección en la lista dependiente Excel 2019 Y Excel 2016

 

Un error común con este tipo de listas desplegables es que cuando hemos seleccionado inicialmente un dato en la celda B2, si modificamos el valor de la celda A2 el valor en B2 continuará igual y no será actualizado:

 

 

Si guardamos el dato con estos datos estaremos almacenando información errónea lo cual puede afectar muchas tareas. Para solucionar esto Excel 2016 no cuenta con un comando que actualice automáticamente la información, para ello debemos usar un código VBA para este fin. Damos clic derecho sobre el nombre de la hoja donde hemos creado la lista desplegable dependiente y seleccionamos la opción Ver código

 

 

Se desplegará la siguiente ventana donde debemos elegir la opción Worksheet y Change respectivamente:

 

 

Allí debemos ingresar el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)If Target = Range("A2") Then Range("B2").Value = ""End IfEnd Sub

Con este código tenemos lo siguiente:

• El evento Worksheet_Change se activa cada vez que se realiza un cambio en la hoja.

• La variable Target analiza si el cambio proviene de la celda A2 y en caso de ser positivo limpiara la celda B2 con el nuevo valor.

 

4. Agregar nuevos datos a la lista desplegable Excel 2019 Y Excel 2016

 

En este caso agregaremos la ciudad de Málaga (España) por lo cual agregaremos una nueva fila para incluir el dato

 

 

Podemos ver que España cuenta ahora con 3 ciudades (B6:B8), si vamos a la pestaña Formulas / Administrador de nombres veremos lo siguiente:

 

 

Notemos que el rango España tiene el rango de celdas B6:B7 lo cual indica que no ha sido actualizada y para esto debemos pulsar el botón Modificar y ajustar el respectivo rango:

 

 

Si ahora vamos a la hoja del libro donde creamos la lista desplegable independiente veremos los cambios ocurridos:

 

 

 

5. Cómo hacer otro tipo listas desplegables en Excel 2019 y Excel 2016

 

 

 

Comprendemos la utilidad y alcance que nos brindan las listas desplegables independientes en Excel 2019 Y Excel 2016. De este modo podremos hacer uso de estas funciones siempre que nos resulte necesario.