Las técnicas que mostrado hasta ahora se basan en la creación de nombres definidos que se refieren a rangos dinámicos. La ventaja de esta técnica es que no requiere conocimientos de Vba (el lenguaje de programación de Excel); todo lo hacemos usando rangos en hojas y fórmulas. La desventaja es que a medida que nuestro modelo va creciendo en variables debemos agregar nombres y definir nuevos rangos dinámicos.
Por ejemplo, en la nota mencionada manejamos nombres de ciudades para cinco países
Para lo que debemos crear seis nombres definidos. Si decidimos agregar otro país, debemos crear el nombre correspondiente. Esto nos limita si creamos un modelo que debe ser usado por un usuario común o si no queremos que nuestros usuarios modifiquen el modelo.
La solución es usar Vba para crear las listas que alimentan las listas desplegables.
A los efectos supongamos que queremos crear un modelo donde el usuario elige un país, de acuerdo a su elección una lista desplegable le muestra sólo las ciudades de ese país y en una tercera celda se muestra la población de esa ciudad
El modelo funciona de la siguiente manera:
En la hoja “lista” tenemos una tabla de Países-Ciudades-Población
En la columna E de la hoja ponemos los países que aparecen en la tabla Países-Ciudades-Población. Esta lista debe contener valores únicos, cosa que podemos hacer con Filtro Avanzado. Pero en este modelo usaremos un poco de código Vba con el objeto Collection
Sub insertarPaises()
Dim arrListaPaises As New Collection, pais
Dim iR As Long
On Error Resume Next
Range("lstPais").ClearContents
On Error GoTo 0
With Sheets("lista")
On Error Resume Next
For Each pais In Range("tblPaises")
arrListaPaises.Add pais, pais
Next pais
On Error GoTo 0
For iR = 1 To arrListaPaises.Count
.Cells(iR + 1, 5) = arrListaPaises(iR)
Next iR
End With
End Sub
No entraremos en los detalles técnicos, pero podemos ver que usamos un rango definido por el nombre “tblPaises”. Este es un rango dinámico que se refiere al rango en uso en la columna A de la hoja “lista”
tblPaises=lista!$A$2:INDICE(lista!$A:$A,CONTARA(lista!$A:$A))
Con este código creamos una nueva “colección” de valores (países) haciendo un rizo a lo largo del rango de los países. Como no se puede agregar un valor ya existente en la colección, usamos On Error Resume Next para que el código no se interrumpa al tratar de agregar un país existente. De esta manera obtenemos una lista de valores únicos. Luego la copiamos al rango lstPais con el loop For…Next
La celda D3 de la hoja “reporte” contiene una validación de datos con la opción Lista usando el nombre definido “lstPais”
El nombre definido “lstPais” se refiere a al fórmula =DESREF(lista!$E$1,1,0,CONTARA(lista!$E:$E)-1,1), que es el rango donde hemos puesto los países con el código mostrado anteriormente.
Para que este rango se actualice automáticamente cuando agregamos o quitamos países y ciudades en la tabla, usamos un evento Deactivate de la hoja “lista”. De esta manera, cuando el usuario vuelve a la hoja reporte luego de efectuar un cambio en la tabla, el evento dispara el código de actualización
En la celda D5 de la hoja “reporte” creamos una lista desplegable con la opción Lista y el nombre
“lstCiudad”
El nombre “lstCiudad” se refiere al rango usado en la columna G que contiene las ciudades del país elegido
Para agregar los nombres de las ciudades del país elegido a este rango, usamos este código
Sub insertarCiudades()
Dim lCounter As Long
Dim rngCel As Range
On Error Resume Next
Range("lstCiudad").ClearContents
On Error GoTo 0
lCounter = 2
With Sheets("lista")
For Each rngCel In Range("tblPaises")
If rngCel.Value = Range("Pais_Elegido").Value Then
.Cells(lCounter, 7) = rngCel.Offset(0, 1)
lCounter = lCounter + 1
End If
Next rngCel
End With
End Sub
En este código usamos nuevamente el rango “tblPaises” para comparar el país elegido (la celda D3 de la hoja “reporte” a la que le hemos definido el nomber “Pais_Elegido”); cuando el país de la tabla Paises-Ciudades-Población coincide con el país elegido, la ciudad es agregada al rango de la columna G.
Este código es disparado por un evento Worksheet_Change de la hoja “reporte”, de manera que cuando se produce un cambio en D3, se dispara el código y la lista de ciudades es actualizada.
El código del evento es el siguiente:
Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Target, Range("Pais_Elegido")).Address = Range("Pais_Elegido").Address Then
Range("Ciudad_Elegida").ClearContents
Call insertarCiudades
End If
End Sub
Finalmente, en la celda D7 usamos una función BUSCARV combinada con SI.ERROR para extraer la población de la ciudad elegida.
El archivo con el ejemplo se puede descargar aquí.
Actualización: nota que explica cómo crear un modelo ampliado (continente-país-ciudad)
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.
