Listas desplegables dependientes – inicialización de celdas con eventos

El tema “listas desplegables dependientes” es uno de los más populares entre los lectores de este blog. Las 17 notas bajo esta etiqueta contienen, al día de hoy, 529 comentarios. Si bien los comentarios son una excelente herramienta de comunicación con mis lectores, muchos aportes y soluciones quedan “enterrados” allí y de hecho no están a disposición de los lectores.

En esta nota expongo el tema de la inicialización de celdas que contienen listas desplegables dependientes, tema que ha sido desarrollado en varios comentarios en distintas notas.

Empecemos por exponer el problema. Supongamos un modelo donde tenemos dos celdas con listas desplegables dependientes (en este caso, con la opción Lista de Validación de Datos)

Como podemos ver, al elegir el continente (América del Norte) en la celda C3, la lista desplegable dependiente en la celda C4 nos muestra sólo los países de ese continente. El problema reside en que al elegir otro continente, el país elegido anteriormente queda en la celda hasta que sea reemplazado.

Esto puede generar errores por lo que necesitamos que al cambiar la elección del continente, el contenido de la celda C4 sea borrado.

Esto lo hacemos programando un evento Worksheet_Change para la hoja que contiene las listas desplegables

Private Sub Worksheet_Change(ByVal Target As Range)
    If Union(Target, Range("celContinente")).Address = _
                            Range("celContinente").Address Then
        Range("celPais").ClearContents
    End If

End Sub

Este evento se dispara cuando se produce un cambio en la celda C3; el código elimina el contenido de la celda C4.

Usamos nombres definidos para las celdas para facilitar la lectura del código y para evitar tener que corregirlo si cambiamos la ubicación de las listas desplegables en la hoja

• celContinente =eleccion!$C$3
• celPais =eleccion!$C$4

Otra variante del tema, como me consulta uno de mis lectores, es que exista un valor por defecto cuando se elige el continente.

Para que en la celda “país” aparezca el primer país en la lista del continente elegido ponemos este evento Worksheet_Change en la hoja correspondiente

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strRangeName As String
 
    If Union(Target, Range("celContinente")).Address = _
                            Range("celContinente").Address Then
        If Len(Range("celContinente")) = 0 Then
            Range("celPais").ClearContents
            Exit Sub
        End If
     
        Call valDefault
    End If

End Sub

y esta macro en un módulo común del editor Vb

Sub valDefault()
    Dim n As Name
    Dim strRangeName As String
 
    strRangeName = WorksheetFunction.Substitute(Range("celContinente"), " ", "_")

    Range("celPais") = WorksheetFunction.Index(Range(strRangeName), 1)
     
End Sub

En esta macro es necesaria ya que al crear los nombres que se refieren a los rangos con las ciudades, los espacion entre las palabras han sido reemplazados por “_” (por ejemplo, América del Sur se transforma en America_del_Sur). Lo mismo hemos hecho en la regla de validación de datos, como ya hemos explicado en notas anteriores

En este modelo, al borrar el contenido de la celda C3 (continente), se elimina automáticamente el valor de la celda C4 (país); al elegir un continente, aparece el primer país en la lista correspondiente; el país puede ser cambiado luego.

Para que aparezca algún otro país como valor por defecto, la posibilidad más obvia es ponerlo en el primer lugar de la lista.

El cuaderno con el ejemplo 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.

Listas desplegables con contenido condicional.

Supongamos una lista de artículos que contiene dos columnas: el código del artículo y el estatus (activo o inactivo). ¿Cómo hacemos para crear una lista desplegable que muestre solamente los artículos activos?

Con anterioridad a la introducción de las “listas” en Excel 2003, luego rebautizadas “tablas” en Excel 2007/10, lo hubiera hecho con una macro. Posiblemente extrayendo los valores que cumplen con el criterio usando Filtro Avanzado y creando un nombre que se refiera a ese rango dinámicamente. Finalmente, usaríamos una macro para automatizar el proceso.

Pero podemos aprovechar las funcionalidades de las tablas para crear un modelo sin macros.

Supongamos que esta es nuestra lista

Vamos a usar una variante de la técnica que mostré en la nota que trató sobre cómo agregar valores únicos a una lista desplegable.

Empezamos por convertir la matriz en “tabla”

