Listas desplegables dependientes – rangos en filas

El tema de crear listas desplegables dependientes ha sido tratado abundantemente en este blog. Ni por casualidad ni por capricho, sino por ser uno de los temas más consultados por mis lectores.

Un lector, cuyo mail he borrado por error y espero que lea esta nota en algún momento, me comentaba que no lograba crear listas dependientes cuando los valores estaban ubicados en un rango horizontal (filas) en lugar de vertical (columnas) tal como muestro en el ejemplo de esta nota.

Como en el caso anterior, el problema reside en el hecho que la función INDIRECTO sólo funciona con texto.

Cuando queremos crear una lista de valores desplegable en Excel, la opción Lista de validación de datos es la alternativa más fácil.

Veamos el caso. Los nombres de los agentes de cada zona de una empresa aparecen en esta hoja en rangos horizontales

Empezamos por crear el nombre que contiene el rango vertical de las zonas

Ahora asignamos la primer celda de la cada celda del la columna C al nombre de cada zona. Es decir

Norte = $C$3
Sur=$C$4
Este=$C$5
Oeste=$C$6

En la celda B9 de la misma hoja ponemos la lista desplegable de las zonas

En la celda B10 creamos la lista desplegable dependiente de los agentes con esta fórmula

=DESREF(INDIRECTO($B$9),0,0,1,CONTARA(INDIRECTO(FILA(INDIRECTO($B$9))&":"&FILA(INDIRECTO($B$9))))-1)

Como ven, combinamos INDIRECTO y FILA para "construir" el texto que representa el rango de los nombres de cada zona, en forma dinámica.
Esta solución tiene un grave problema: si ponemos las listas desplegables en otra hoja, la fórmula no funcionará correctamente. Aquí nos enfrentamos con dos problemas:

1 – tenemos que crear una referencia a la hoja que contiene los valores de las listas (en nuestro ejemplo, la Hoja1)

2 – en Validación de datos no podemos usar referencias a hojas remotas directamente; tenemos que "encapsularlas" en un nombre.

Por estos motivos creamos el nombre "lista_dependientes" que se refiere a esta fórmula:

=DESREF(INDIRECTO(Hoja2!$B$3),0,0,1,CONTARA(INDIRECTO("Hoja1!"&FILA(INDIRECTO(B3))&":"&FILA(INDIRECTO(B3))))-1)

En esta fórmula creamos el texto de la referencia en la función CONTARA poniendo en forma explícita el nombre de la hoja que contiene las listas de valores (Hoja1).

Ahora podemos ser fieles al principio de separar los datos de los reportes y usar la lista dependiente en la Hoja2

El archivo del ejemplo se puede descargar 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.

Autofiltro en Excel 2007

La funcionalidad Autofiltro ha sido mejorada en forma significativa en Excel 2007 en comparación al Excel Clásico (97-2003). Veamos las diferencias cuando queremos filtrar una lista usando fechas como criterio.

Supongamos esta lista de fechas de nacimientos

Como ven, hemos aplicado Autofiltro. Queremos filtrar la lista para que muestre sólo los nombres de las personas nacidas entre el 01/01/1962 y el 31/12/1963 (es decir, entre los años 1962 y 1963).

Si usamos Excel 2007 la tarea es bien sencilla. El diálogo del Autofiltro nos muestra las fechas disponibles ya agrupadas por años

Haciendo clic en el año podemos ver y elegir algún mes en particular

A su vez, haciendo un clic en el mes podremos ver los días

Esto nos permite realizar con facilidad nuestra tarea de filtrar dejando visibles sólo los nacidos en los años 1962 y 1963

y ya está!

En Excel 2003 la tarea es menos trivial. La lista desplegable no nos ofrece ningún nivel de agrupación de los datos

En Excel 2003 usamos criterios personalizados

Otro aspecto interesante en Excel 2007 es que al reconocer el tipo de datos de la columna de filtrado, el menú nos ofrece las alternativas relevantes

En cambio si queremos filtrar según la columna de Nombres, veremos en el menú "Filtro de Texto" en lugar de "Filtro de Fechas"

Otra posibilidad que se ha agregado en Excel 2007 es la posibilidad de filtrar (y también ordenar) por color

Personalmente no creo que sea una buena idea utilizar colores como datos (o "meta-datos"). Colores similares pueden parecer idénticos a la vista sin serlo de hecho.

Posición del objeto Calendario en una hoja de Excel

De vuelta mis vacaciones estoy tratando de ponerme al día con las consultas de mis lectores. Lamentablemente no podré responder a todas, pero algunas serán transformadas en notas en el blog. Como la consulta de un lector sobre cómo posicionar el objeto Calendario en una hoja de Excel.

La consulta viene a colación de la nota sobre validación de fechas en Excel que publiqué hace ya casi tres años. Allí mostré como hacer que el objeto Calendario aparezca cuando activamos una celda de un determinado rango.

La consulta de mi lector consiste en cómo lograr que el calendario aparezca siempre a la derecha de la celda activa y no en el centro del área de la hoja que aparece en pantalla. Para lograr esto tendremos que programar la propiedad Position del Userform que contiene el calendario.

Empezamos por descargar el ejemplo de la nota mencionada. Luego activamos el editor de Vba (Alt+F11) y el Userform1. En la ventanilla de las propiedades activamos la pestaña Categorized y vamos al grupo Position

Vemos que tenemos, además del tamaño (Height y Width) la posibilidad de establecer la posición inicial (StartUpPosition)

En nuestro caso vamos a usar la opción 0-Manual. Por supuesto, esto no es suficiente y tendremos que establecer la posición relativa a la celda activa programáticamente.

Activamos el Modulo1 que contiene la rutina "abrir_calendario" y ponemos este código

Sub abrir_calendario()
    With UserForm1
        .Left = ActiveCell.Left + ActiveCell.Width + 25
        .Top = ActiveCell.Top + 75
        .Show
     End With
    
End Sub

El código es bastante obvio y se puede apreciar cómo se controla la posición en relación a la celda activa. Los parámetros "+25" y "+75" nos permiten establecer el grado de desvío.

El archivo se puede descargar gratuitamente aquí.