Manual de Tips Excel 2010Filtro Avanzado valores nicos en otra hoja Ya hemos mencionado en el pasado la posibilidad de extraer valores nicos de una lista con Filtro Avanzado.En Excel 2007 usamos Datos-Ordenar y Filtrar-Avanzadas

El problema con este mtodo, tanto con Excel Clsico como con Excel 2007, es que aparentemente podemos extraer la lista de valores nicos slo a la hoja que contiene la lista

Pero si leemos con atencin el mensaje de Excel, vemos que dice "Slo se puede copiar los datos filtrados a la hoja activa". Y remarco "hoja activa", ya que esta es la clave para resolver el "problema".Cuando queremos copiar valores nicos de una lista a una hoja distinta de la hoja que contiene los datos, sencillamente activamos el Filtro Avanzado desde la hoja que recibir la lista de valores nicos. De esta manera la hoja activa es la que recibir los valores nicos.Veamos el caso en nuestro ejemplo. La lista de nombres, con varios valores repetidos, est en la hoja "lista"; nuestro objetivo es copiar la lista de valores nicos en la hoja "nicos".Elegimos la celda de la hoja "nicos" donde queremos poner los valores nicos (A1 en nuestro caso) y activamos Filtro Avanzado y ponemos todas las definiciones excepto "Rango de la lista"

Ahora sealamos el rango de la lista

Al apretar "Aceptar" los valores nicos sern copiados al rango deseado.Antes de descubrir esta solucin (y supongo que no soy el primero en descubrirla) me haba embarcado a escribir una macro para hacer la tarea. Moraleja: dedicar siempre unos segundos para evaluar si entendemos lo que nos dicen.

Validacin de datos en varias hojas cdigo mejorado Gracias al comentario del amigo Juan Munevar en minota anterior, vimos que el cdigo para evitar duplicados a travs de varias hojas tiene un inconveniente: si la propiedad "mover despus de presionar Entrar" est activada el cdigo puede borrar el contenido de la celda equivocada.

Para que el cdigo de la anterior funcione, la propiedad "mover despus de presionar Entrar" debe estar desactivada

Como no podemos saber de antemano cul es la definicin del cuaderno debemos escribir un cdigo para cubra todas las posibilidades. Lo que debemos tomar en cuenta es el orden de los eventos cuando apretamos Entrar.El cdigo evala el valor de la celda que era la celda activa al disparar el evento (Target). Es decir, si la opcin de mover la seleccin despus 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 seleccin 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 cdigo es el siguiente:

Sub valid_accross_sheets(valValue)Dim iValCalc As IntegeriValCalc = WorksheetFunction.CountIf(Range("Lista1"), valValue) + _WorksheetFunction.CountIf(Range("Lista2"), valValue) + _ WorksheetFunction.CountIf(Range("Lista3"), valValue)If iValCalc > 1 ThenMsgBox "El valor " & ActiveCell.Value & " ya existe"Select Case Application.MoveAfterReturnCase Is = FalseActiveCell.ClearContentsCase ElseSelect Case Application.MoveAfterReturnDirectionCase Is = xlDownActiveCell.Offset(-1, 0).ClearContentsCase Is = xlUp ActiveCell.Offset(1, 0).ClearContentsCase Is = xlToRightActiveCell.Offset(0, -1).ClearContentsCase Is = xlToLeftIf ActiveCell.Column = 1 ThenActiveCell.ClearContentsElseActiveCell.Offset(0, 1).ClearContentsEnd IfEnd SelectEnd SelectEnd IfEnd SubEjercicio

Validacin de datos en mltiples hojas de un cuaderno Excel All por el 2007 publiqu dos notas sobre cmo aplicar validacin 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.

Despus de una bsqueda en la Internet, encontr un pequeo 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 validacin de datos a travs de varias hojas. Habra que usar una macro de tipo evento para hacer la comprobacin a medida que se van introduciendo cambios en la hoja).

En las notas mostr dos mtodos con frmulas en contradiccin con el comentario de Umlas. El problema con los mtodos que expuse en mis notas es que no funcionan. As que como gesto de contricin y arrepentimiento (que viene a ser lo mismo), aqu va mi nota sobre como validar datos a travs 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 dinmica (con la funcin 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 cdigo de la macro que hace esta tarea es:

Sub valid_accross_sheets(valValue)Dim iValCalc As IntegeriValCalc = WorksheetFunction.CountIf(Range("Lista1"), valValue) + _WorksheetFunction.CountIf(Range("Lista2"), valValue) + _ WorksheetFunction.CountIf(Range("Lista3"), valValue)If iValCalc > 1 ThenActiveCell.ClearContentsMsgBox "El valor " & ActiveCell.Value & " ya existe"End IfEnd Sub

Esta macro usa la funcin 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 ms 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 cdigo 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 dinmico no incluir el nuevo valor y la validacin fallar.

El cuaderno con el ejemplo puede descargarse aqu. Seguir leyendo... Coordinar horarios con Excel Esta nota viene a colacin de mi rencuentro con un entraable amigo de la juventud. Nos unen maravillosos recuerdos de la juventud pero nos separan 15 husos horarios.

Despus de intercambiar nuestras direcciones de Skype empezamos a coordinar el da y la hora para comunicarnos. Fijamos el prximo 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 da elegido para conversar es el sbado 18 de febrero. Las horas activas, es decir, cuando nuestro estado mental permite una conversacin ms o menos coherente, son entre las 9 de la maana 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.

Cmo funciona el modelo?

Las celdas de la tabla de horarios (el rango E2:F6H2:I25) contienen esta frmula:

=$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 expresin (FILA()-2)/24 calcula la hora del da en saltos de 1 hora, usando el valor de la funcin FILA y dividiendo por 24 (ese es el mtodo que usa Excel para los clculos de horas). Esta frmula nos permite pasar al da siguiente cuando se cumplen las primeras 24 horas.

Para mostrar el resultado con el da de la semana usamos formato personalizado:

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

Como puede verse, la frmula usada es muy sencilla: =$C2=1. Veamos qu hay en C2

En A2 ponemos la frmula:

=Y(HORA(H2)>=HORA($F$5);HORA(H2)=HORA($F$5);HORA(I2)