Ahora agregamos una columna auxiliar (“Aux1”) con esta fórmula

=SI(B2="Activo",A2,"")

El próximo paso es crear la columna “Aux2” que contiene la fórmula

=SI(CELDA("contenido",C2)="","",FILA())

Lo que hace esta fórmula es evaluar si el resultado de la fórmula es vacío (la celda no está vacía; contiene una fórmula); en caso afirmativo da un resultado vacío, en casi negativo muestra el número de código del artículo.

En esta columna obtenemos un número de orden para los artículos con estatus “activo”.

Finalmente creamos la columna auxiliar “Lista” donde aparecen los artículos activos ordenados por orden de aparición en la tabla. Esto lo hacemos con la fórmula

=INDICE($C$2:$C$15,COINCIDIR(K.ESIMO.MENOR($D$2:$D$15,FILA()-1),$D$2:$D$15))

Como estamos trabajando con una tabla, al agregar un nuevo artículo todas las fórmulas son copiadas automáticamente.

Lo último que nos queda por hacer es crear una nombre que se refiera dinámicamente al rango de la columna Lista en la tabla que no contiene valores #NUM!.

En el administrador de nombres definimos el nombre “ListaArticulos” que se refiere a la fórmula

=DESREF(articulos!$E$2,0,0,SUMAPRODUCTO(--NO(ESERROR(Tabla1[Lista]))),1)

Ahora podemos crear la lista desplegable usando Validación de Datos—Lista

Este video muestra como se adapta el contenido de la lista desplegable a los cambios en la tabla



El archivo con el 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.

Listas desplegables dependientes con combobox

Algunos de mis memoriosos lectores recordarán seguramente las notas sobre listas desplegables dependientes y listas desplegables dependientes múltiples que publiqué hace ya más de seis años la primera y casi cuatro años atrás la segunda.

Ambas fueron muy populares, con más de 120000 vistas y 250 comentarios. Hasta hoy en día sigo recibiendo comentarios y consultas relacionados con el tema de las notas. Una de las consultas más corrientes es como construir el modelo pero usando el control combobox en lugar de validación de datos, que es la técnica que muestro en esas notas.

Hay varias razones para usar el control combobox en lugar de listas desplegables de validación de datos. Una de ellas es la posibilidad de usar la propiedad de autocompletar del combobox. Otra es el hecho de que con validación de datos, la opción se hace evidente sólo cuando se elige la celda que la contiene. Sólo cuando seleccionamos la celda que contiene la lista veremos la flecha que nos permite desplegar la lista de opciones. En cambio el cuadro combinado (combobox) es un objeto visible permanentemente.

Empecemos por recordar que Excel cuenta con dos colecciones de objetos que pueden ser incluidos en una hoja: controles de formulario y controles ActiveX

Los controles de Formulario son más fáciles de implementar, pero no nos sirven para nuestro modelo ya que no aceptan nombres que se refieren a rangos y tampoco se pueden programar.

El primer paso es crear los nombres definidos que alimentan las listas desplegables. A los efectos de este ejemplo, los rangos serán estáticos. En un modelo más avanzado crearíamos nombres definidos con rango dinámicos.

La forma más práctica de crear nombres definidos con rangos estáticos es usar la funcionalidad “Crear desde la selección”

En la hoja “Ciudades” los rangos tienen tamaños distintos por lo que usaremos un pequeño truco para no tener que definir cada nombre por separado. En la hoja “Ciudades” tenemos de hecho una tabla donde los nombres de los países figuran en la primer fila. El proceso es el siguiente:

1. Elegimos una de las celdas de la tabla (en nuestro ejemplo A1)
2. Apretamos Ctrl + * para seleccionar todo el rango de la tabla
3. Accionamos F5 (Ir A), apretamos el botón Especial y seleccionamos la opción Constantes-Texto. Esto hace que sólo las celdas que contienen los nombres de las ciudades sean seleccionadas.
4. Finalmente usamos “Crear desde la selección-Fila superior”

Este video muestra el proceso



Ahora tenemos que insertar los controles en la hoja. Elegimos el control cuadro combinado (combobox) de la colección ActiveX y lo insertamos en la hoja

