Esta funcionalidad permite controlar el tipo de datos que son introducidos en un rango determinado. Existen varias posibilidades
Las más interesantes, en cuanto a posibilidades y flexibilidad, son Lista y Personalizada.
Lista permite generar una lista desplegable de la cual puede el usuario elegir valores válidos.
Ya hemos mostrado diferentes técnicas para poblar la lista, en especial haciendo uso de nombres, para referirnos a rangos que se encuentran en otra hoja y para establecer referencias dinámicas.
En una entrada anterior mostraba como crear listas desplegables dependientes. Es decir, que los valores de una lista de validación de datos dependan de la elección de un determinado valor en otra lista.
Hoy veremos como agregar valores únicos a una lista desplegable de validación de datos.
El archivo con el ejemplo se puede descargar aquí.
La técnica consiste en generar una lista de valores únicos en un rango de alguna hoja, que será la referencia de la lista de validación de datos.
Para lograrlo usamos una tabla auxiliar con dos rangos.
En el rango "Valores únicos" usamos la fórmula =SI(CONTAR.SI($A$5:A6,A6)=1,A6,"")
Esta fórmula nos permite establecer si un valor aparece más de una vez. Es importante prestar atención a la definición del rango $A$5:A6, donde la primer celda tiene una referencia absoluta y la segunda una referencia relativa.
En el rango "No. De Orden" generamos un número que nos servirá de argumento en la fórmula que generará la lista de valores únicos.
=SI(CELDA("contents",C5)="","",FILA(C5))
El número es el número de fila sólo par los valores que aparecen por primera vez. Esta fórmula usa como argumento el resultado de la fórmula del rango "Valores únicos".
En el rango "Lista" usamos la fórmula
INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))
para hacer aparecer los valores que tiene número de orden.
Para evitar resultados #¡NUM!, agregamos una condición (en color verde) para evaluar resultados de error
=SI(ESERROR(INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))),"",INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25)))
Una explicación detallada de las fórmulas se puede leer aquí.
Categorías: Funciones&Formulas_, Varios_
Technorati Tags: Validacion de Datos
hola podrias dar un poco mas de explicacion de cada paso, al ser formulas muy complejas, es muy dificil de entender para un usuario de un nivel bajo-intermedio unas explicaciones tan concretas.
ResponderBorrarmuchas gracias y enhorabuena por su blog.
Hola,
ResponderBorraren estos días estoy muy ocupado con un proyecto. Prometo publicar una explicación detallada en breve.
Hola Jorge.
ResponderBorrarDesde hace un tiempo que no se pueden bajar los archivos xls de ejemplos que ofreces. ¿Existe algún otro sitio desde el que se puedan bajar? Gracias.
Hola, estoy buscando una alternativa al Filelodge, que se ha vuelto imposible. Espero poder resolverlo en pocos días.
ResponderBorrarEntre los proyectos del 2007 figura abrir un sitio propio para estos archivos, pero esto llevará un tiempo.
Mientras tanto, si me das tu dirección e-mail te puedo mandar el archivo.
Hoal Jorge muy buenos tus blogs, por favor me puedes ayudar, tengo una columna con datos y no quiero que haya duplicados, y cuando valido datos no me permite introducir datos nuevos Gracias
ResponderBorrarHola, sigue las instrucciones que aparecen en la nota sobre evitar duplicados con validación de datos.
ResponderBorrarJorge: tu blog es excelente y es un verdadero orgullo que seas argentino. Me creía un usuario avanzado antes de conocerlo pero ahora me doy cuenta lo mucho que me falta aprender. Para mí sos un genio! Muchas gracias.
ResponderBorrarMi consulta es:
Quiero usar como validación de celda dentro de la opción Lista - Origen un rango sin duplicados que se ajusta dinámicamente (tal como lo haces en el archivo "valores unicos en lista desplegable.xls" rango Lista usando desref)pero que pueda elegirse con la función indirecto (como lo haces en la nota de los paises y capitales).
Es posible? Cómo?
Hola
ResponderBorrarINDIRECTO sólo acepta rangos como argumento, por eso cuando tratás de pasar como argumento un rango definido con DESREF, el resultado es un error. Una solución es definir los rangos de valores únicos con suficiente filas para que a medida que se vayan agregando valores éstos aparezcan en la lista.
Jorge:
ResponderBorrarComo hago para que la Lista desplegable de validación de celda aparezca con el primer registro seleccionado en lugar de el último? Como agregué varias filas en blanco ahora cuando despliego la lista me aparece seleccionado el último dato que es un blanco y debo subir a ver los datos moviendo la barra de desplazamiento.
Jorge: Usando tu nota de "Listas desplegables dependientes múltiples" encontré la solución. En lugar de definir la validación de la Lista con la función: =Indirecto(q9)
ResponderBorrarlo hago con:
=DESREF(INDIRECTO(Q9),0,0,CONTARA(INDIRECTO(Q9))-CONTAR.BLANCO(INDIRECTO(Q9)),1)
y así obtengo solo los registros con datos en la lista sin incluir los vacíos. Insisto, sos un genio! Gracias.
No tengo mas que felicitarte por tu blog, es sencillamente genial, y para mí motivo de consulta permanente. Apelando a tu paciencia paso a plantearte mi situación, lo mas conciso que puedo:
ResponderBorrarTengo en una columna un listado de una cantidad muy importante de números de Facturas (mas de 2000, y se irán agregando), las cuales se van cargando (columna “abonadas”-lista desplegable-) conforme se abonan. El problema es el siguiente : Cómo puedo hacer para que la lista se vaya reduciendo en la medida en que las voy cargando en la columna de “abonadas”.
Desde ya Muchas gracias!!, saludos Héctor.-
Fofi,
ResponderBorrartenés que usar una columna auxiliar para indicar el estado de la factura (abonada, no abonada). Luego usamos esa columna como criterio para crear la lista de valores. Por ejemplo, supongamos que la lista de facturas está en el rango A2:A15. En el rango B2:B15 ponemos "abonada" donde corresponda. EN el rango C2:C15 ponemos esta fórmula:
=SI(ESBLANCO(B2),A2,"")
En el rango D2:D15 ponemos
=SI(LARGO(C2)<>0,FILA(),"")
Finalmente en E2:E15 creamos la lista de valores con
=INDICE($C$2:$C$15,COINCIDIR(K.ESIMO.MENOR($D$2:$D$15,FILA()-1),$D$2:$D$15))
Los valores #NUM! los ocultamos con formato condicional.
Jorge, en la validacion de datos, la fecha para desplegar la lista solo me aparece cuando doy click a la celda. Es posible hacer que siempre aparezca en todas las celdas con validacion? Tengo plantillas en las cuales no se como indicar visualmente que algunas celdas poseen listas desplegable.
ResponderBorrarNo, las flechas sólo aparecen cuando se selecciona la celda.
ResponderBorrarComo alternativa podés poner combobox de la barra de formularios o de la barra de controles ActiveX.
Excelente todo el desarrollo Jorge, esta soluciones son muy útiles para la comunidad. Una pregunta, como tendría que hacer para que en la columna "F" los valores aparezcan ordenados alfabéticamente?
ResponderBorrarSaludos
Marcelo B.
Marcelo,
ResponderBorrarfijate en esta nota.