Lista desplegable con contenido condicional - versión con Vba

Supongamos este escenario:

tenemos una lista que asocia nombres con valores

donde los nombres se repiten.

Queremos crear una lista desplegable que muestre los valores asociados al nombre que eliljamos en la celda G2 de este ejemplo

Vamos a mostrar como está construido este modelo.

Comenzamos por mostrar la columna A que están oculta. Esta columna contiene un rango dinámico donde ponemos los valores asociados al nombre que aparece en la celda G2.

El rango D1:E14 que contiene los nombres y sus valores está definido como Tabla.
La columna A contiene los valores asociados al nombre introducido en la celda G2.
Para poner estos valores usamos un evento de tipo Change

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$G$2" Then
        Range("tblNombreValor[#All]").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("A1"), Unique:=False
    End If
 
End Sub

El código usa la funcionalidad Filtro Avanzado (Datos - Ordenar y Filtrar - Avanzadas) que nos permite filtrar una tabla y copiar los resultados a otro rango.

Definimos un nombre que se refiere a este rango dinámicamente con esta fórmula

=Hoja1!$A$2:INDICE(Hoja1!$A:$A,CONTARA(Hoja1!$A:$A))

Ahora definimos una validación de datos en la celda H2 con la opción Lista y el nombre definido ListValores en la casilla Origen

Con esto concluimos la construcción del modelo. El archivo del ejemplo se puede descargar aquí.

En cuanto al formato condicional para señalar las filas del nombre introducido en G2, usamos la opción Formula con esta definición

Validación de datos con criterios múltiples

Un lector me consulta como aplicar validación de datos con más de un criterio. Por ejemplo, que en un rango determinado se pueda ingresar valores entre 1 y 10 y que no haya duplicados.

Antes de mostrar la solución, es importante señalar el punto central que el mecanismo de Validación de Datos funciona con fórmulas que dan como resultado VERDADERO o FALSO. Para que el valor a introducir sea aceptado, la fórmula de la validación debe evaluar a VERDADERO.

En nuestro ejemplo queremos que las dos condiciones se cumplan simultáneamente. Para eso debemos usar la función Y. Si queremos aplicar la validación de datos múltiple en el rango B3:B10 usaremos la fórmula

=Y(CONTAR.SI($B$3:$B$10,B3)<2,Y(B3>=1,B3<=10))

Esta fórmula funciona de la siguiente manera:

CONTAR.SI($B$3:$B$10,B3)<2 evalúa si el valor a introducir aparecerá más de una vez. Si el valor ya aparece en el rango, el resultado de esta fórmula es FALSO.

Y(B3>=1,B3<=10)) verifica que el valor a introducir sea mayor o igual a 1 y menor o igual a 10.

Finalmente, la función Y al principio de la fórmula combina ambos resultados dando VERDADERO solamente si ambas condiciones se cumplen.

Si queremos evitar valores duplicados y los valores permitidos van de 1 a 10 y de 20 a 25, es decir, dos intervalos de números, ampliamos nuestra fórmula con la función O

=Y(CONTAR.SI($B$3:$B$10,B5)<2,O(Y(B5>=1,B5<=10),Y(B5>=20,B5<=25)))

La función O da VERDADERO si alguna de las funciones Y que son sus argumentos da VERDADERO.

Cuando las flechas de validación de datos en Excel no aparecen

Cuando definimos en una celda validación de datos con la opción Lista, al seleccionarla aparece una flecha que permite desplegar la lista

¿Qué hacer cuando a pesar de haber definido todo correctamente la flecha no aparece al seleccionar la celda, como le sucedió a una de mis lectoras?

El "primer sospechoso" es que hayamos quitado la marca en "Celda con lista desplegable" en la definición de la validación de datos