Al insertar el objeto en la hoja se activa al modo Diseño; en este estado, con el sontrol seleccionado, abrimos el menú Propiedades para definir dos propiedades el control: Linked Cell y ListFIllRange

LinkedCell es la celda donde aparecerá el resultado de la elección; en este control ponemos A2. ListFillRange es el rango que contiene los valores de la lista desplegable (en nuestro caso el nombre definido Continentes)

Una vez definidas las propiedades apretamos el icono Diseño para pasar a la situación normal de uso del control. Ahora podemos elegir un contienente y éste se registrara en la celda A2

Como explicamos en las notas anteriores, los nombres definidos no aceptan espacios entre las palabras. Por lo tanto usamos la función SUSTITUIR para transformar el resultado del control al nombre definido. En la celda A4 ponemos

=SUSTITUIR(A2," ","_")

Antes de agregar un nuevo control para la lista desplegable de los países definimos el nombre “continente elegido” que se refiere a esta fórmula:

=INDIRECTO(eleccion!$A$4)

En las propiedades del control definimos:

LinkedCell: A6
ListFillRange: continente_elegido

De esta manera la lista de las ciudades depende del continente elegido y el país elegido se registra en la celda A6

Nos resta insertar el control para elegir las ciudades. Empezamos por poner la fórmula

=SUSTITUIR(A6," ","_")

en la celda A8 para transformar la elección del cuadro combinado en el nombre definido que contiene las ciudades del país elegido.
Luego creamos el nombre definido “país_elegido” que se refiere a la fórmula:

=INDIRECTO(eleccion!$A$8)

Ahora definimos las propiedades del control:

LinkedCell: A10 (o cualquier otra celda donde queramos que aparezca la ciudad)
ListFillRange: pais_elegido

Un último toque es programar un evento que limpie el contenido de las combobox cuando se cambia la elección del continente. En el módulo de la hoja del editor Vbe ponemos este código

Private Sub ComboBox1_Change()
    ComboBox2.Value = ""
    ComboBox3.Value = ""
End Sub

Este evento hace que cuando se cambia el valor en la Combobox1 (continents), se borran los valores de las dos restantes combobox.

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.

Lista desplegable con actualización automática

Sigo saldando las deudas con mis lectores y en esta oportunidad cómo crear dinámicamente una lista desplegable con valores únicos.

La situación es la siguiente: creamos una tabla de facturas en una hoja de Excel con los campos “fecha”, “Cliente” y “Total”.

El campo “Cliente” contiene una lista desplegable creada con Validación de Datos. El objetivo es que el usuario introduzca el nombre del cliente eligiéndolo de la lista desplegable.

Para eso necesitamos crear una lista desplegable que se actualice automáticamente cada vez que agregamos un registro con un nuevo cliente en la base de datos.

Supongamos que la los registros únicos de la lista desplegable los ponemos en la columna H (en un modelo real el rango de la lista desplegable estaría en una hoja oculta). Nuestro objetivo es actualizar la lista cada vez que ingresemos un cliente nuevo.

Para actualizar la lista desplegable vamos a programar un evento. Como verán a continuación el código es muy sencillo ya que vamos a usar la herramienta Filtro Avanzado para crear una lista de valores únicos.
Empecemos por señalar que el rango de la base de datos lo definimos como Tabla (Lista en Excel 2003), de manera que la validación de datos del campo Cliente (la columna B) se agregue en forma automática cada vez que agregamos un registro en la base de datos.

Veamos como funciona el modelo:



1 - Creamos un nombre que se refiere a un rango dinámico con la fórmula

=DESREF('base de datos'!$H$1,1,,CONTARA('base de datos'!$H:$H))

Esta fórmula genera un rango dinámico donde el último valor del rango es una celda vacía. Esto es necesario para evitar que la validación de datos nos impida ingresar un cliente que aún no encuentra en la lista de clientes. Obviamente, esto cancela la validación de datos, ya que al haber una celda vacía todo valor que ingresemos será valido, pero nos permite crear el efecto de actualización automática.

2 - Programamos un evento de hoja Worksheet_SelectionChange con este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("C:C")).Address = Range("C:C").Address Then
        Range("H:H").ClearContents
        Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
                            CopyToRange:=Range("H1"), Unique:=True
    End If

End Sub

