Empiezo con una aclaración: por primera vez he eliminado una entrada luego de haberla publicado. Se trata de la entrada donde mostraba un código para evitar el problema de la destrucción de la validación de datos al pegar un valor copiado de otra celda. Había demasiadas circunstancias que provocaban que el código no funcionara. Mea culpa!
Volviendo sobre el tema, una de las debilidades de Validación de Datos es que si un usuario pega un valor en una celda del rango validado en lugar de ingresarlo manualmente, las definiciones de la validación quedan eliminadas.
La única forma de evitar estas situaciones es usar macros, más precisamente eventos.
Buscando en la Internet encontré, entre otras, una solución propuesta por John Walkenbach. El problema con esta solución es que si el usuario en lugar de pegar el valor usa la opción Pegado Especial-Valores (o Fórmulas), el evento no responde y el valor es aceptado.
El código que propongo usa una parte del código de Walkenbach, para el caso que el usuario use la opción de pegado común, y parte de mi código para el caso que use pegar-valores, pegar-fórmulas, Insertar o arrastre el valor a la celda.
En el módulo de Vba de la hoja pegamos estos códigos
Private Sub Worksheet_Change(ByVal Target As Range)
' rutina desarrollada por Jorge Dunkelman - JLD Excel Blog
' parte del codigo tomado de la nota de John Walkenbach http://www.j-walk.com/ss/excel/tips/tip98.htm
Dim rngValid As Range, cell As Range
Dim Msg As String
Dim codeValid As Variant
Set rngValid = Range("rngValidado")
On Error Resume Next
If Not HasValidation(rngValid) Then
Application.Undo
MsgBox "Valor no válido", vbCritical
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
For Each cell In Target
If Union(cell, rngValid).Address = rngValid.Address Then
codeValid = ActiveCell.Validation.Value
If codeValid = True Then
Exit Sub
Else
MsgBox "Valor no válido", vbCritical
Application.EnableEvents = False
cell.ClearContents
cell.Activate
Application.EnableEvents = True
End If
End If
Next cell
End Sub
Private Function HasValidation(r) As Boolean
Dim x
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
Nótese que estamos usando un evento Worksheet_Change y una función UDF.
Al rango B3:B12 de la hoja hemos aplicado validación de datos con la opción Lista. Los valores permitidos son a1, a2, a3 y a4.
Este video muestra cómo funciona la macro
El archivo con el ejemplo y el código puede descargarse aquí.
Sugerencias y ayuda para Excel en español. Ejemplos de funciones y fórmulas, gráficos, automatización con Vba, Power Query y PowerPivot
Mostrando las entradas con la etiqueta Validacion de Datos. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Validacion de Datos. Mostrar todas las entradas
martes, diciembre 22, 2009
sábado, octubre 17, 2009
Listas desplegables en Excel con ajuste automático.
En Excel es my fácil crear listas desplegables. El método más práctico es usando Validación de Datos – Listas. En este blog hemos tratado el tema de diversas oportunidades y hemos mostrado también como crear listas desplegables dependientes.
En relación a este tema recibo a menudo consultas sobre cómo ir eliminando de la lista los valores que ya han sido seleccionados.
Supongamos que estamos organizando en que mesa se sentarán los invitados a una cena o recepción. Para el caso supongamos una lista de 16 invitados que habrá que sentar en cuatro mesas, cuatro en cada una.
Empezamos por poner la lista de invitados en una hoja y una tabla que representa las mesas y sillas en otra
El objetivo es crear una lista desplegable que pondremos en cada celda del rango C3:F6 (rango que hemos puesto en el nombre “mesas”) de manera que cada vez que ubiquemos un comensal en la tabla éste desaparezca de la lista.
Mostraremos dos posibilidades: con columnas auxiliares y con fórmulas matriciales.
Con columnas auxiliares.
En la hoja que contiene la lista de invitados creamos una columna auxiliar con el rótulo “Por ubicar”
El objetivo de esta columna es controlar que invitado ya ha sido ubicado en alguna mesa. Para eso usamos la fórmula
=SI(CONTAR.SI(mesas,C2)=1,"",C2)
Por ejemplo, si ubicamos a Carla y a Federico en la matriz de las mesas, obtenemos
Ahora agregamos una nueva columna auxiliar (Aux1)
La fórmula
=CONTAR.SI($D$2:$D$17,"<="&D2)
da un número de orden a los valores del rango C2:C17 (como la función JERARQUIA a una serie de números).
En caso que el valor de celda sea vacío (la celda no está vacía ya que contiene una fórmula) el resultado es 0 (cero).
Nuestro próximo paso es crear una celda para controlar cuantas invitados nos quedan por ubicar, es decir, cuantos valores distintos de cero hay en el rango Aux1. Con este objetivo ponemos esta fórmula en la celda J1
=CONTAR.SI(E2:E17,">0")
Ahora creamos la columna auxiliar Aux2 en el rango F2:F17 con esta fórmula
=K.ESIMO.MAYOR($E$2:$E$17,$J$1-FILA()+2)
Esta columna nos sirve como argumento para la columna final, en el rango G2:G17, donde obtenemos los valores de la lista desplegable
Para evitar que en la lista desplegable aparezcan los valores #NUM de las últimas dos celda, usamos el valor de la celda “control” para crear un rango dinámico que las excluya con la fórmula
=DESREF(invitados!$G$2,0,0,invitados!$J$1,1)
Aplicamos esta validación de datos al rango “mesas”.
A medida que vamos ubicando a los invitados, la lista se va ajustando y mostrando sólo aquellos que quedan por ubicar
Con fórmulas matriciales.
Al igual que en el modelo anterior creamos la lista de los invitados a ubicar en el rango D2:D17.
La lista de invitados a ubicar la creamos en el rango E2:E17 con esta fórmula matricial
={INDICE($D$2:$D$17,COINCIDIR(K.ESIMO.MAYOR(CONTAR.SI($D$2:$D$17,"<="&D2:D17),FILA()-1),CONTAR.SI($D$2:$D$17,"<="&D2:D17),0))}
Como puede verse, no obtenemos errores #NUM, pero las celdas al final de la lista no están vacías. De manera que necesitamos una celda de control, como en la solución con columnas auxiliares, para que la lista desplegable muestre sólo los nombres disponibles.
En la celda H1 ponemos esta fórmula matricial
={SUMA((CONTAR.SI($D$2:$D$17,"<="&D2:D17)<>0)*1)}
La lista desplegable la creamos con el fórmula
=DESREF('invitados matricial'!$E$2,0,0,'invitados matricial'!$H$1,1)
Technorati Tags: MS Excel
En relación a este tema recibo a menudo consultas sobre cómo ir eliminando de la lista los valores que ya han sido seleccionados.
Supongamos que estamos organizando en que mesa se sentarán los invitados a una cena o recepción. Para el caso supongamos una lista de 16 invitados que habrá que sentar en cuatro mesas, cuatro en cada una.
Empezamos por poner la lista de invitados en una hoja y una tabla que representa las mesas y sillas en otra
El objetivo es crear una lista desplegable que pondremos en cada celda del rango C3:F6 (rango que hemos puesto en el nombre “mesas”) de manera que cada vez que ubiquemos un comensal en la tabla éste desaparezca de la lista.
Mostraremos dos posibilidades: con columnas auxiliares y con fórmulas matriciales.
Con columnas auxiliares.
En la hoja que contiene la lista de invitados creamos una columna auxiliar con el rótulo “Por ubicar”
El objetivo de esta columna es controlar que invitado ya ha sido ubicado en alguna mesa. Para eso usamos la fórmula
=SI(CONTAR.SI(mesas,C2)=1,"",C2)
Por ejemplo, si ubicamos a Carla y a Federico en la matriz de las mesas, obtenemos
Ahora agregamos una nueva columna auxiliar (Aux1)
La fórmula
=CONTAR.SI($D$2:$D$17,"<="&D2)
da un número de orden a los valores del rango C2:C17 (como la función JERARQUIA a una serie de números).
En caso que el valor de celda sea vacío (la celda no está vacía ya que contiene una fórmula) el resultado es 0 (cero).
Nuestro próximo paso es crear una celda para controlar cuantas invitados nos quedan por ubicar, es decir, cuantos valores distintos de cero hay en el rango Aux1. Con este objetivo ponemos esta fórmula en la celda J1
=CONTAR.SI(E2:E17,">0")
Ahora creamos la columna auxiliar Aux2 en el rango F2:F17 con esta fórmula
=K.ESIMO.MAYOR($E$2:$E$17,$J$1-FILA()+2)
Esta columna nos sirve como argumento para la columna final, en el rango G2:G17, donde obtenemos los valores de la lista desplegable
Para evitar que en la lista desplegable aparezcan los valores #NUM de las últimas dos celda, usamos el valor de la celda “control” para crear un rango dinámico que las excluya con la fórmula
=DESREF(invitados!$G$2,0,0,invitados!$J$1,1)
Aplicamos esta validación de datos al rango “mesas”.
A medida que vamos ubicando a los invitados, la lista se va ajustando y mostrando sólo aquellos que quedan por ubicar
Con fórmulas matriciales.
Al igual que en el modelo anterior creamos la lista de los invitados a ubicar en el rango D2:D17.
La lista de invitados a ubicar la creamos en el rango E2:E17 con esta fórmula matricial
={INDICE($D$2:$D$17,COINCIDIR(K.ESIMO.MAYOR(CONTAR.SI($D$2:$D$17,"<="&D2:D17),FILA()-1),CONTAR.SI($D$2:$D$17,"<="&D2:D17),0))}
Como puede verse, no obtenemos errores #NUM, pero las celdas al final de la lista no están vacías. De manera que necesitamos una celda de control, como en la solución con columnas auxiliares, para que la lista desplegable muestre sólo los nombres disponibles.
En la celda H1 ponemos esta fórmula matricial
={SUMA((CONTAR.SI($D$2:$D$17,"<="&D2:D17)<>0)*1)}
La lista desplegable la creamos con el fórmula
=DESREF('invitados matricial'!$E$2,0,0,'invitados matricial'!$H$1,1)
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.
Technorati Tags: MS Excel
viernes, julio 10, 2009
Lista desplegable con ajuste automático en Excel
Esta es una variante de la técnica que mostré en la nota sobre cómo agregar valores únicos en una lista desplegable con validación de datos.
Un lector me presenta el siguiente caso: ha creado una lista desplegable que muestra una lista de facturas. ¿Cómo hacer para que las facturas abonadas vayan desapareciendo de la lista desplegable?
Supongamos que esta es la lista de las facturas
En el rango B6:B19 aparecen los números de las facturas y en el rango C6:C19 si han sido pagadas (señalado con la palabra “pagada).
En nuestro ejemplo, queremos crear una lista desplegable que sólo muestre aquellas facturas que no han sido pagadas
Por supuesto, queremos que esta lista sea dinámica, es decir que cuando pongamos “pagada” al lado de una factura, ésta desaparezca de la lista
Empezamos por crear la primer columna auxiliar en el rango E6:E19.
En la celda E6 ponemos esta fórmula
=SI(LARGO(C6)=0,FILA(),"")
y la copiamos a todo el rango.
Ahora agregamos una segunda columna auxiliar en el rango F6:F19 con esta fórmula
=INDICE($B$6:$B$19,COINCIDIR(K.ESIMO.MENOR($E$6:$E$19,FILA()-5),$E$6:$E$19))
=SI(ESERROR(F6),"",F6)
Este rango es el que usamos para definir la lista de valores válidos. Creamos el nombre "lista"
Si quitamos la palabra “pagada” en alguna celda, la factura correspondiente aparecerá automáticamente en la lista; si agregamos “pagada”, la factura desaparecerá.
Technorati Tags: MS Excel
Un lector me presenta el siguiente caso: ha creado una lista desplegable que muestra una lista de facturas. ¿Cómo hacer para que las facturas abonadas vayan desapareciendo de la lista desplegable?
Supongamos que esta es la lista de las facturas
En el rango B6:B19 aparecen los números de las facturas y en el rango C6:C19 si han sido pagadas (señalado con la palabra “pagada).
En nuestro ejemplo, queremos crear una lista desplegable que sólo muestre aquellas facturas que no han sido pagadas
Por supuesto, queremos que esta lista sea dinámica, es decir que cuando pongamos “pagada” al lado de una factura, ésta desaparezca de la lista
.
La solución consiste en crear dos columnas auxiliares y una tercera que será la que contenga los valores válidos para la lista.
La solución consiste en crear dos columnas auxiliares y una tercera que será la que contenga los valores válidos para la lista.
Empezamos por crear la primer columna auxiliar en el rango E6:E19.
En la celda E6 ponemos esta fórmula
=SI(LARGO(C6)=0,FILA(),"")
y la copiamos a todo el rango.
Esta columna nos da un “número de orden” que es de hecho un número entero único (el número de fila) que identifica a las facturas que aún no han sido pagadas.
Ahora agregamos una segunda columna auxiliar en el rango F6:F19 con esta fórmula
=INDICE($B$6:$B$19,COINCIDIR(K.ESIMO.MENOR($E$6:$E$19,FILA()-5),$E$6:$E$19))
Tenemos un pequeño problema y es que cuando una factura ha sido pagada el valor de la fórmula es #NUM!.
Si usáramos el rango F6:F19 para crear la lista de los valores, también el error #NUM! aparecería en ella lo cual es poco elegante.
Una de las soluciones es crear una tercera columna en el rango G6:G19 con la fórmula
=SI(ESERROR(F6),"",F6)
Este rango es el que usamos para definir la lista de valores válidos. Creamos el nombre "lista"
Si quitamos la palabra “pagada” en alguna celda, la factura correspondiente aparecerá automáticamente en la lista; si agregamos “pagada”, la factura desaparecerá.
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.
Technorati Tags: MS Excel
sábado, mayo 02, 2009
Catálogo de Imágenes en Excel – otra versión
Crear catálogos de imágenes en Excel es uno de los temas más consultados en este blog. Hace un poco más de dos años y medio publiqué la primer nota sobre base de datos de imágenes que se convirtió en una de las más leídas del blog.
La técnica empleada para crear el catálogo de imágenes en Excel no es trivial y a principios de este año publique una nota aclaratoria que incluye un enlace para descargar una animación del proceso.
Además de los problemas potenciales que señalaba en la nota original, si guardamos las imágenes en el cuaderno, tal como muestra la técnica empleada en la nota original, el archivo tiende a volverse muy “pesado”, en especial si guardamos imágenes de alta resolución.
En esta nota mostraremos una técnica distinta, donde las imágenes están guardadas en alguna carpeta del sistema (computador personal o red) y sólo usamos Excel para mostrarlas de acuerdo a un número de código que se haya elegido en alguna celda.
Empezamos por guardar las imágenes en una única carpeta. Por comodidad las pondré en D:\catalogo
En el siguiente paso construimos el cuaderno Excel que tendrá dos hojas, Lista y Catalogo.
Como su nombre indica, en la primer hoja pondremos la lista de imágenes pero en lugar de guardar la imagen en la hoja pondremos la referencia a la dirección en el sistema
Podemos ver que los valores en la primer fila no son números de código y dirección en el disco duro. La función de estos valores será explicada más adelante.
Ahora creamos dos rangos dinámicos que encapsularemos en dos nombres
imagenes =DESREF(Lista!$A$1,0,0,CONTARA(Lista!$A:$A),1)
path_imagenes =DESREF(Lista!$A$1,0,0,CONTARA(Lista!$A:$A),2)
Estos nombres se adaptan automáticamente a la cantidad de filas en la lista usando la técnica que ya hemos mostrado en varias oportunidades. “Imagenes” se refiere al rango de la columna A que contiene valores; “path_imagenes“ se refiere al rango A:B con el número de filas correspondiente. Estos nombres serán usados en la hoja “Catalogo” donde mostramos las imágenes.
En la hoja Catalogo creamos una lista desplegable usando la opción Lista de Validación de Datos
Los valores de la lista desplegable están definidos por el rango dinámico “imagenes”
Como pueden ver, el primer valor de la lista sirve como indicación al usuario qué es lo que tiene que hacer. Los valores de la lista desplegable sirven como argumentos en para la función BUSCARV que ponemos en la celda C3
=BUSCARV(A3,path_imagenes,2,0)
Esta fórmula da como resultado la dirección de la imagen (que pusimos en la hoja Lista).
Nuestro próximo paso es poner en la hoja un medio de mostrar la imagen correspondiente al código que hayamos elegido. Activamos la barra de herramientas Cuadro de Controles y elegimos el control Imagen
Ponemos el control en una posición cercana a la lista desplegable y para mejorar el aspecto de la hoja quitamos las líneas de división. La posición del control no es casual y nos sirve para ocultar la fórmula que hemos puesto en C3.
Ahora necesitamos un método para cargar la imagen correspondiente, lo que haremos con una macro, y un evento que haga correr la macro cuando el usuario elige un número de código.
Sub show_pic() es la macro para cargar la imagen en el control
Sub show_pic()
Dim PicAddress
PicAddress = Sheets("Catalogo").Range("C3").Value
If IsError(PicAddress) Then
Sheets("Catalogo").Image1.Picture = Nothing
Else
Sheets("Catalogo").Image1.Picture = LoadPicture(PicAddress)
End If
End Sub
Esta macro va en un módulo común del editor de VBa.
Para que la macro corra cuando se elige un número de código programamos este evento en el módulo de la hoja Catalogo
Private Sub Worksheet_Change(ByVal Target As Range)
Dim celControl As Range
Set celControl = [A3]
If Union(Target, celControl).Address = celControl.Address Then show_pic
End Sub
Elegimos un número de código en la lista desplegable, lo que dispara la macro que carga la imagen correspondiente en el control
La ventaja de esta técnica reside en que al no guardar las imágenes en un cuaderno de Excel, éste es muy estable y nos permite usar imágenes de alta resolución.
Finalmente agregaremos dos mejoras a nuestro modelo. Tenemos que evitar que el usuario guarde el cuaderno con una imagen cargada en el control. Esto puede suceder de dos maneras:
1 – que el usuario intente cerrar el cuaderno y Excel le pida guardarlo antes de cerrarlo (y que el usuario decida guardarlo)
2 – que el usuario decida guardar el cuaderno por propia iniciativa.
La idea no es evitar que el usuario guarde el cuaderno, sino que al hacerlo no haya ninguna imagen cargada en el control.
Para lograr esto programamos dos eventos en el objeto ThisWorkbook
Workbook_BeforeClose
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Catalogo").Image1.Picture = Nothing
Sheets("Catalogo").Range("A3").ClearContents
End Sub
Workbook_BeforeSave
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Sheets("Catalogo").Image1.Picture = Nothing
Sheets("Catalogo").Range("A3").ClearContents
End Sub
El ejemplo se puede descargar aquí. Para que el modelo funcione hay que reemplazar los valores del rango A2:B6 por referencias a archivos que existan en el computador o red del usuario.
Technorati Tags: MS Excel
La técnica empleada para crear el catálogo de imágenes en Excel no es trivial y a principios de este año publique una nota aclaratoria que incluye un enlace para descargar una animación del proceso.
Además de los problemas potenciales que señalaba en la nota original, si guardamos las imágenes en el cuaderno, tal como muestra la técnica empleada en la nota original, el archivo tiende a volverse muy “pesado”, en especial si guardamos imágenes de alta resolución.
En esta nota mostraremos una técnica distinta, donde las imágenes están guardadas en alguna carpeta del sistema (computador personal o red) y sólo usamos Excel para mostrarlas de acuerdo a un número de código que se haya elegido en alguna celda.
Empezamos por guardar las imágenes en una única carpeta. Por comodidad las pondré en D:\catalogo
En el siguiente paso construimos el cuaderno Excel que tendrá dos hojas, Lista y Catalogo.
Como su nombre indica, en la primer hoja pondremos la lista de imágenes pero en lugar de guardar la imagen en la hoja pondremos la referencia a la dirección en el sistema
Podemos ver que los valores en la primer fila no son números de código y dirección en el disco duro. La función de estos valores será explicada más adelante.
Ahora creamos dos rangos dinámicos que encapsularemos en dos nombres
imagenes =DESREF(Lista!$A$1,0,0,CONTARA(Lista!$A:$A),1)
path_imagenes =DESREF(Lista!$A$1,0,0,CONTARA(Lista!$A:$A),2)
Estos nombres se adaptan automáticamente a la cantidad de filas en la lista usando la técnica que ya hemos mostrado en varias oportunidades. “Imagenes” se refiere al rango de la columna A que contiene valores; “path_imagenes“ se refiere al rango A:B con el número de filas correspondiente. Estos nombres serán usados en la hoja “Catalogo” donde mostramos las imágenes.
En la hoja Catalogo creamos una lista desplegable usando la opción Lista de Validación de Datos
Los valores de la lista desplegable están definidos por el rango dinámico “imagenes”
Como pueden ver, el primer valor de la lista sirve como indicación al usuario qué es lo que tiene que hacer. Los valores de la lista desplegable sirven como argumentos en para la función BUSCARV que ponemos en la celda C3
=BUSCARV(A3,path_imagenes,2,0)
Esta fórmula da como resultado la dirección de la imagen (que pusimos en la hoja Lista).
Nuestro próximo paso es poner en la hoja un medio de mostrar la imagen correspondiente al código que hayamos elegido. Activamos la barra de herramientas Cuadro de Controles y elegimos el control Imagen
Ponemos el control en una posición cercana a la lista desplegable y para mejorar el aspecto de la hoja quitamos las líneas de división. La posición del control no es casual y nos sirve para ocultar la fórmula que hemos puesto en C3.
Ahora necesitamos un método para cargar la imagen correspondiente, lo que haremos con una macro, y un evento que haga correr la macro cuando el usuario elige un número de código.
Sub show_pic() es la macro para cargar la imagen en el control
Sub show_pic()
Dim PicAddress
PicAddress = Sheets("Catalogo").Range("C3").Value
If IsError(PicAddress) Then
Sheets("Catalogo").Image1.Picture = Nothing
Else
Sheets("Catalogo").Image1.Picture = LoadPicture(PicAddress)
End If
End Sub
Esta macro va en un módulo común del editor de VBa.
Para que la macro corra cuando se elige un número de código programamos este evento en el módulo de la hoja Catalogo
Private Sub Worksheet_Change(ByVal Target As Range)
Dim celControl As Range
Set celControl = [A3]
If Union(Target, celControl).Address = celControl.Address Then show_pic
End Sub
Elegimos un número de código en la lista desplegable, lo que dispara la macro que carga la imagen correspondiente en el control
La ventaja de esta técnica reside en que al no guardar las imágenes en un cuaderno de Excel, éste es muy estable y nos permite usar imágenes de alta resolución.
Finalmente agregaremos dos mejoras a nuestro modelo. Tenemos que evitar que el usuario guarde el cuaderno con una imagen cargada en el control. Esto puede suceder de dos maneras:
1 – que el usuario intente cerrar el cuaderno y Excel le pida guardarlo antes de cerrarlo (y que el usuario decida guardarlo)
2 – que el usuario decida guardar el cuaderno por propia iniciativa.
La idea no es evitar que el usuario guarde el cuaderno, sino que al hacerlo no haya ninguna imagen cargada en el control.
Para lograr esto programamos dos eventos en el objeto ThisWorkbook
Workbook_BeforeClose
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Catalogo").Image1.Picture = Nothing
Sheets("Catalogo").Range("A3").ClearContents
End Sub
Workbook_BeforeSave
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Sheets("Catalogo").Image1.Picture = Nothing
Sheets("Catalogo").Range("A3").ClearContents
End Sub
El ejemplo se puede descargar aquí. Para que el modelo funcione hay que reemplazar los valores del rango A2:B6 por referencias a archivos que existan en el computador o red del usuario.
Technorati Tags: MS Excel
miércoles, febrero 18, 2009
Hipervínculo a una dirección de correo Outlook
Siguiendo en el tema de los hipervínculos, respondemos hoy a la consulta de un de mis lectores: ¿Cómo puedo hacer para crear un hipervínculo a una dirección de correo Outlook dependiendo del valor de una celda con una lista validada?
EL tema es más sencillo de lo que parece en principio, pero hay un pequeño problema por resolver, como en casi todos los aspectos de nuestras vidas.
Empecemos por notar que cuando ponemos en una celda de Excel un texto que sigue el patrón de una dirección de correo electrónico, Excel agrega automáticamente un hipervínculo de tipo “mailto:xxx@yyy.zzz”
Como ya sabemos, podemos crear una lista desplegable usando validación de datos. Para eso creamos una lista de direcciones de mail y le damos un nombre definido. Para más comodidad definimos un rango dinámico con DESREF
Luego creamos la lista desplegable con validación de datos-lista
Ponemos la lista desplegable en la celda B1, de manera que podamos elegir la dirección
El problema con este método, es que a diferencia de cuando introducimos la dirección manualmente, Excel no crea el hipervínculo
Existen dos remedios a esta situación:
1 – después de seleccionar el nombre apretamos F2 (edición) e inmediatamente después Enter.
2 – Creamos un evento de manera que las acciones señaladas en 1 sucedan automáticamente. En el módulo Vba de la hoja1 (en nuestro ejemplo) ponemos este código
Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Target, [B1]).Address = [B1].Address Then
SendKeys "{F2}"
SendKeys "{Enter}"
Application.EnableEvents = False
End If
Application.EnableEvents = True
End Sub
La sentencia SendKeys imita el tecleado de F2 y Enter si la variable Target coincide con la celda que hemos definido como celda que contiene la dirección.
Technorati Tags: MS Excel
EL tema es más sencillo de lo que parece en principio, pero hay un pequeño problema por resolver, como en casi todos los aspectos de nuestras vidas.
Empecemos por notar que cuando ponemos en una celda de Excel un texto que sigue el patrón de una dirección de correo electrónico, Excel agrega automáticamente un hipervínculo de tipo “mailto:xxx@yyy.zzz”
Como ya sabemos, podemos crear una lista desplegable usando validación de datos. Para eso creamos una lista de direcciones de mail y le damos un nombre definido. Para más comodidad definimos un rango dinámico con DESREF
Luego creamos la lista desplegable con validación de datos-lista
Ponemos la lista desplegable en la celda B1, de manera que podamos elegir la dirección
El problema con este método, es que a diferencia de cuando introducimos la dirección manualmente, Excel no crea el hipervínculo
Existen dos remedios a esta situación:
1 – después de seleccionar el nombre apretamos F2 (edición) e inmediatamente después Enter.
2 – Creamos un evento de manera que las acciones señaladas en 1 sucedan automáticamente. En el módulo Vba de la hoja1 (en nuestro ejemplo) ponemos este código
Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Target, [B1]).Address = [B1].Address Then
SendKeys "{F2}"
SendKeys "{Enter}"
Application.EnableEvents = False
End If
Application.EnableEvents = True
End Sub
La sentencia SendKeys imita el tecleado de F2 y Enter si la variable Target coincide con la celda que hemos definido como celda que contiene la dirección.
Technorati Tags: MS Excel
jueves, febrero 12, 2009
Referencias remotas en validación de datos
Una de las consultas frecuentes que recibo es si se puede crear una validación de datos en una hoja de un cuaderno de Excel usando una lista (es decir, un rango) que se encuentra en otro cuaderno.
La respuesta es si, con un poco de trabajo manual. Veamos este ejemplo: tenemos un cuaderno que hemos guardado con el nombre "clientes.xls" donde tenemos una lista de clientes. En un segundo cuaderno, "validación.xls", queremos crear una lista desplegable con validación de datos, basándonos en la lista del primer cuaderno.
Para facilitar la visualización he puesto ambos cuadernos en una ventana usando Ventana-Comparar en Paralelo
En el cuaderno clientes.xls definimos el nombre "clientes" que contiene el rango A2:A92
Ahora pasamos al cuaderno validación.xls y creamos el nombre "clientes" con esta definición
=clientes.xls!Lista_de_Clientes
Hay que prestar atención al signo de exclamación entre el nombre del cuaderno remoto y el nombre que hemos definido en él.
Ahora creamos la lista desplegable en la hoja1 del cuaderno validación.xls usando el nombre que acabamos de definir ("clientes")
Ahora podemos ver que la lista desplegable en validación.xls usa los valores del rango definido en el cuaderno clientes.xls.
Para que esta lista funcione ambos cuadernos deben estar abiertos.
La respuesta es si, con un poco de trabajo manual. Veamos este ejemplo: tenemos un cuaderno que hemos guardado con el nombre "clientes.xls" donde tenemos una lista de clientes. En un segundo cuaderno, "validación.xls", queremos crear una lista desplegable con validación de datos, basándonos en la lista del primer cuaderno.
Para facilitar la visualización he puesto ambos cuadernos en una ventana usando Ventana-Comparar en Paralelo
En el cuaderno clientes.xls definimos el nombre "clientes" que contiene el rango A2:A92
Ahora pasamos al cuaderno validación.xls y creamos el nombre "clientes" con esta definición
=clientes.xls!Lista_de_Clientes
Hay que prestar atención al signo de exclamación entre el nombre del cuaderno remoto y el nombre que hemos definido en él.
Ahora creamos la lista desplegable en la hoja1 del cuaderno validación.xls usando el nombre que acabamos de definir ("clientes")
Ahora podemos ver que la lista desplegable en validación.xls usa los valores del rango definido en el cuaderno clientes.xls.
Para que esta lista funcione ambos cuadernos deben estar abiertos.
Technorati Tags: MS Excel
viernes, septiembre 26, 2008
Autocompletar en Validación de Datos
Toda persona que haya usado Excel por algún tiempo conoce la funcionalidad Validación de Datos. Una de las características más "sexy" de esta funcionalidad es la posibilidad de crear listas desplegables con facilidad.
En notas anteriores ya hemos mostrado como podemos mejorar aún más esta función creando listas desplegables ordenadas o listas desplegables dependientes.
Pero lo que falta en validación de datos es la funcionalidad autocompletar. Esto es importante cuando tenemos listas con varias decenas o centenas de miembros.
Supongamos que creamos una lista desplegable con todos los países del mundo (o casi todos, según Wikipedia) . Esta lista tendrá 247 miembros. Para facilitar la búsqueda de un país determinado podemos ordenar la lista alfabéticamente, pero de todas maneras si buscamos Zimbabue o Yemen tendremos que pulsar el ratón o el teclado decenas de veces.
La solución es imitar en la lista de validación de datos el comportamiento Autocompletar como existe las celdas de una hoja. La idea es que si pulsamos B aparezca Bahamas, si pulsamos Br aparezca Brasil, etc.
Para lograr esto podemos incorporar a la hoja de Excel un control ActiveX. En este caso usaremos un cuadro combinado (ComboBox).
En nuestro modelo hemos puesto el cuadro combinado sobre la celda B1 y enseguida mostraremos como dirigimos la elección de la lista a la celda B3
Para poder seleccionar el objeto activamos primero el modo de diseño
Luego seleccionamos el cuadro combinado con un clic y abrimos el menú de las propiedades del objeto
En LinkedCell ponemos B3, el ListFillRange ponemos Hoja2!A1:A247 (que es donde tenemos la lista de países) y en MatchEntry ponemos 1-fmMatchEntryComplete
También podemos cambiar otras propiedades como la fuente y el aspecto del objeto.
Cerramos el cuadro de propiedades y cancelamos el modo de diseño haciendo clic sobre el icono.
Ahora al abrir la ventanilla del cuadro combinado vemos los primeros 8 miembros de la lista (también esta propiedad puede ser cambiada en el cuadro de propiedades del objeto). Los miembros aparecen en el orden en que se encuentran en el rango de origen.
Pero lo más interesante es que podemos escribir directamente en la ventanilla y los países aparecerán de acuerdo a las letras que hayamos escrito
Al poner B aparece automáticamente Bahamas
Si agregamos ahora "r", el valor se convierte en Brasil. Nótese que el valor en la celda ligada, B3, se actualiza instantáneamente
Technorati Tags: MS Excel
En notas anteriores ya hemos mostrado como podemos mejorar aún más esta función creando listas desplegables ordenadas o listas desplegables dependientes.
Pero lo que falta en validación de datos es la funcionalidad autocompletar. Esto es importante cuando tenemos listas con varias decenas o centenas de miembros.
Supongamos que creamos una lista desplegable con todos los países del mundo (o casi todos, según Wikipedia) . Esta lista tendrá 247 miembros. Para facilitar la búsqueda de un país determinado podemos ordenar la lista alfabéticamente, pero de todas maneras si buscamos Zimbabue o Yemen tendremos que pulsar el ratón o el teclado decenas de veces.
La solución es imitar en la lista de validación de datos el comportamiento Autocompletar como existe las celdas de una hoja. La idea es que si pulsamos B aparezca Bahamas, si pulsamos Br aparezca Brasil, etc.
Para lograr esto podemos incorporar a la hoja de Excel un control ActiveX. En este caso usaremos un cuadro combinado (ComboBox).
En nuestro modelo hemos puesto el cuadro combinado sobre la celda B1 y enseguida mostraremos como dirigimos la elección de la lista a la celda B3
Para poder seleccionar el objeto activamos primero el modo de diseño
Luego seleccionamos el cuadro combinado con un clic y abrimos el menú de las propiedades del objeto
En LinkedCell ponemos B3, el ListFillRange ponemos Hoja2!A1:A247 (que es donde tenemos la lista de países) y en MatchEntry ponemos 1-fmMatchEntryComplete
También podemos cambiar otras propiedades como la fuente y el aspecto del objeto.
Cerramos el cuadro de propiedades y cancelamos el modo de diseño haciendo clic sobre el icono.
Ahora al abrir la ventanilla del cuadro combinado vemos los primeros 8 miembros de la lista (también esta propiedad puede ser cambiada en el cuadro de propiedades del objeto). Los miembros aparecen en el orden en que se encuentran en el rango de origen.
Pero lo más interesante es que podemos escribir directamente en la ventanilla y los países aparecerán de acuerdo a las letras que hayamos escrito
Al poner B aparece automáticamente Bahamas
Si agregamos ahora "r", el valor se convierte en Brasil. Nótese que el valor en la celda ligada, B3, se actualiza instantáneamente
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.
Technorati Tags: MS Excel
sábado, septiembre 20, 2008
Controlar valores fuera de rango en Excel
Ya hemos escrito en alguna nota sobre ciertos problemas que pueden surgir al usar validación de datos para controlar los valores que son introducidos en una celda.
Supongamos esta lista de números en el rango A1:A10, para el cual hemos definido como valores permitidos sólo números enteros en 50 y 100
Si intentamos poner, por ejemplo, 48 Excel genera un mensaje de error
¿Cómo es entonces que en la lista aparecen valores "ilegales"? Esto se debe a que Excel controla la validez de los datos en el momento de ser introducidos manualmente. Si los datos existen en la hoja antes de haber definido la regla de validación de datos o si copiamos los datos de otra fuente y los pegamos en la hoja, Validación de datos no funciona.
Una forma rápida de controlar la validez de valores en situaciones como las descritas en el párrafo anterior es usar la barra de Auditoría de fórmulas
Esta barra tiene un botón para rodear con círculos valores no válidos
En nuestro caso, al apretar el botón vemos este resultado
Al reemplazar el valor inválido por uno permitido, el círculo desaparece. Esto hace que este método sea muy cómodo.
Por ejemplo, si tenemos que ubicar un valor determinado en una tabla existente, podemos definir una regla para validación de datos que excluya este valor y luego usar auditoría de fórmulas para ubicarlo.
Si queremos ubicar el valor 561 en esta tabla,
definimos validación de datos con la fórmula =A1<>561 (asegurándonos que la elda activa sea A1 y que usamos referencias relativas)
Luego usamos auditoría de fórmulas para ubicar la celda que contiene el valor
Para ubicar duplicados podemos usar esta fórmula en validación de datos
=CONTAR.SI($A$1:$F$14,A1)=1
Al aplicar auditoría de fórmulas vemos que el valor 360 se repite
Supongamos esta lista de números en el rango A1:A10, para el cual hemos definido como valores permitidos sólo números enteros en 50 y 100
Si intentamos poner, por ejemplo, 48 Excel genera un mensaje de error
¿Cómo es entonces que en la lista aparecen valores "ilegales"? Esto se debe a que Excel controla la validez de los datos en el momento de ser introducidos manualmente. Si los datos existen en la hoja antes de haber definido la regla de validación de datos o si copiamos los datos de otra fuente y los pegamos en la hoja, Validación de datos no funciona.
Una forma rápida de controlar la validez de valores en situaciones como las descritas en el párrafo anterior es usar la barra de Auditoría de fórmulas
Esta barra tiene un botón para rodear con círculos valores no válidos
En nuestro caso, al apretar el botón vemos este resultado
Al reemplazar el valor inválido por uno permitido, el círculo desaparece. Esto hace que este método sea muy cómodo.
Por ejemplo, si tenemos que ubicar un valor determinado en una tabla existente, podemos definir una regla para validación de datos que excluya este valor y luego usar auditoría de fórmulas para ubicarlo.
Si queremos ubicar el valor 561 en esta tabla,
definimos validación de datos con la fórmula =A1<>561 (asegurándonos que la elda activa sea A1 y que usamos referencias relativas)
Luego usamos auditoría de fórmulas para ubicar la celda que contiene el valor
Para ubicar duplicados podemos usar esta fórmula en validación de datos
=CONTAR.SI($A$1:$F$14,A1)=1
Al aplicar auditoría de fórmulas vemos que el valor 360 se repite
Technorati Tags: MS Excel
Suscribirse a:
Entradas (Atom)