Una de las funcionalidades más utilizadas en la validación de datos en Excel son las listas desplegables ya que nos ofrecen un control absoluto sobre el ingreso de datos de los usuarios. Sin embargo, crear listas dependientes no siempre es una tarea sencilla, así que te mostraré un método para lograr este objetivo.
Decimos que tenemos listas desplegables dependientes cuando la selección de la primera lista afectará las opciones disponibles de la segunda lista. Esto nos ofrece un mayor control sobre las opciones elegidas por el usuario ya que siempre habrá congruencia en los datos ingresados.
Para nuestro ejemplo utilizaremos un listado de países y ciudades con el cual crearemos un par de listas desplegables que mostrarán las ciudades que pertenecen al país previamente seleccionado.
Este listado se encuentra en una hoja de Excel llamada Datos que es donde prepararemos los datos de manera que poder crear con facilidad las listas desplegables dependientes desde cualquier otra hoja del libro.
Preparación de los datos
El primer paso que debemos dar es crear una lista de países únicos. Para esto haré una copia de los datos de la columna A y pegaré los valores en la columna D. Posteriormente, con la columna seleccionada, iré a la ficha Datos > Herramientas de datos y pulsaré el botón Quitar duplicados.
Ahora seleccionaré el rango de celdas D2:D7 y le pondré el nombre Paises. Para asignar un nombre a un rango de celdas debemos seleccionarlo e ingresar el texto en el Cuadro de nombres de la barra de fórmulas.
El segundo paso será nombrar los rangos de las ciudades para cada país de la siguiente manera:
- Selecciona el rango que contiene las ciudades de un país.
- Nombra dicho rango con el nombre del país.
Siguiendo este procedimiento tan simple, la siguiente imagen muestra el momento en que selecciono las ciudades de Argentina y asigno el nombre adecuado a dicho rango.
Es muy importante que el nombre del rango sea exactamente igual al nombre del país ya que ese será nuestro vínculo entre ambas listas. De la misma manera como he creado el rango de ciudades para Argentina crearé un nuevo rango para cada país.
Una vez terminada esta tarea tendré 7 rangos nombrados. Un rango nombrado para cada uno de los 6 países y además un nombre para la lista de países únicos. Para ver esa lista de rangos nombrados puedo ir a la ficha Fórmulas y hacer clic en el botón Administrador de nombres.
Si te equivocaste en el nombre del rango o seleccionaste un grupo de celdas incorrecto, el Administrador de nombres te permitirá hacer cualquier modificación haciendo clic en el botón Editar.
Crear listas desplegables dependientes
Ahora que ya tenemos listos nuestros rangos nombrados podemos crear las listas desplegables. Para eso iré a una nueva hoja de mi libro de Excel, seleccionaré la celda A2 e iré a la ficha Datos > Herramientas de Datos > Validación de datos. En el cuadro de diálogo elegiré la opción Lista y en el cuadro Origen colocará el valor “=Paises” que es el nombre del rango que contiene la lista de países únicos.
Al hacer clic en el botón Aceptar podremos comprobar que la celda A2 contiene una lista desplegable con los países.
Ahora crearemos la lista desplegable dependiente de la celda B2 y para eso seleccionaré dicha celda e iré a la ficha Datos > Herramientas de datos > Validación de datos. En el cuadro de diálogo mostrado seleccionaré la opción Lista y el en cuadro Origen colocaré la siguiente fórmula:
=INDIRECTO(A2)
La función INDIRECTO se encargará de obtener el rango de celdas cuyo nombre coincide con el valor seleccionado en la celda A2.
Es muy probable que al hacer clic en el botón Aceptar se muestre un mensaje de advertencia diciendo que: El origen actualmente evalúa un error ¿Desea continuar? Este error se debe a que en ese momento no hay un País seleccionado en la celda A2 y por lo tanto la función INDIRECTO devuelve error, así que solo deberás hacer clic en la opción Si para continuar.
En el momento en que selecciones un país de la celda A2, las ciudades de la celda B2 serán modificadas para mostrar solamente aquellas que pertenecen al país seleccionado.
Con estos pasos hemos crear un par de listas desplegables dependientes en Excel las cuales muestran las ciudades correspondientes a un país determinado.
Limpiar selección de lista dependiente
Las listas dependientes que acabamos de crear en la sección anterior tienen un pequeño inconveniente y es que después de hacer una primera selección de País y Ciudad, al hacer una nueva selección de País, la celda que muestra las ciudades permanecerá con la selección anterior.
Para que me entiendas mejor hagamos un ejemplo sencillo. Seleccionaré el país Colombia en la celda A2 y posteriormente en la celda B2 seleccionaré la ciudad Medellín. Hasta ahí todo va bien, pero si ahora selecciono el país México en la celda A2, la celda B2 seguirá mostrando la ciudad Medellín.
Si en ese momento guardamos el libro, tendremos una incongruencia en los datos. La mala noticia es que no existe un comando de Excel para solucionar este problema. La buena noticia es que podemos utilizar código VBA para pedir a Excel que limpie la celda B2 cada vez que haya un cambio en la celda A2. Para agregar el código debemos hacer clic derecho sobre el nombre de la hoja y seleccionar la opción Ver código.
En las listas desplegables mostradas debemos elegir la opción Worksheet y Change tal como se muestra en la siguiente imagen.
El código que debemos pegar en esta ventana es el siguiente:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("B2").Value = ""
End If
End Sub
El evento Worksheet_Change se dispara cada vez que se realiza un cambio en una celda de la hoja. Pero ya que estamos interesados en un cambio de la celda A2, comparamos la variable Target para saber si el cambio proviene de dicha celda. En caso afirmativo, limpiamos el valor de la celda B2.
Si aplicas esta solución a tus archivos, deberás guardarlos como un Libro habilitado para macros de manera que pueda ejecutarse adecuadamente el código VBA.
Agregar datos a las listas desplegables dependientes
Si deseas agregar nuevos datos a las listas desplegables, deberás tener cuidado de mantener las referencias adecuadas en cada uno de los rangos nombrados. Por ejemplo, para agregar una nueva ciudad para México insertaré una nueva fila debajo de la ciudad Guadalajara.
Ahora el país México tiene 4 ciudades en lugar de 3 así que será necesario modificar el rango nombrado para sus ciudades. Para hacer este cambio debemos ir a la ficha Fórmulas y hacer clic en el botón Administrador de nombres. Al abrirse el cuadro de diálogo notarás dos cosas:
- Aunque las ciudades de Perú fueron desplazadas hacia abajo por la inserción de la nueva fila, Excel modificó automáticamente la referencia para indicar que dicho nombre ahora se refiere el rango B18:B20.
- Excel no modificó el rango correspondiente a México y en este momento dicho rango termina en la celda B16 por lo que es necesario que modifiquemos manualmente dicha referencia. Para que todo funcione correctamente debo indicar lo siguiente:
=Datos!$B$14:$B$17
Para ingresar esta nueva referencias puedes seleccionar el nombre México y hacer clic en el botón Editar. Se mostrará un nuevo cuadro de diálogo donde podrás indicar la nueva referencia.
Con este cambio será suficiente para ver la nueva ciudad al momento de seleccionar el país México dentro de las listas desplegables.
Así que, ya sea que vas a agregar nuevas Ciudades o Países deberás poner especial atención a las referencias de los rangos nombrados y deberás editarlas en caso de ser necesario desde el Administrador de nombres.
Puedes descargar el archivo de trabajo que he utilizado en este artículo de manera que puedas comprobar por ti mismo la creación de listas desplegables dependientes en Excel. Practica la creación de este tipo de listas e implementa la misma lógica de solución en tus propios datos.