En este código el evento se produce cuando seleccionamos una celda en la columna C, es decir, después de haber ingresado el cliente en la columna B.

Si queremos que la lista desplegable aparezca ordenada alfabéticamente podemos en el evento una llamada a este código (que va en un módulo común del Vbe)

Sub ordenar_clientes()

    Range("H1").CurrentRegion.Sort Key1:=Range("H2"), _
                Order1:=xlAscending, Header:=xlYes _
                , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal

End Sub

El código del evento en el módulo de la hoja Base de datos quedaría

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("C:C")).Address = Range("C:C").Address Then
        Range("H:H").ClearContents
        Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
                            CopyToRange:=Range("H1"), Unique:=True
    End If

    Call ordenar_clientes
  
End Sub

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.

Listas desplegables con valores dependientes

Supongamos que queremos crear un formulario en Excel con dos celdas: hora de comienzo y hora de finalización. Las horas las elegimos de celdas desplegables, que creamos fácilmente con validación de datos. Pero en nuestro modelo queremos que las horas que aparezcan en la la celda “hora de finalización” dependan de la hora elegida en la celda “hora de comienzo

Para lograr que la lista desplegable de la celda “Hasta” se adapte a la hora introducida en la celda “De” tenemos que usar rangos dinámicos que crearemos con la función DESREF.

Los pasos para crear este modelo:

En el rango A3:A26 definimos las horas del día (en este ejemplo usamos una escala de una hora, pero podemos usar medias horas o cualquier otra escala). Definimos el nombre “rngHorarioSencillo” que se refiere a este rango.

Definimos el nombre “celdaDeSencillo” que se refiere a la celda E4.

En la celda C8 ponemos la fórmula

=COINCIDIR(celdaDeSencillo,rngHorarioSencillo,0)

y creamos el nombre “controlHora2Sencillo” que se refiere a la celda C8. Como alternativa podemos crear un nombre que contenga la fórmula en lugar de ponerla en una celda. En mi opinión, el uso de celdas nos permite crear modelos más claros y fáciles de administrar.

Para crear la lista desplegable de la celda “De” usamos el nombre “rngHorarioSencillo”

Para crear la lista desplegable en la celda “Hasta” usamos un rango dinámico definido por esta fórmula:

=DESREF(sencillo!$A$3,controlHora2Sencillo,,CONTARA(rngHorarioSencillo)-controlHora2Sencillo)

Otro detalle que podemos agregar es un evento que borre el contenido de la celda “Hasta” cuando cambiamos el valor de la celda “De”. En el módulo Vbe de la hoja ponemos este código

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("celdaDeSencillo").Address Then
        Target.Offset(0, 1).ClearContents
    End If
End Sub


Finalmente ocultamos las columnas A y B.


Un caso más complejo es cuando queremos que el modelo incluya varias líneas donde la hora de comienzo de una línea depende de la hora de finalización de la línea anterior

En este caso emplearemos prácticamente la misma técnica, pero en lugar de celdas de control incluiremos las fórmulas en nombres.

En la primer celda del formulario definimos la lista desplegable con el nombre “rngHorario”, que se refiere a todo el rango de horas

Ahora tenemos que definir los controles para las celdas “De” y “Hasta”. En el caso de “De” tenemos que referirnos a la celda “Hasta” inmediata anterior (la que se encuentra una fila por arriba). Esto lo hacemos definiendo la fórmula en un nombre pero asegurándonos que la referencia a la fila sea relativa (sin el signo $).

Al definir el control para el campo “De” seleccionamos la celda D5 y definimos el nombre “hora1Ref” con esta fórmula

=COINCIDIR(complejo!$E4,rngHorario,0)

Para definir el control para el campo “Hasta” seleccionamos previamente la celda E4 y definimos el nombre “hora2Ref” con esta fórmula

=COINCIDIR(complejo!$D4,rngHorario,0)

Estos controles nos sirven ahora para definir los rangos dinámicos de las listas desplegables.

Para las celdas D5:D9 definimos el nombre “rngHora1Comp” con la fórmula

=DESREF(complejo!$A$3,hora1Ref,,CONTARA(rngHorario)-hora1Ref)

Para las celdas E4:E9 definimos el nombre “rngHora2Comp” con la fórmula

=DESREF(complejo!$A$3,hora2Ref,,CONTARA(rngHorario)-hora2Ref)