Otra posibilidad es que hayamos activado la opción de ocultar objetos. Para comprobar esto podemos activar el "Panel de selección" en Diseño de página—Activar. En el panel podemos ver el estado del objeto (en esta animación "Drop Down 2)

También podemos revisar las definiciones de Excel en Archivo—Avanzadas

Si se ha seleccionado la opción Nada, todos los objetos serán invisibles, incluidas las flechas de validación de datos.

Finalmente, como con algunos gobiernos, existe la posibilidad de que se trate de un archivo corrupto. En este caso después de cerrar el archivo podemos abrirlo usando la opción "Abrir y reparar"

Listas desplegables dependientes múltiples con Vba

Esta es una extensión de mi nota anterior sobre listas desplegables dependientes. Uno de mis lectores pregunta

Como se haría para que tenga más niveles, por ejemplo 5 listas desplegables dependientes

Podemos extender nuestro modelo a casi todo número de niveles de dependencia introduciendo ciertos cambios. A los efectos del ejemplo extenderemos el modelo a tres niveles: continente – país – ciudad.

El modelo extendido puede descargarse aquí.

Como en el modelo de la nota anterior, creamos una lista de continentes a partir de la base de datos, usando el objeto Collection e insertando la lista de valores únicos en un rango auxiliar. Este rango es el origen de la lista desplegable creada con Validación de datos

La lista la creamos con este código

Sub insertarContinentes()
    Dim arrListaContinentes As New Collection, continente
    Dim iR As Long
 
    On Error Resume Next
    Range("lstContinentes").ClearContents
    On Error GoTo 0
 
    With Sheets("lista")
        On Error Resume Next
            For Each continente In Range("tblContinentes")
                arrListaContinentes.Add continente, continente
            Next continente
        On Error GoTo 0
        For iR = 1 To arrListaContinentes.Count
            .Cells(iR + 1, 6) = arrListaContinentes(iR)
        Next iR
    End With
        
End Sub

Para que esta lista esté permanentemente actualizada, programamos un evento Worksheet_Deactivate de la hoja que contiene los datos (la hoja “lista”)

Private Sub Worksheet_Deactivate()
    Call insertarContinentes
End Sub

Para crear la lista de los países correspondientes a los continentes, creamos un código similar al de los continentes, pero con una condición para que los países agregados a la colección sean los que corresponden al continente

Sub insertarPaises()
    Dim rngCell As Range
    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")
                If pais.Offset(0, -1).Value = Range("Continente_Elegido") Then
                arrListaPaises.Add pais, pais
            End If
            Next pais
        On Error GoTo 0
        For iR = 1 To arrListaPaises.Count
            .Cells(iR + 1, 8) = arrListaPaises(iR)
        Next iR
    End With
        
End Sub
El código de la rutina para generar la lista de ciudades queda igual a la del modelo anterior.

El último toque es agregar código en el evento Worksheet_Change de la hoja “reporte” para disparar las rutinas que crean las listas de países y ciudades

Private Sub Worksheet_Change(ByVal Target As Range)
    If Union(Target, Range("Continente_Elegido")).Address = Range("Continente_Elegido").Address Then
        Range("Pais_elegido").ClearContents
        Range("Ciudad_Elegida").ClearContents
        Call insertarPaises
    End If
 
    If Union(Target, Range("Pais_elegido")).Address = Range("Pais_elegido").Address Then
        Range("Ciudad_Elegida").ClearContents
        Call insertarCiudades
    End If
 
End Sub

Por supuesto, hemos creados nombres definidos que se refieren a los rangos del modelo.

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.

Validación de direcciones de e-mail en Excel

Validación de datos es una herramienta excelente, indispensable diría, en todo modelo de Excel que requiera ingresar datos. Pero, como todo en este mundo, tiene sus limitaciones. Por ejemplo, ¿cómo comprobamos que el usuario ingrese direcciones válidas de correos electrónicos?

Si se tenemos una lista de direcciones válidas, nos basta con usar la opción Lista de validación de datos. Pero, ¿qué hacemos si no contamos con una lista de direcciones pero queremos asegurarnos que la sintaxis de la dirección es correcta? Es decir, abc123@xyz.com es una dirección válida, independientemente de que exista o no; pero abc..123@xyz no lo es).

