En la nota anterior sobre
listas desplegables dependientes mostramos cómo crearlas con validación de datos.
En esa nota vimos como crear una lista desplegable de países y ciudades. Una vez elegido el país, la segunda lista desplegable mostraba sólo ciudades de ese país.
Algunos lectores me consultan como hacer lo mismo pero con más de dos niveles de dependencia. Por ejemplo, continentes-países-ciudades.
La técnica es básicamente la misma. Incluimos las listas en rangos nominados (dentro de nombres, usando Insertar-Nombres-Definir) y luego usamos validación de datos con la opción Lista y en Origen usamos fórmulas con la función INDIRECTO.
Supongamos este cuaderno con cuatro hojas
En la hoja Continentes tenemos una lista de los continentes
Estos valores nos servirán como referencia a los nombres que contendrán la lista de países de cada continente. Definimos el rango A1:A7 dentro del nombre "continente"
continente=Continentes!$A$1:$A$7
En la hoja Países creamos campos con las listas de los países de cada continente. Por comodidad (la mía) he puesto sólo dos países por continente
Finalmente ponemos listas de las ciudades por países en la hoja Ciudades
Ahora tenemos que definir los nombres. Una forma de ahorrar tiempo es usar el método Insertar-Nombre-Crear. En la hoja Países seleccionamos la celda A1 y abrimos el menú Insertar-Nombre-Crear
Como queremos que Excel use la fila superior como rótulo para los nombres, señalamos la opción "crear nombres en fila superior".
El resultado será:
África=Paises!$A$2:$A$3
América_del_Norte=Paises!$B$2:$B$3
América_Central=Paises!$C$2:$C$3
América_del_Sur=Paises!$D$2:$D$3
Asia=Paises!$E$2:$E$3
Europa =Paises!$F$2:$F$3
Oceanía=Paises!$G$2:$G$3
Como verán Excel ha agregado "_" en los casos que el nombre del continente está formado por más de una palabra. Esto se debe a que por definición los nombres en Excel no pueden tener espacios en blanco (ni símbolos especiales).
Usamos el mismo método para definir los nombres en la hoja Ciudades, con este resultado
Congo =Ciudades!$A$2:$A$61
Canadá =Ciudades!$B$2:$B$61
México=Ciudades!$C$2:$C$61
Venezuela=Ciudades!$D$2:$D$61
China=Ciudades!$E$2:$E$61
Alemania=Ciudades!$F$2:$F$61
Australia=Ciudades!$G$2:$G$61
Angola =Ciudades!$H$2:$H$61
Estados_Unidos=Ciudades!$I$2:$I$61
Guatemala=Ciudades!$J$2:$J$61
Colombia=Ciudades!$K$2:$K$61
Vietnam=Ciudades!$L$2:$L$61
Francia =Ciudades!$M$2:$M$61
Islas_Fidji=Ciudades!$N$2:$N$61
Como ven, todos los nombres tiene el mismo tamaño de rango (de la fila 2 a la 61), lo que hará que en la lista desplegable aparezcan espacios en blanco. Más adelante veremos como solucionar este problema.
Ahora que hemos definido todos los nombres, definimos las listas desplegables en la hoja "Elección"
En la celda B1 definimos la lista con la opción Lista y la fórmula "=continente". Esto crea una referencia al rango que contiene los nombres de los continentes
En la celda B2 creamos la lista de países que será dependiente del continente elegido en la celda B1
En este caso creamos la referencia al rango usando la función INDIRECTO. Además tenemos que usar la función SUSTITUIR para poner las líneas "_" en lugar de los espacios entre las palabras, para que el valor de la celda coincida con el nombre del rango
=INDIRECTO(SUSTITUIR(B1," ","_"))
Usamos la misma técnica en la celda B3, usando como referencia el valor de la celda B2
Si elegimos el continente América del Sur, podremos elegir sólo Venezuela o Colombia. Si elegimos Colombia podremos elegir una de las ciudades que hemos incluido en la columna Colombia de la hoja Ciudades
Si fuera necesario podríamos agregar más listas dependientes creando los campos adecuados (barrios, jurisdicciones, etc.).
La técnica que hemos mostrado adolece de un defecto estético, los espacios en blanco. Para solucionar este problema tenemos que usar rangos dinámicos. Normalmente usamos la función DESREF para crear rangos dinámicos. El problema en nuestro caso es que la función INDIRECTO solo acepta rangos como argumento.
Para superar este problema usaremos esta fórmula en la creación de la lista desplegable dependiente de la celda B3 (ciudades)
=DESREF(INDIRECTO(SUSTITUIR(B2," ","_")),0,0,CONTARA(INDIRECTO(SUSTITUIR(B2," ","_"))),1)
Ahora la lista dependientes de ciudades no mostrará espacios en blanco.
La fórmula fue tomada del excelente sitio
Contextures de Debra Dalgleish.
El cuaderno con el ejemplo y las fórmulas puede descargarse
aquí.
Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.
Aquí pueden descargar el cuaderno