El archivo con el ejemplo 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.

Activar una macro desde un cuadro combinado (ComboBox)

En la nota anterior vimos como activar una macro a partir de la elección en una lista desplegable. Esta lista fue creada con validación de datos.

Podemos también crear listas desplegables usando el control cuadro combinado (ComboBox). Pero en ese caso tendremos que usar técnicas un poco distintas de las que mostramos ayer.

Empecemos por señalar que hay dos tipos de controles: los de la barra de formularios y los ActiveX (tema que ya fue tratado en este blog). Los controles de la barra de formularios son fáciles de usar pero no pueden ser programados. Los ActiveX al poder programarlos son más flexibles, pero requieren un poco más de conocimientos.

Siguiendo con el ejemplo de la nota anterior, veremos cómo implementarlo con ambos tipos de controles.


Cuadro combinado de la barra de formularios.

Empezamos por crear las tres macros en un módulo común del editor de Vba

Sub dia()
    MsgBox "Buenos dias"
End Sub
Sub tarde()
    MsgBox "Buenas tardes"
End Sub

Sub noche()
    MsgBox "Buenas noches"
End Sub

Luego creamos una lista con los nombres de las macros y le asignamos un nombre (en este caso usamos el cuadro de nombres para crearlo)

El próximo paso es pegar el cuadro combinado y definir sus propiedades. En la pestaña Programador

Una vez creado abrimos el menú de propiedades del control apretando el botón derecho del mouse.

En el rango de entrada ponemos el nombre que se refiere al rango con la lista de macros y en la casilla de vincular con celda ponemos alguna celda, no necesariamente en la misma hoja y no necesariamente visible

Ahora podemos ver que al elegir la opción "dia", la celda vinculada recibe el valor 1 (ya que "dia" es el primer elemento de la lista). Si elegimos "tarde" la celda vinculada recibe el valor 2.

El paso siguiente es crear una macro lo que haremos abriendo el menú contextual del control (clic con el botón derecho) y eligiendo la opción Asignar macro y apretando el botón Nuevo

En el editor de Vba veremos que Excel ha creado una macro, sin código en esta etapa

En esa macro ponemos este código

Sub Listadesplegable1_AlCambiar()
    Dim intCaseSel As Integer

    intCaseSel = Range("C8")

    Select Case intCaseSel
        Case 1: Call dia
        Case 2: Call tarde
        Case 3: Call noche
    End Select
      
    
End Sub


Cada vez que un valor del cuadro combinado es elegido, la instrucción Select activa la macro correspondiente según el valor de la celda vinculada al control.


Cuadro combinado ActiveX

Empezamos seleccionando el cuadro combinado de los controles ActiveX

y pegándolo en algún lugar de la hoja. En el momento en que terminamos de pegar el control, la hoja pasa al modo "Diseño", lo que nos permite definir las propiedades del control. Abrimos el menú de propiedades (no el de formato!) y definimos el rango de los valores de la lista (ListFillRange)

En el próximo paso abrimos el menú Ver código. Excel crea un evento Change para el control,

donde ponemos este código

Private Sub ComboBox1_Change()
    Application.Run ComboBox1.Value
End Sub

Nótese que el código está en el módulo de la hoja que contiene el control. El evento usa el método Run con el valor elegido en el cuadro combinado (ComboBox1.Value) para activar la macro adecuada.

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.

Activar una macro desde una lista desplegable

En este blog hemos tratado extensivamente el tema de las listas desplegables. Pero siempre queda un detalle más a tratar, como la consulta de un lector sobre cómo accionar una macro a partir de la elección efectuada en una lista desplegable.

La idea es tener una lista desplegable en una celda creada con validación de datos de la cual podemos elegir tres valores. Según la elección, la macro correspondiente es accionada.

Vamos a ejemplificarlo con un modelo absolutamente inocuo, pero útil para los propósitos de la explicación. Programamos tres macros: "dia", "tarde" y "noche". Cuando accionamos la macro "dia" aparece en pantalla el mensaje "Buenos días"; cuando accionamos la macro "tarde", aparece en pantalla el mensaje "Buenas tardes". La tercer opción la dejo librada a la imaginación de mis perspicaces lectores.



En este modelo usamos los siguientes elementos:

