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í.

Coordinar horarios con Excel

Esta nota viene a colación de mi reencuentro con un entrañable amigo de la juventud. Nos unen maravillosos recuerdos de la juventud pero nos separan 15 husos horarios.

Después de intercambiar nuestras direcciones de Skype empezamos a coordinar el día y la hora para comunicarnos. Fijamos el próximo fin de semana, pero ¿a qué hora? Aquí es donde Excel puede darnos una buena ayuda.

Yo resido en la Ciudad 1 y mi amigo en la Ciudad 2. La diferencia horaria es de 15 horas y el día elegido para conversar es el sábado 18 de febrero. Las horas activas, es decir, cuando nuestro estado mental permite una conversación más o menos coherente, son entre las 9 de la mañana y las 10 de la noche (cada uno en su ciudad). Este es el modelo que construí en Excel para ver las horas en que ambos coincidimos

En el cuadro de definiciones (el rango E2:F6) ponemos la fecha, la diferencia horaria, y las horas de comienzo y final del horario de actividad y en el rango H2:I25 vemos las horas en que coincidimos con fondo verde y texto en negrita.

¿Cómo funciona el modelo?

Las celdas de la tabla de horarios (el rango E2:F6 H2:I25) contienen esta fórmula:

=$F$2+((FILA()-2)/24) para Ciudad 1

=$F$2+((FILA()-2+$F$3)/24) para Ciudad 2

donde $F$3 es la diferencia horaria entre las ciudades

La expresión (FILA()-2)/24 calcula la hora del día en saltos de 1 hora, usando el valor de la función FILA y dividiendo por 24 (ese es el método que usa Excel para los cálculos de horas). Esta fórmula nos permite pasar al día siguiente cuando se cumplen las primeras 24 horas.

Para mostrar el resultado con el día de la semana usamos formato personalizado:

Para poner el fondo verde y el texto en negrita usamos formato condicional. La técnica que propongo usa columnas auxiliares ocultas, como seguramente mis perspicaces lectores habran ya notado (en la primer imagen la primer columna visible es la columna D).

Como puede verse, la fórmula usada es muy sencilla: =$C2=1. Veamos qué hay en C2

En A2 ponemos la fórmula:

=Y(HORA(H2)>=HORA($F$5);HORA(H2)<=HORA($F$6))

El resultado es verdadero si la hora de la fecha evaluada en la columna de la ciudad 1 cae dentro del rango establecido entre F5 y F6; lo mismo con la fórmula en la celda B2 para la ciudad 2

=Y(HORA(I2)>=HORA($F$5);HORA(I2)<=HORA($F$6))

En la columna C multiplicamos ambos resultados. Como se trata de expresiones lógicas, dan 1 si ambos resultados son VERDADERO y 0 si alguno de los resultados es FALSO.

Si bien podemos crear una fórmula compleja que combine las tres fórmulas y usarla directamente en el formato condicional, esta técnica tiene en mi opinión la ventaja de exponer en forma más clara la forma del cálculo.