La solución es usar expresiones regulares (regular expresions). La expresión regular es un patrón que nos permite describir un conjunto de texto sin enumerar sus elementos. No voy entrar en los detalles técnicos, que pueden leer en la nota del enlace, pero si señalar que estos patrones pueden ser usados en Vba. De esta manera podemos crear una función definida por el usuario para validar direcciones de correo electrónico.

La función para validar la sintaxis de una dirección de correo electrónico es

Public Function blnEmailValid(ByVal strEmailAdd As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
        blnEmailValid = .Test(strEmailAdd)
    End With
End Function

Este código fue publicado por Vishesh en ExcelExperts.com.

En este ejemplo, ponemos la función en la celda D2 para evaluar la dirección ingresada en la celda C2; si la dirección es válida el valor en la celda D2 es VERDADERO, en caso contrario FALSO

También podemos usarla en Validación de Datos, usando el valor de la celda D2 en la opción Personalizada (en este ejemplo, ponemos la función en A2 que podamos hemos ocultado)

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.

Validación de datos en varias hojas – código mejorado

Gracias al comentario del amigo Juan Munevar en mi nota anterior, vimos que el código para evitar duplicados a través de varias hojas tiene un inconveniente: si la propiedad "mover después de presionar Entrar" está activada el código puede borrar el contenido de la celda equivocada.

Para que el código de la anterior funcione, la propiedad "mover después de presionar Entrar" debe estar desactivada

Como no podemos saber de antemano cuál es la definición del cuaderno debemos escribir un código para cubra todas las posibilidades. Lo que debemos tomar en cuenta es el orden de los eventos cuando apretamos Entrar.
El código evalúa el valor de la celda que era la celda activa al disparar el evento (Target). Es decir, si la opción de mover la selección después de apretar Entrar no está activada, borramos el contenido de la celda activa que es la misma que Target; si se mueve a la izquierda Target se encuentra a la derecha de la celda activa; si se mueve hacia abajo, Target se encuentra arriba; si se mueve hacia arriba, Target es la celda inmediata inferior.
Un caso particular es si la selección se mueve a la izquierda. En este caso, si la celda evaluada está en la columna A, Target coincide con la celda activa ya que Excel no puede seleccionar una celda que no existe.

En definitiva, el código es el siguiente:

Sub valid_accross_sheets(valValue)
   
    Dim iValCalc As Integer
   
    iValCalc = WorksheetFunction.CountIf(Range("Lista1"), valValue) + _
                WorksheetFunction.CountIf(Range("Lista2"), valValue) + _
                 WorksheetFunction.CountIf(Range("Lista3"), valValue)
   
   
    If iValCalc > 1 Then
        MsgBox "El valor " & ActiveCell.Value & " ya existe"
        Select Case Application.MoveAfterReturn
            Case Is = False
                ActiveCell.ClearContents
            Case Else
                Select Case Application.MoveAfterReturnDirection
                    Case Is = xlDown
                        ActiveCell.Offset(-1, 0).ClearContents
                    Case Is = xlUp
                         ActiveCell.Offset(1, 0).ClearContents
                    Case Is = xlToRight
                        ActiveCell.Offset(0, -1).ClearContents
                    Case Is = xlToLeft
                        If ActiveCell.Column = 1 Then
                            ActiveCell.ClearContents
                        Else
                            ActiveCell.Offset(0, 1).ClearContents
                        End If
                    End Select
        End Select
                   
    End If
 
End Sub

El cuaderno puede descargarse aquí.

Validación de datos en múltiples hojas de un cuaderno Excel

Allá por el 2007 publiqué dos notas sobre cómo aplicar validación de datos entre valores que se encuentran en varias hojas de un mismo cuaderno. La idea era impedir la entrada de valores duplicados en el cuaderno, sin importar en que hoja.

Después de una búsqueda en la Internet, encontré un pequeño comentario de Bob Umlas (Excel MVP):

Data validation across sheets isn't supported. You may need an event macro with code to do the checking for you as the worksheet changes (no se puede aplicar validación de datos a través de varias hojas. Habría que usar una macro de tipo evento para hacer la comprobación a medida que se van introduciendo cambios en la hoja).

En las notas mostré dos métodos con fórmulas en contradicción con el comentario de Umlas. El problema con los métodos que expuse en mis notas es que no funcionan. Así que como gesto de contrición y arrepentimiento (que viene a ser lo mismo), aquí va mi nota sobre como validar datos a través de las hojas de un cuaderno utilizando Vba (macros).

Siguiendo con el ejemplo que expuse en esas notas, tenemos un cuaderno con tres hojas; en cada hoja hay una lista de nombres. La idea es que no podamos ingresar en cualquiera de las listas un nombre que ya existe en cualquiera de las otras listas.

Para cada lista hemos creado un nombre que se refiere al rango en forma dinámica (con la función DESREF)

=DESREF(Hoja1!$A$2;0;0;CONTARA(Hoja1!$A:$A)-1;1)

=DESREF(Hoja2!$A$2;0;0;CONTARA(Hoja2!$A:$A)-1;1)

=DESREF(Hoja3!$A$2;0;0;CONTARA(Hoja3!$A:$A)-1;1)

Lo que hacemos ahora es crear un evento de manera que cuando se produzca un cambio en alguna de las hojas, el evento dispare una macro que controle si el nuevo valor introducido en alguna de las tres listas ya existe.

El código de la macro que hace esta tarea es:

Sub valid_accross_sheets(valValue)
   
    Dim iValCalc As Integer
   
    iValCalc = WorksheetFunction.CountIf(Range("Lista1"), valValue) + _
                WorksheetFunction.CountIf(Range("Lista2"), valValue) + _
                 WorksheetFunction.CountIf(Range("Lista3"), valValue)
   
    If iValCalc > 1 Then
        ActiveCell.ClearContents
        MsgBox "El valor " & ActiveCell.Value & " ya existe"
    End If
 
End Sub

Esta macro usa la función CONTAR.SI de Excel para comprobar cuantas veces aparece el valor de la celda activa (en la que hemos introducido el valor). Si el valor aparece más de una vez, el contenido de la celda activa es borrado y aparece un mensaje diciendo que el valor ya existe.

El valor de la celda activa es pasado a la macro por evento Workbook_SheetChange del objeto ThisWorkbook. Usamos este evento para evitar tener que programar un evento para cada hoja

El código es muy sencillo: hace una llamada a la macro valid_accross_sheets(valValue) pasando el valor de Target (la celda en la que hemos ingresado el nuevo valor).

Un detalle a tener en cuenta es que los rangos de los nombres deben ser continuos. Si dejamos una fila en blanco, el rango dinámico no incluirá el nuevo valor y la validación fallará.

El cuaderno con el ejemplo puede descargarse aquí.

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.

Validación de datos - Tamaño de la fuente de la lista desplegable

En la última semana tres lectores me han consultado sobre el mismo tema: el tamaño de la fuente en las listas desplegables creadas con validación de datos.

El tamaño de la fuente en estas listas está definido por defecto y no puede ser cambiado. Esto genera dos problemas:

1 – Algunos de los valores de la lista desplegable aparecen “cortados”

2 – Si el zoom de la hoja está por debajo del 100%, los valores puede llegar a ser casi ilegibles

En esta nota mostraré dos estrategias para solucionar estos problemas:

1 – usar eventos para cambiar el ancho de la columna que contiene la celda con la validación de datos y el nivel de zoom de la hoja;

2 – usar una combobox de la colección de controles ActiveX.

Partimos de esta situación donde tenemos una lista de departamentos de una empresa, que hemos incluido en el nombre definido “lstDepartamentos” y una lista desplegable en la celda E2. Las columnas A, B y C nos sirven como columnas auxiliares y en aplicaciones prácticas estarán ocultas. EL nivel de zoom es 80%

Solución con un evento simple.


La estrategia es programar un evento de manera que cuando el usuario seleccione la celda que contiene la lista (E2), el ancho de la columna E pase a ser 30 (antes del evento es 15) y el zoom será del 120%. Al seleccionar cualquier celda fuera de E2, el zoom vuelve a ser del 80% y el ancho de la columna 15.
En el módulo de la hoja correspondiente ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
    If Target.Address = "$E$2" Then
        ActiveWindow.Zoom = 120
        Range("E2").ColumnWidth = 30
    Else
        Range("E2").ColumnWidth = 15
        ActiveWindow.Zoom = 80
    End If

End Sub

Solución con evento complejo

La estrategia es la misma que en la solución anterior, pero el ancho de la columna será fijado dinámicamente de acuerdo al valor de la lista más largo. Para esto creamos una función UDF (definida por el usuario) para determinar este valor y lo pasamos al código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim iColWidth As Double
'
    iColWidth = max_len_in_range(Range("lstDepartamentos"))
      
    If Target.Address = "$E$2" Then
        ActiveWindow.Zoom = 120
        Target.ColumnWidth = iColWidth
    Else
        ActiveWindow.Zoom = 80
        Range("E2").ColumnWidth = 15
    End If

End Sub

Private Function max_len_in_range(rngList As Range)
    Dim iR As Long
    Dim tmpLen As Integer
  
    For iR = 2 To rngList.Count
        If Len(rngList(iR)) > Len(rngList(iR - 1)) Then
            tmpLen = Len(rngList(iR))
        End If
    Next iR
  
    max_len_in_range = tmpLen
  
End Function
Solución con ComboBox de la colección ActiveX

La ventaja de los controles ActiveX es que sus propiedades y sus eventos pueden ser programados. Esto nos permite “detectar” cuando el usuario activa el control y también cuando lo desactiva. Programamos un evento para cada una de las situaciones:

Private Sub ComboBox1_GotFocus()
    ActiveWindow.Zoom = 120
    Application.EnableEvents = False
    Me.Select
    Application.EnableEvents = True
End Sub

Private Sub ComboBox1_LostFocus()
ActiveWindow.Zoom = 80
End Sub

Los códigos hay que ponerlos en el módulo de la hoja correspondiente.

El archivo con los ejemplos se puede descargar aquí.

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.

Impedir ingreso de fórmulas en celdas de Excel

Excel viene provisto con un mecanismo para controlar el tipo de datos permitidos en un rango: Validación de datos. La herramienta controla el tipo de datos ingresado, es decir, constantes (números, fechas, texto), pero no evitará que el usuario ingrese una fórmula en el rango validado.

Este problema se me presentó cuando estaba desarrollando un formulario para ingresar datos de gastos

Este tipo de formularios basados en hojas de Excel requieren poco esfuerzo para construirlos. Los valores permitidos para cada uno de los campos están controlados con Validación de datos; los botones en la parte inferior permiten pasar los datos a otra hoja que hace las veces de base de datos; las filas y columnas innecesarias están simplemente ocultas.

El problema es que validación de datos no impide que una fórmula sea ingresada en las celdas de la columna Monto

Para evitar esto tenemos que usar un evento. En el módulo de la hoja correspondiente ponemos este código

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Union(Target, Range("rngMonto")).Address = Range("rngMonto").Address Then
        If Target.HasFormula Then
            Target.ClearContents
            MsgBox "Solo valores, no formulas!"
        End If
    End If
   
End Sub

Al introducir un valor en el rango Monto, se dispara el código que evalúa si se trata de una fórmula con la propiedad HasFormula. En caso positivo el contenido de la celda (target) es borrado y aparece el mensaje informando que no está permitido introducir fórmulas.

Este video muestra el funcionamiento de la validación

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.

Rangos dinámicos con la función INDIRECTO de Excel.

Excel permite construir rangos dinámicos, tema que ya hemos tratado en diversas oportunidades en este blog. Rangos dinámicos son aquellos cuya referencia (dirección) se expande o contrae con los cambios en el número de miembros del rango. Estos rangos se definen con fórmulas, por lo general con la función DESREF. También hemos visto que podemos referirnos dinámicamente a un rango usando su nombre como argumento de la función INDIRECTO, por ejemplo cuando creamos listas desplegables dependientes. Pero esto genera un problema con los rangos dinámicos: INDIRECTO no acepta fórmulas como argumentos, sólo texto.
En esta nota veremos un rodeo sencillo a este problema, sin usar macros o funciones definidas por el usuario.




Por ejemplo, si queremos construir una lista desplegable que muestre las sucursales de red

Para crear la lista desplegable usamos validación de datos, con la opción Lista donde usamos como referencia el nombre Sucursales que contiene el rango D2:D13

La referencia al rango en el nombre es absoluta, por lo que si agregamos sucursales a continuación del último valor de la lista, deberemos editar el nombre y cambar la referencia.
Para lograr que la lista se actualice automáticamente al agregar nuevas sucursales tenemos que definir el rango como rango dinámico. Para esto usamos la función DESREF
=DESREF(Hoja2!$D$2,0,0,CONTARA(Hoja2!$D:$D),1)
Aquí pueden leer una explicación detallada sobre la función DESREF (OFFSET en la versión inglesa).
Ahora vamos a agrupar las sucursales por zonas

La idea es elegir una zona en B3 y que la lista desplegable en B4 muestre sólo las sucursales correspondientes. Para esto usaremos validación de datos con la opción lista y en Origen pondremos INDIRECTO(B3). Esto funciona bien si usamos referencias absolutas. Por ejemplo, definimos el nombre “absOeste” como $H$3:$H$5

Nótese que la fórmula en Origen es =INDIRECTO(“abs”&B3), es decir concatenamos el nombre de la zona en B3 con “abs” para obtener el nombre “absOeste” que le hemos puesto al rango.
Si agregamos la sucursal 13 en H6, ésta no queda incluida en el rango del nombre. Podemos usar la fórmula “tradicional” con DESREF para crear el nombre “dinOeste”

Al tratar de crear la lista desplegable con validación de datos recibimos esta advertencia

Como explicamos más arriba, INDIRECTO no puede evaluar fórmulas, sólo texto. En lugar de DESREF o fórmulas definidas por el usuario (macros) como sugieren algunos sitios y foros, podemos usar la funcionalidad Tablas en (Listas Excel Clásico).

Veamos el proceso, primero en Excel 2007 y luego en Excel Clásico.
En B2 creamos una lista desplegable con validación de datos poniendo los nombres de las zonas directamente en la ventanilla Origen

Para crear el rango dinámico Norte seleccionamos las celdas E2:E5, activamos la pestaña Insertar y pulsamos Tabla. Marcamos la opción “La tabla tiene encabezados” y pulsamos Aceptar

Seguidamente activamos Herramientas de Tablas y en Nombre de la tabla cambiamos el nombre por defecto por Norte

Repetimos el mismo proceso para las restantes tres zonas. Ahora en B4 ponemos una lista desplegable con Validación de Datos-Lista y la fórmula =INDIRECTO($B$3)

Las tablas se expanden automáticamente, por lo que al agregar la sucursal 13 en la zona Oeste, ésta aparecerá en la lista desplegable.
En Excel Clásico (versiones 97-2003) usamos la misma técnica pero con algunas diferencias.
En lugar de Tablas, la funcionalidad en Excel Clásico es Listas. Para convertir un rango en una lista usamos el menú Datos-Lista-Crear Listas.
En Excel Clásico no tenemos la posibilidad de darle un nombre a la lista, por lo que usaremos el menú Insertar-Nombre-Definir

Seleccionamos el rango F4:F6 y creamos el nombre Norte. De la misma manera creamos las listas y los nombres del resto de las zonas. Al agregar nuevas sucursales en las zonas, la lista se expande automáticamente

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.