1. una lista desplegable con los nombres de las macros; la lista la creamos con validación de datos-lista
2. tres macros: "dia", "tarde" y "noche"
3. un evento de tipo Worksheet_Change que ponemos en el módulo de la hoja correspondiente (en nuestro caso la hoja donde está la lista deslegable)-

Los pasos:

• Creamos la lista desplegable

• Creamos las macros

Nótese que las macros van en un módulo común del editor de Vba.

• Creamos el evento

El código del evento

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strToCall As String
 
    strToCall = Range("C2").Value
 
    On Error Resume Next
    If Target.Address = "$C$2" Then Application.Run strToCall
    On Error GoTo 0
 
End Sub

debe ir en el módulo de la hoja. La función del evento es accionar la macro elegida cuando cambia el valor de la celda C2 que contiene la lista desplegable.
La instrucción On Error evita que la macro se detenga con un error cuando el usuario borra el valor de la celda C2 y ésta queda vacía.

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.

Rangos dinámicos y funciones volátiles

En los comentarios de la nota anterior surgió el asunto de los rangos dinámicos y las funciones volátiles. A pesar de que estos temas han sido tratados de alguna manera en este blog, haremos una reseña en esta nota para aquellos lectores que no conocen el tema.

Empecemos por lo básico: ¿qué es un rango dinámico? El rango A1:A5 en la fórmula =SUMA(A1:A5) es estático.

Si agregamos una fila dentro del rango, Excel lo ajustará automáticamente convirtiendo la fórmula a =SUMA(A1:A6).

Pero si insertamos la nueva fila entre la fila 5 y 6 de nuestro ejemplo, Excel no ajustará el rango de la fórmula

Para superar este problema podemos convertir el rango A1:A5 de la fórmula en dinámico usando rangos nominados ("nombres"). Seleccionamos la última celda del rango (A5 en nuestro ejemplo) y usamos como referencia en un nombre que llamaremos "ultima_celda" (sin acentos y sin espacios en blanco que nos aceptables en nombres) la celda inmediata superior (A4 en nuestro ejemplo)

Hay que prestar atención a que la referencia es relativa (la dirección de la celda no incluye las "anclas" $)

Ahora podemos escribir nuestra fórmula de esta manera:

Otro tipo de situación se presenta cuando queremos que el rango de una lista se extienda o contraiga de acuerdo a la cantidad de elementos (celdas ocupadas) que la componen. Un ejemplo es cuando creamos una lista de valores para una lista desplegable con validación de datos.

La receta clásica es usar la función DESREF como en este ejemplo

DESREF crea un rango empezando en el "ancla" (la celda A1 en nuestro caso) y usando la función CONTARA para determinar cuántas filas están incluidas en él.

Para crear listas desplegables dependientes usamos la función INDIRECTO.

Aquí comienza la cuestión con las funciones volátiles. ¿Qué es una función volátil?

No todas las fórmulas de una hoja son recalculadas cada vez que introducimos un cambio. Excel determina qué celdas serán afectadas por el cambio y cuáles no y sólo recalcula las primeras.

Sin embargo hay algunas funciones que son recalculadas con cualquier cambio en la hoja. Estas son las funciones volátiles. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones.

Entre las funciones volátiles de Excel se cuentan, justamente, DESREF e INDIRECTO.

Podemos construir rangos dinámicos sin usar DESREF e INDIRECTO con estas técnicas:

1 – si usamos la versión 2003 de Excel o posteriores, la mejor alternativa en mi opinión es usar Listas (Excel 2003, Tablas en Excel 2007/2010). No nos complicamos la vida con fórmulas complejas que a veces pueden producir errores inadvertidos y le dejamos que todo el trabajo lo haga Excel por detrás de las bambalinas.

2 – Usar una combinación de INDICE y CONTARA:

=Hoja1!$A$1:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A))

Finalicemos la nota diciendo que la influencia de las funciones volátiles en una hoja de Excel será significativa sólo cuando se haga uso intensivo de ellas, algo similar a lo que ocurre con las fórmulas matriciales. Por eso, y como en muchos otros aspectos de la vida, las cuestiones no son absolutas. Usar funciones volátiles no es ni buena ni mala práctica; todo es una cuestión de cantidad.

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.