miércoles, 14 de octubre de 2009

Ejemplo de doble Validación condicionada.

Pregunta un lector sobre la forma de realizar una validación condicionada

Cómo poder hacer lo siguiente:
1. Si la celda A1 introduzco el valor 1, entonces la celda B1 solo puede permitir los numeros 1, 2 o 3.
2. Pero si en la celda A1 introduzco el valor 2, entonces la celda B1 solo puede permitir los valores 4, 5 y 6


Para dar solución a esta duda tendremos que Asignar nombres a los distintos rangos, para luego mediante la Validación configurar los posible valores. Podremos jugar y experimentar más adelante con los rangos dinámicos.
El problema que surge al tratar de vincular el valor de una celda con el de otra celda, siendo en ambos casos valores numéricos, es que Excel nos devuelve un error, puesto que entiende que pretendemos emplear valores genéricos ya utilizados en otras partes de la aplicación; por lo que para salvar este inconveniente convertiremos en texto algunos de estos valores.
Vamos por partes, en primer lugar supongamos que es esta la distribución de posibles valores:


Pretendemos que cuando en la celda A2 seleccione un valor de los posibles en el campo 'Opción1', como primer nivel, en la celda B2 automáticamente sólo me permita desplegar y seleccionar aquellos existentes en las columnas F, G y H, como segundo nivel.
Antes de continuar, es fundamental dar una forma de texto a las cabeceras de las columnas F, G y H, mediante la función =TEXTO(valor;formato) para cada agrupación de segundo orden; esta función convierte en texto cualquier valor numérico, en nuestro ejemplo
=TEXTO(1;"#") convertimos el valor 1 en un texto con un formato de un caracter,
=TEXTO(2;"#") convertimos el valor 2 en un texto con un formato de un caracter,
=TEXTO(3;"#") convertimos el valor 3 en un texto con un formato de un caracter;

Una vez realizada esta conversión podremos comenzar a Asignar nombres a cada agrupación de valores, ya sean de primer o segundo nivel; recordemos cómo lo hacíamos viendo la entrada Asignar nombres a rangos; en concreto, para nuestro ejemplo, los nombres asignados son:
Opción1=Hoja1!$D$2:$D$4
_1=Hoja1!$F$2:$F$3
_2=Hoja1!$G$2:$G$4
_3=Hoja1!$H$2:$H$4
Nos fijamos en la forma particular en que Excel a dado los nombres a las agrupaciones de segundo nivel, y es que a pesar de la conversión en texto, Excel no puede entender un Nombre como un valor numérico genérico; sin embargo esto podremos salvarlo.
Llegamos al último paso, la Validación. Para la celda A2 configuramos la siguiente validación:


y para la celda B2:


Fijémonos en la función empleada en este paso:
=INDIRECTO("_"&$A$2)
es aquí donde hemos 'corregido' esa particularidad de Excel, el guión bajo; lo que conseguimos con esta función es que la Validación busque en el rango que sea igual a un guión bajo seguido del valor elegido en la celda A2.
Como se puede ver no he configurado los mensajes de error o títulos de esas celdas validadas, pero podríamos hacerlo igual que en cualquier otro caso, entiendo que no es básico para el desarrollo del ejemplo.

Probamos que todo funcione correctamente desplegando y seleccionando un valor en la celda A2, y vemos que efectivamente la celda B2, al desplegar la lista, sólo nos devuelve los valores correspondientes:


105 comentarios:

  1. Como puedo asignar una condicional a una formula si se me dice que debo agregar una unidad cada 10 unidades de otras, que tengo en una celda dentro de una hoja de trabajo. Este valor puede oscilar en función de otros datos., es por ello que te dan esa fórmula. Gracias de antemano. se como asignar la primera condicional > = a 10marcar 1 ó 0 pero cada 10 más, por ejemplo 23 no sabría como poner que me reflejase 2. Gracias otra vez.

    ResponderEliminar
  2. Hola Gracyluis,
    si he comprendido correctamente tu pregunta, tienes una celda con un valor numérico (celda origen), cualquiera, y lo que se pretende es asignar un otro valor formulado en función de la primera celda origen; de tal forma que si el origen está entre 0 y 10 el resultado sea 1, si el origen está entre 10 y 20 (10 más) el resultado sea 2 (1+1), entre 20 y 30 resultado 3, etc.
    En este caso, suponiendo que el valor origen no tiene ningún límite de valor yo no utilizaría la función SI, puesto que esta limita tus condiciones a 7. Creo que lo más sencillo es que encuentres una norma, formulada, que te devuelva aquellos resultados.
    Por ejemplo,
    =ENTERO(celda origen/10)
    esta función te devolverá para cada intervalo de números de diez un resultado incrementado en uno por decena...
    Espero haber entendido correctamente tu cuestión.
    Un saludo

    ResponderEliminar
  3. AMIGO LA VERDAD NO SE COMO EXPLICARTE, PERO ESPERO ME ENTIENDAS, MIRA LO QUE QUIERO HACER ES LO SIGUIENTE:

    TENGO LA SIGUIENTE INFORMACION

    INFRAESTRUCTURA TIPO

    VIAS A
    COLEGIOS B
    CENTROS DE SALUD C

    MIRA CUANDO ELIJA LA OPCION VIAS QUIERO QUE EN LA OTRA CELDA ME APARESAC UN MENU DESPEGABLE CON: AUTOPISTA,FERREA,TROCHA CARROZABLE. CUANDO ELIJA LA OPCION COLEGIOS ME APARESCAN LA OPCION INICIAL, PRIMARIO, SECUNDARIO, SUPERIOR. Y CUANDO ELIJA LA OPCION CENTROS DE SALUD ME APRESACAN LAS OPCIONES HOSPITAL, POSTA MEDICA, CLINICA.


    ESPERO ME HAYA COMPRENDIO Y OJALA ME PUEDAS AYUDAR. mi correo es ing.mijespab@gmail.com , TE LO AGRADECERIA BASTANTE

    ResponderEliminar
  4. Hola,
    te mando al correo indicado el ejemplo, aunque ya te digo que es igual al expuesto en:
    http://excelforo.blogspot.com/2009/10/ejemplo-de-doble-validacion.html
    Espero te sea de utilidad.
    Slds

    ResponderEliminar
  5. Yo deseo que aL elegir un rango de la lista deslizable, en la otra columna me aparezca el valor correspondiente, mis datos son:
    dias tiempo
    1 - 6
    2 - 10
    3 - 15
    4 - 22
    .....
    osea que cuando elija en la lista deslisable el dia 4, en la otra columna me aparezca en automatico el 22, si elijo 1 me aparezca el 6 y asi sucesivamente x q mis datos llegan hasta el dia 50.... GRACIAS X AYUDARME!!!

    ResponderEliminar
  6. Hola,
    lo que debes hacer es asignar nombre al rango de 'días' e incluirlo en un desplegable, por ejemplo una celda validada tipo lista, por ejemplo la D4; a continuación incluye el la celda E4 una función de búsqueda vertical
    =BUSCARV(D4;A1:B50;2;0)
    con lo que conseguiras obtener el dato de 'tiempo'.
    Slds

    ResponderEliminar
  7. Hola, esoty iniciando en esto, y mi duda es similar a la anterior preguntada, pero no consigo hacerlo

    He creado una lista validada quedando asi:

    Orientacion:

    Norte
    Sur
    Este
    Oeste

    Y lo que quisiera ahora, es que en la celda contigua, al elegir la orientacion automaticamente me cogiese un coeficiente asignado a esa orientacion

    Gracias y un saludo

    ResponderEliminar
  8. Hola,
    a pregunta similar respuesta similar ;-)
    En una celda, por ejem, A1 tienes una celda validada con tipo Lista (desplegando los valores Norte, Sur, este y Oeste), en la celda contigüa, B1 incluirías la función
    =BUSCARV(A1;Hoja2!A:B;2,0)
    Siendo en la hoja2 donte tienes tu correspondencia de valores, en la columna A la orientación y en la columna B el coeficiente asignado.
    Espero te sirva de ayuda.
    Slds

    ResponderEliminar
  9. Sinceramente la guía es muy poco clara, podría ser más corto y concreto. Sin embargo, les agradezco porque pude entenderla con ayuda de otras guías y aplicarla.

    ResponderEliminar
  10. Hola Ramiro,
    el agradecido soy yo por tus comentarios y aportes, así como por tu crítica constructiva.
    Un cordial saludo

    ResponderEliminar
  11. Hola! Soy principiante en excel y tengo una duda: puedo hacer que me cambie un valor numérico por un texto? Me explico: he creado una hoja para poner las notas de mis alumnos y quiero establecer que si la nota de un alumno está entre 100% y 95%, me salga en otra casilla "Excelente"; si la nota está entre 94% y 90%, que salga "Notable"; etc... Eso se puede hacer???? muchas gracias

    ResponderEliminar
  12. Hola...

    mira estoy haciendo la doble validacion con numero me salio bien, pero al colocar palabras no me sale la validacion .....

    lo que quiero hacer es.

    columna perro carro
    perro a c
    carro b g

    espero que me ayudes gracias!!

    ResponderEliminar
  13. Hola,

    Necesito ayuda en la columna A2 tengo la cantidad de $1,400.00 y en la columnas siguientes B2,C2... las fechas en que se hicieron los pagos quiero que la fecha adopte el valor de A2 para luego sumar el total.

    A B C D E
    1 CUOTA JULIO AGOSTO SEPT TOTAL
    2 1,400 31/7/12

    3 TOTAL TOTAL...

    ResponderEliminar
    Respuestas
    1. Hola,¿podrías explicarlo de otra manera?.
      Me cuesta entender qué necesitas?.
      Un saludo

      Eliminar
    2. De antemano, muchas gracias por responder. Necesito que el texto (fecha columnas B2,C2...) coja el valor de la cuota $1,400 (Columna A2) para luego hacer la sumar total y saber cuanto han pagado durante los meses. (B2+C2+D2+....)

      Eliminar
    3. Envíame un fichero con un ejemplo (aunque sea con valores fijos) a
      excelforo@gmail.com

      Slds

      Eliminar
    4. Hola otra vez, te mande por email la ficha, espero me ayudes si es posible, esta muy complicado. GRACIAS

      Eliminar
  14. necesito ejemplos de doble validacion

    ResponderEliminar
  15. Hago una consulta, necesito colocar en una misma columna dos reglas de validación, como se hace?
    Ejemplo, necesito que en la columna A se introduzca un numero con un largo determinado de caracteres y que a su vez no se repita ningún dato.
    ej necesito poner 00001, 00002 y que no pueda poner nuevamente 00002 nuevamente ni tampoco 002.

    muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Javier,
      el problema que veo en tu planteamiento, es que si exiges un número determinado de caracteres, como pones en el ejemplo 00001, 00002 etc (exiges entonces que el número de caracteres sea 5) y no se puedan repetir valores ya dados en la columna A, si quieres que admita ese formato '00001' deberás tratar los datos como texto.
      Si admitimos esa condición de datos tipo texto, para asignar dos reglas (o más) de validación tienes que irte a permitir personalizado.
      Puedes echar un vistazo a
      http://excelforo.blogspot.com.es/2010/03/validacion-de-datos-personalizada.html
      o
      http://excelforo.blogspot.com.es/2011/10/validacion-de-datos-personalizada.html

      En tu caso la validación personalizada sería:
      =Y(LARGO(A1)=5;CONTAR.SI(A:A;A1)=1)
      creando la validación con la columna A seleccionada, y la celda A1 activa.

      Espero te sirva
      Slds

      Eliminar
    2. Muchisimas gracias!
      lo único que tuve que cambiar fue que en vez de colocar ";" tuve que usar "," pero sacando ese detalle la formula que me pasaste funciono a la perfección.
      Nuevamente Gracias!!

      Eliminar
    3. Gracias a ti Javier,
      lo del ; o , depende de la configuración de sistema que utilices... cada persona lo tiene de una manera, yo utilizo la más extendida.
      Me alegro te sirviera.
      Slds

      Eliminar
  16. Buenas! Lo primero decirte que me aclaraste muchísimo y que me ha servido de gran ayuda! GRACIAS.
    Sin embargo mi problema es el siguiente: ¿cómo puedo arrastrar esa validacion de datos para que me la ajuste a las filas siguientes? Pongamos de ejemplo que la casilla B2 está condicionada a la A2, y ya funciona perfectamente, pero si arrastro la celda, la casilla B3 me aparece condicionada a la A2, y no a la A3.
    Gracias de antemano por tu respuesta
    Un cordial saludo

    ResponderEliminar
    Respuestas
    1. Hola Esther!!
      para que te aparezca relacionado como indicas,tendrías que arrastrar sin más, pero ojo, la fórmula, esto es, el origen en la configuración de la validación debe estar sin fijar; en tu caso: A2 y no $A$2...
      En el fondo opera igual que una función o fórmula en la hoja de cálculo.

      Otra manera es seleccionar primero el rango donde vayan las validaciones, e insertar después la Validación de datos, pero igual que antes sin fijar filas.

      Espero haberme explicado.
      Slds

      Eliminar
  17. Hola amigos de excel foro, les quiero comentar que cree una hoja excel en la cual utilice la validacion de datos, con una lista, segui toods los pasos, trabaje con ingreso de los datos de la lista y guarde el archivo, mas al otro dia abro el archivo pasa seguir ingresando los datos validados, sorpresa que no se ha guardo la validaciomn y tengo que comenzar de nuevo a validar, alguien me puede explicar como fijar bien esta validacion de datos gracias
    oscar
    ogomezcabrera@yahoo.es

    ResponderEliminar
    Respuestas
    1. Hola Oscar,
      no tiene nada de particular trabajar con validación de datos; es suficiente Guardar antes de cerrar.

      Quizá te falló el sistema o por error presionaste no guardar cambios.
      Slds

      Eliminar
  18. Antes que nada excelente foro me encanta y me ha sacado de muchos apuros. En esta ocasión tengo un par de dudas: 1) como le hago para sacar de una "tabla de docentes" los nombre que empiecen con "M" y que además contengan la letra "R". ejemplo: que incluya a: María pero que excluya a: Moises y 2) en la misma "tabla de docentes" quiero sacar los datos de de maestros "activos" pero que den materias de química y matemáticas.

    mi tabla esta constituida por

    A1 B1 C1
    "nombre activo/inactivo materias"

    Gracias y Saludos.

    Arturo V.

    ResponderEliminar
    Respuestas
    1. Hola Arturo,
      yo emplearía un Autofiltro personalizado sobre el campo 'Nombre', con el operador igual a: M*r*
      los asteríscos son uno de los comodines de Excel, e implican varios caracteres. Con M*r* decimos que muestre los elementos del campo que comiencen con 'M' y tengan una 'r' en algún sitio.
      Slds

      Eliminar
  19. hola quisiera saber si puedo auto completar en una celda con datos de otra hoja, es decir si en la celda a1 escribo s , automaticamente busque coincidencias de palabras con esa inicial, desde un rango que este en la hoja 2. agradesco ya de por si tu respuesta,
    saludos

    ResponderEliminar
  20. Hola, quería saber si es posible que, al agregar un número en una celda, en otra, me aparezca automáticamente una lista desplegable. En este ejemplo, ambas son listas, pero lo que necesito es que, al colocar un numero o una edad, en la celda continua me aparezca una lista desplegable. Es algo parecido a una funcion SI pero con listas desplegables. Espero tu rpta. Slds,

    ResponderEliminar
    Respuestas
    1. Hola,
      no es posible usando simplemente la Validación de celdas, sin embargo, usando alguna macro que haga visible un Control(ActiveX) tipo ComboBox condicionado al valor de una celda resultaría. Algo de este estilo:
      If Range("E2").Value > 10 Then
      ComboBox1.Visible = True
      Else
      ComboBox1.Visible = False
      End If


      También, empleando macros, y asignando a una celda una Validación de datos determinada (tipo Lista) en función de un valor de otra celda, o bien sin Validación según otros valores...

      En cuallquier caso siempre empleando macros.

      Slds

      Eliminar
  21. Hola, Muchas gracias por los ejemplos y de antemano por tu atención. Lo que yo necesito es que al poner la opción en la opción 2 solo me permita meter valores ligados a lo que lo que puse en la opción 1. Tal cual esta en el ejemplo, pero sin la lista despegable. Yo tengo demasiados datos por opción y la lista no es practica. Prefiero tener que tipear la opción 2, pero que no me permita meter un valor que no corresponde a la lista de lo que ingrese en la opción 1. No se si me explique bien.

    ResponderEliminar
    Respuestas
    1. Hola Eduardo,
      bueno, el mecanismo será exactamente igual, ya que se trata de la misma herramienta 'Validación de datos'.
      Realiza los mismos pasos pero en la celdas que no quieras te aparezca el desplegable, simplemente en la ventana de configuración desmarca la opción 'Celda con desplegable' (opción que se activa sólo al permitir tipo Lista.

      Lo que se consigue con esto es la misma funcionalidad que lo explicado en el post, pero sin que en la celda aparezca la flechita de desplegable. Pero en ambos casos (con o sin flecha) la posibilidad de escribir o tipear los valores permitidos en la celda están habilitados.

      Espero haber explicado.
      Un cordial saludo

      Eliminar
  22. Muchismas gracias Ismael por tu pronta respuesta. Excelente funciono de maravilla

    ResponderEliminar
  23. Me ha servido la información aunque no he terminado lo que deseo.

    Como ejemplo pongo una paletería donde se venden 2 tipos de paletas,
    hielo
    maciza

    Si escojo la paleta de hielo entonces puedo escoger otras dos opciones. Leche, agua.

    Hasta aquí no tengo problemas, puedo hacerlo con las validaciones, el asunto es que de acuerdo a la especificación "leche, agua" también tengo otro desplegado, que serian los distintos sabores, diferentes para ambos.

    Cómo puedo hacer esto?

    ResponderEliminar
    Respuestas
    1. Hola Erick,
      la idea es la misma, daría igual un segundo que un tercer nivel.
      Sobre el nivel I (Hielo, Maciza), aplicamos el segundo nivel (Leche, Agua) empleando los nombres definidos con la función INDIRECTO; el tercer nivel que propones (Sabores) en función al segundo, se construiría con nuevos nombres definidos =INDIRECTO(Leche) o =INDIRECTO(Agua).

      La clave es la creación de Nombres definidos.
      Slds

      Eliminar
  24. Cómo poder hacer lo siguiente:
    1. Si en la celda A1 introduzco el valor 1, entonces que en la celda B1 solo pueda permitir los numeros 1, 2 o 3. (esto es similar al post)
    2. Pero si en la celda A1 introduzco el valor 2, entonces en la celda B1 pueda introducir cualquier valor.
    Gracias de antemano

    ResponderEliminar
    Respuestas
    1. Hola, no es sencillo el tema.
      Pero si las condiciones son tan cerradas como las que planteas, entonces selecciona la celda B1, entra en validación de datos personalizada y en el campo fórmula introduce:
      =O(Y($A$1=1;O($B$1=1;$B$1=2;$B$1=3));$A$1=2)

      Te funcionará como indicas.
      Slds cordiales

      Eliminar
    2. Hola a todos y muchas gracias por todas las ayudas, ¡son magníficas!

      ¿Y si en un caso como este necesitas que, si en A1 figura el valor 2, en B1 se pueda escribir, no cualquier valor, sino cualquier número entero? ¿cómo habría que modificar la fórmula?
      Muchas gracias de antemano por vuestra ayuda y un saludo.

      Eliminar
    3. Hola,
      situado en B1, configuras la validación personalizada:
      =Y(A1=2;B1=ENTERO(B1))
      Saludos

      Eliminar
    4. ¡¡¡Fantástico!!!

      Muchas gracias y un saludo

      Eliminar
  25. Hola Ismael,
    Gracias por la pronta respuesta, si embargo las condiciones no son tan cerradas como las que te he planteado. Igualmente voy a probar a partir de tu gran aporte.
    Slds

    ResponderEliminar
    Respuestas
    1. Hola!!
      ta te había comentado que no era sencillo el planteamiento... son muchas las variables que hay que cerrar para el planteamiento 'sencillo' inicial, por lo que a más parámetros más complicado el tema...
      Suerte y comentas.

      Eliminar
  26. Hola Ismael,
    Opté por la opción más sencilla, terminé utilizando la opción de validar con listas (similar la post con la función Indirecto), solo que seleccioné en el mensaje de salida la opción Información y no mostrar el mensaje (de esta manera permitiría colocar cualquier valor en la celda B1). Solo tengo que informar muy bien a los usuarios que si en la celda B1 se desplega una lista, que elijan valores de la misma. Se que quedo expuesto a que ingresen cualquier valor, pero tendré que monitorear de cerca los primeros tiempos.
    Nuevamente muchas gracias por tu ayuda.
    Slds

    ResponderEliminar
    Respuestas
    1. Bueno,
      a veces no queda más remedio que quedar en manos del usuario...

      Quizá con macros si se podría configurar, comprobando a posteriorí los datos introducidos...
      Slds

      Eliminar
  27. Hola, tengo un caso similar pero un tanto más complicada, me explico tengo las siguentes columnas
    *ÁREA_DE_ESTUDIO
    *LIBRO
    *Nombre_del_Autor
    *Editorial
    para la primera he realizzado una validación de datos con indirecto, lo cual no tengo problema
    la segunda de la misma forma, tampoco tengo inconveniente
    la tercera ahí es cuando mi problema surge pues ejemplo del libro Pepito del área Literatura tengo 4 pero son distintos autores y quiero que en el listado me aparezca estos cuatro y no todos los autores de los libros, no puedo poder rangos pues sería innumerables (más de 3000)
    y en editorial quiero que me aparezca el listado solo de las editoriales que tenga el área de literatura, el libro pepito y el autor XX, asi ejemplo me aparecería para escoger entre dos editoriales
    EN RESUMEN quiero que cada vez se vaya filtrando más al momento de elegir, trate de hacer en la validación con formula BUSCARV pero no me permite. les agradezco la ayuda!!!!

    ResponderEliminar
    Respuestas
    1. Hola Silvia,
      prueba con esta entrada, aunque presupone que tienes listas separadas para cada 'filtro' aplicado
      http://excelforo.blogspot.com.es/2010/04/validacion-de-celdas-anidadas-y.html

      Creo que cualquier otra solución pasaría por macros bastantes complejas. Con fórmulas aplicadas quizá se podría conseguir alguna cosa, pero posiblemente los listados aparecieran con espacios en blanco... es decir, es como si del listado completo sólo aparecieran con valor los que cumplan los criterios y en blanco los demás...

      Slds

      Eliminar
  28. Buenos días
    No sé si lo que yo quiero se podrá hacer...
    Tengo un archivo de Excel donde guardo presupuestos y con fórmulas para que me calcule, el iva, la comisión... etc
    Mi pregunta es, cómo puedo apuntar para el mismo cliente varios presupuestos sin que esto me contabilice todos los trabajos, cuando finalmente sólo nos quedaríamos con el más barato (quiero apuntarlo porque en ese archivo es donde llevamos todo el control...)
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola!,
      la verdad no comprendo bien tu planteamiento... hasta donde entiendo comentas que tienes un fichero con diferentes registros, y varios presupuestos para un mismo cliente (me imagino en diferentes filas/registros)... y quiers trabajar con el más baráto.

      La idea, si es así como lo planteo, sería tener una columna para los diferentes columnas y sus columnas adyacentes con los datos formulados IVA, Comisión, Total.
      Para quedarse con el más barato, supongo a determinar en algún otro lugar del libro de trabajo, para encontrar el presupuesto de un cliente en particular (el ppto más bajo), asociaríamos la siguiente fórmula matricial (validar presionando Ctrl+Mayusc+Enter):
      =MIN(SI(A1:A5=E5;B1:B5))
      suponiendo en A1:A5 esté el listado con todos los clientes y en B1:B5 el valor de los presupuestos (de todos), y en E5 el nombre o código de cliente (el mismo que encontraremos en A1:A5).

      Espero te sirva
      Slds

      Eliminar
  29. Aprovecho la oportunidad para agradecerte el incontable numero de veces que me has salvado, la verdad tengo tu blogg entre mis favoritos. La claridad con la que explicas la forma de dar solución a las multiples necesidades de muchos, que debemos usar excel como mano derecha.
    Tu blogg me ha permitido crear algunas cosas que hace un par de años me tomaban días realizar, realmente muy, muy agradecido.

    ResponderEliminar
    Respuestas
    1. Muchisimas gracias por tus palabras, Miguel...
      esta es la motivación para continuar con mi pequeño trabajo.
      Un cordial saludo

      Eliminar
  30. Hola buenas tardes,
    te escribo para realizarte una consulta
    como puedo validar en excel si la primera o segunda
    cifra son ceros, estoy intentando validar que sean
    cuatro números, pero si pongo primero ceros
    no lo coge, solo lo hace bien si los números
    son distintos de cero
    saludos

    ResponderEliminar
    Respuestas
    1. Hola Manuel,
      suponiendo que el número de dígitos máximo sea de cuatro, en la validación de datos personalizada insertaría la fórmula:
      =IZQUIERDA(TEXTO(A1;"0000");2)="00"
      par la celda A1.
      Con esa fórmula nos aseguramos que sólo los números de máximo cuatro dígitos y con los dos primeros cero se permitan en esa celda.
      Si trabajas con más dígitos debes cambiar el formato "0000" por tantos ceros como necesites.
      Saludos

      Eliminar
  31. Hola buena noches. Estoy haciendo un formulario con listas dependientes, todo iba bien hasta que los nombres empezaron a ser ldemasiado largos y parece que excel tiene algun tipo de limite (37caracteres) al marcar los nombres y claro ya no se me desplieda la sigiente lista cuando uso la funcion INDIRECTO ¿habria alguna solucion?lo de acortar el nombre, no es posible pues se necesita como está.
    Gracias de antemano.
    un saludo

    ResponderEliminar
    Respuestas
    1. Hola Luis,
      se me ocurre aplicar un condicional SI con el que recortes los nombres existentes, esto es, crear unos segundos Nombres definidos más cortos (menos de 22 caracteres.. yo diría que ese es el límite), y luego aplicar un condicional dentro del INDIRECTO para que te lo detecte....

      De todas formas no entiendo el por qué de esos Nombres definidos tan largos...

      Espero te oriente
      Saludos

      Eliminar
  32. Hola Ismael, son listas que vienen nombres de la administracion describiendo subdirecciones generales de las que cuelgan secretarias y otros organismos, no se si habra posibilidad de establecer un codigo = al nombre para poder sustituirlo, asi podria seguir. El limite que yo he contado (excel 2010) es el que indicaba 37 caracteres y tengo nombres que se me van a los 52 caracteres, de los que cuelgan otros ya dentro del limite.
    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      creo que podrías emplear una macro que cambie los nombres definidos 'largos' por otros nuevos, más cortos, y que respondan a alguna regla en cuanto al nombre.
      Puedes echas un vistazo a
      http://excelforo.blogspot.com.es/2012/04/cambiar-el-ambito-de-un-nombre-definido.html

      Es bastante parecido en cuanto a la estructura de la macro.
      El trabajo consistiría en ir borrando los nombres definidos existentes y creando unos nuevos con los rangos de los viejos, y de paso cambiar el Nombre.

      Un saludo

      Eliminar
  33. Estimado Ismael, como sería el caso si quisiera un filtro avanzado solo para las fechas de los meses de agosto (por ejemplo) de cualquier año. Gracias por tu respuesta, un abrazo desde Lima. Andrés

    ResponderEliminar
    Respuestas
    1. Hola Andrés,
      deberás crear un rango de criterio en dos columnas sobre el mismo campo.
      Por ejemplo, supongamos que en tu base de datos el campo se llama 'FechaReg'...
      en el rango de criterios añadimos como cabecera en H1 y I1 dos veces 'FechaReg' y 'FechaReg', en H2 añadimos:
      >=01/08/2014
      y en I2
      <=31/08/2014

      puedes ver otros ejemplos en
      http://excelforo.blogspot.com.es/2009/10/un-ejemplo-de-filtro-avanzado-en-excel.html

      Espero te oriente.
      Un cordial saludo

      Eliminar
    2. Gracias por tu respuesta tan rápida estimado Ismael y buena la aclaración pero en mi caso hay más de 10 años y tengo que filtrar solo un mes (agosto por ejemplo) de todos los años.
      Anticipadamente, gracias por tu respuesta y por el tiempo que das para servir a otros, Dios te bendiga.

      Eliminar
    3. Hola!
      podrías optar por filtrar con la condición O (en 10 filas) una por año de manera similar a como te indicaba.. aunque sería algo pesado.
      Otra opción es añadir una columna auxiliar a tu origen de datos con la fórmula
      =MES(fecha)

      y luego aplicar sobre ese campo el filtro (=8)

      Saludos cordiales

      Eliminar
    4. Si, creo que lo mejor es añadir una columna auxiliar. Muchas gracias Ismael!!

      Eliminar
  34. Buen día...

    Estoy realizando una validación de datos, tengo la columna A en donde tengo que ingresar algún número del 1 al 5, y en la columna B si en la columna A seleccione 2 debe aparecer 20 ó 21 pero si es algún otro valor debe aparecer en blanco, en la columna C si selecciona en la B 20, debe aparecer 30 ó 31 de lo contrario en blanco, el detalle que tengo es que si en la columna A selecciono 1 o 3,4 y 5 previo a que en la celda coloque el número 2 las celdas de la columna B y C no se ponen en blanco automáticamente.

    Recibe un cordial saludo y en espera de un comentario...

    ResponderEliminar
    Respuestas
    1. Hola Benjamín,
      es normal que valores introducidos no cambien al variar la primera celda de la columna A.
      Ten presente que esto es una regla de validación de datos al introducir valores en una celda... otra cosa es que nos aprovechemos y explotemos sus posibilidades.. pero esta funcionalidad no lleva asociada ninguna fórmula en la celda, por lo que es normal que no actualice.

      Lo que pretendes, en todo caso, se podría conseguir programando un evento _Change en la hoja que limpiara los valores de esas celdas antes cualquier cambio en ellas...

      Saludos cordiales

      Eliminar
    2. Hola disculpen sé que esto ya tiene sus años, pero busco de su valiosa experiencia y gurú en estos temas, es por ello que les pido de favor me pudieran ayudar, pues tengo un archivo que tiene 2 columnas con lista desplegables la columna A es la principal y lo que se coloque en ella, la segunda columna valida lo que se coloque en la primera y otorga una lista desplegable, pero busco que si en la columna A cambian lo que pudieron elegir de la lista desplegable a otra opción de la misma lista, que borré la celda de la columna B, para que el usuario vuelva a seleccionar dentro de la columna B lo que se eligió en la columna A. Ojalá y pudieran con su vasta experiencia una gran ayuda me serviria muchísimo.

      Eliminar
    3. Hola,
      acciones del tipo que comentas solo se podrían realizar con programación...
      Yo aplicaría un evento hoja _Change para que detecte el cambio en esa columna A y borre la celda correspondiente de la columna B

      Espero haberte orientado
      Sdls

      Eliminar
  35. Hola, espero me puedan ayudar.
    Quiero hacer una formula para textos en excel, de manera tal que si la celda A2 tiene una lista de tres opciones al escoger la primera el resto de las celdas de esa columna cambien con los textos correspondientes y así con la elección de cada opcion en A2.
    Ejemplo A2: Posee perro y las opciones que se desplequen en cada cela sean la razas de perros. Saludos

    ResponderEliminar
    Respuestas
    1. Hola Cris,
      no me queda claro si hablas de una Validación de datos en celdas de un rango o de fórmulas para cambiar el resultado...
      Podrías aclarar un poco el planteamiento??
      Un saludo!

      Eliminar
  36. amigo, necesito saber si se puede con el 0

    que en a1 puedan seleccionar 0 y 1 y segun ello en b1 aparezca 0a9 y 0a2
    a1= 0
    b1= 0 1 2 3 4 5 6 7 8 o 9
    o
    A1= 1
    B1= 0 1 o 2.

    ResponderEliminar
    Respuestas
    1. Hola Patricio,
      el mecanismo es exactamente el mismo que el descrito...
      El problema, por eso del detalle expuesto y el uso de un guión bajo (cualquier otro caracter valdría), es que un número sólo no lo admitiría como nombre definido..

      Por eso en A1 pondrías la validación de datos tipo Lista con valores: 0 y1
      y en B1 la validación con la fórmula indicada..
      Por otro lado debes tener tus dos listas en dos rangos, por ejemplo
      D1:D10 con el nombre definido '_0'
      y
      E1:E3 con '_1'

      Saludos

      Eliminar
  37. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  38. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  39. Favor ayudeme a personalizar con validacion de datos una celda en la cual debe constar asi H- #### una H permanente y cuatro caracteres como numeros

    Le agradecere mucho

    ResponderEliminar
    Respuestas
    1. Hola!
      aplica una Validación de datos personalizada con la siguiente en la celda D5:
      =Y(IZQUIERDA(D5;2)="H-";LARGO(DERECHA(D5;LARGO(D5)-2))=4;ESNUMERO(VALOR(DERECHA(D5;4))))

      Saludos

      Eliminar
  40. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  41. necesito encontrar la forma de hacer lo siguiente, tengo las columnas A,B,C , si en la columna A coloco la letra "X" en la columna b y c no me debe permitir colocar ninguna letra;

    o si es en la columna B donde coloco la letra , las columnas a y c no me deben permitir colocar la letra X y si es en la columna C donde coloco la letra, las columnas a y b no deben permitirme colocar nada

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      selecciona las tres columnas A:C (el rango que necesites), y suponiendo tu celda activa esté en la fila 1, accede a la Validación de datos > Personalizada e incluye la siguiente fórmula:
      =CONTARA($A1:$C1)=1

      Saludos

      Eliminar
  42. Bunos Dìas Ismael que tal, necesito saber como hago para colocar en una celda dos valores distinto y que cada uno este condicionado con distinta formulas, para trabajar con la misma celda dentro de cada formula. Tengo la función “=SI(J=10;(A2+B2)*10/100+E2;SI(J=29(C2>D2);D2/2+C2;C2/2+D2)+E2)”, me esta mostrando error de funciòn.

    Explicación: Son dos datos distintos 10% y 15% validado dentro la misma celda, la formula de 10% es (A2+B2)*10/100+E2; y la formula de 15% cada celda tiene un porcentaje distinto, es decir; C2=5% y D=7%, luego de sacar cada porcentaje, se procede a la formula (C2>D2);D2/2+C2;C2/2+D2)+E2).

    Muchas Gracias por tu colaboración

    ResponderEliminar
    Respuestas
    1. Hola Juan Carlos,
      discúlpame pero no comprendo... ¿quieres que aparezca al mismo tiempo en una sola celda dos valores distintos??..
      si es esto lo que necesitas, en principio, no es posible tal cosa.
      Si puedes explicar algo mejor lo que pretendes...
      ;-)
      Slds

      Eliminar
  43. Muchachos, como están? si quisiera hacer esto con una macro? se podría?

    ResponderEliminar
    Respuestas
    1. Hola Jefferson,
      sí, claro.. se podría, empleando ComboBox o ListBox...
      pero el trabajo sería más laborioso.
      Tendrías que emplear el evento _Change (por ejemplo) en el primer ComboBox para realizar la carga del segundo ComboBox dependiente, en función del .Value desplegado en el primero...

      Slds

      Eliminar
  44. Hola, me gustaría hacer esta pregunta. Tengo una hoja con tres columnas, Columna A: Paises; Columna B: Emisiones; y, Columna C: Número. Como puedo aplicar en la columna C, una regla de validación, para que no se dupliquen los registros, pero en función de las 3 columnas. Es decir, que en la columna C, que normalmente son números, me permita duplicarlos, pero que siempre que no sea el mismo país y la misma emisión.

    ResponderEliminar
    Respuestas
    1. Hola Antonio,
      imagina tus datos en el rango A1:C9, con los datos distribuidos por los campos que indicas:
      Paises - Emisiones - Número

      Selecciona el conjunto de datos desde A2 a C9, asegúrate que la celda activa es la A2... y entra en la validación de datos > tipo Personalizada e introduce la siguiente fórmula

      =CONTAR.SI.CONJUNTO($A$2:$A$9;$A2;$B$2:$B$9;$B2)=1

      Saludos

      Eliminar
    2. Hola Ismael, muchas gracias, por tu contestación. He probado la formula que indicas, pero en el momento que un registro se repita Países y Emisiones, ya no me permite cambiar el campo Número. Y sí debería permitirlo. Lo que no puede permitir es repetir el duplicado en los 3 campos, pero sí en dos. A ver si me ayudas a conseguirlo, porque estoy harto de pensarlo y no lo consigo. Gracias.

      Eliminar
    3. Hola Antonio,
      en el primer comentario indicabas:
      que en la columna C, que normalmente son números, me permita duplicarlos, pero que siempre que no sea el mismo país y la misma emisión.
      Por eso la solución.

      Si no quieres que se repita el duplicado en los 3 campos, pero sí en dos
      Añade la tercera condición dentro de la función
      CONTAR.SI.CONJUNTO
      que te indicaba

      Un cordial saludo

      Eliminar
    4. Muchas gracias, ahora lo he conseguido. No sabe la de vueltas que le he dado a esto. Por fin. Muchísimas gracias.

      Eliminar
  45. Hola,

    Ante la previsión de que mis rangos cambien y se tengan que introducir más referencias a los rangos, los definí con valores vacíos y me serví de laS funciones DESREF y CONTARA para que la lista de validación mostrase únicamente los valores no vacíos de dichos rangos. La fórmula que introduje en la celda es la siguiente:

    =SI(F2=M1;DESREF(XEL1046752;1;;CONTARA(CONN2));SI(F2=N1;DESREF(XEO1046752;1;;CONTARA(GALX2));SI(F2=O1;DESREF(XER1046752;1;;CONTARA(MOND2));SI(F2=P1;DESREF(XEU1046752;1;;CONTARA(SMAX2));""))))

    Mi problema es que con esta fórmula, la validación de datos no me impide introducir cualquier valor, incluso si no está en ninguno de estos rangos, con lo cual todo mi trabajo carece de resultado. ¿Alguien puede ayudarme para completar mi fórmula?

    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      lo ideal es que conviertas tus rangos en Tablas y luego le asignes un nombre definido a ese lsitado... posteriormente podrás emplear en tu validación de datos tipo lista el nombre definido.
      Si revisas la categoría del blog 'Asignar nombres definidos' verás diferentes ejemplos de esta práctica.. más simples que la que intentas
      ;-)

      Saludos

      Eliminar
    2. Muchas gracias por la respuesta Ismael,

      Siguiendo tu consejo lo he hecho mediante tablas, pero me surgen dos problemas:

      1 - Al tener tablas de Excel, no puedo Compartir el libro. Sería lo deseable ya que quisiera que dos o más usuarios pudieran acceder a él, no es imprescindible pero sería perfecto. ¿Hay alguna forma de compartirlo pese a que existan tablas de Excel?

      2 - Este es un problema aparte, con el resto del código. Básicamente lo que hace el código de este archivo es cortar la fila de la tabla cuya celda final tenga un determinado valor, para luego pegarla en otra hoja, bloquear y ocultar esta última, y volver a la primera. El objetivo es tener un histórico de los registros que se van eliminando pero que los distintos usuarios no puedan acceder a esa hoja, ya que se pueden confundir, y solo yo tengo la contraseña. Mi problema es que al añadir estas dobles validaciones, tuve que añadir una columna más a la tabla, para seleccionar primero el modelo, y luego la otra que según el modelo indica unas características u otras. En medio del código para hacer todo esto, antes de hacer la doble validación tenía:

      ActiveSheet.Range("$A:$J").AutoFilter Field:=10, Criterial:="=CERRADO"

      Mientras que ahora, al haber añadido una columna, y estar el campo que quiero filtrar más a la derecha puse:

      ActiveSheet.Range("$A:$K").AutoFilter Field:=11, Criterial:="=CERRADO"

      Además de este cambio, he realizado otros para que las acciones que realizo abarquen toda la tabla en su nueva extensión. Sin embargo, al ejecutar, aparece el error 1004 en tiempo de ejecución: Error en el método AutoFilter de la clase Range, señalando a esta línea de código en concreto que acabo de detallar. En la tabla antigua no tuve ningún problema ¿Qué puedo estar haciendo mal?

      ¡Muchas gracias de nuevo!

      Eliminar
    3. Hola,
      a la primera pregunta: No hay forma de 'Compartir libro' con tablas... tendrías que emplear otras herramientas como One Drive de MS
      Segunda cuestión:
      probaría a quitar primero el filtro y luego a aplicarlo... no se me ocurre que otra cosa puede estar mal ??

      Saludos

      Eliminar
  46. HOLA COMO PUEDO PONER DOS VALIDACIONES PARA UNA MISMA CELDA COMO POR EJEMPLO Q SOLO TENGA 9 DIGITOS Y QUE A SU VEZ NO SE REPITA EN OTRA CELDA

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes añadir una validación personalizada con una fórmula de este estilo:
      =Y(largo(celda)=9;contar.si(rango; celda)=1)

      Esa es la idea
      Saludos

      Eliminar
  47. Buena tarde
    quiero dos validaciones para una misma celda una que no me permita ingresar duplicados y otra que me permita ingresar solo los datos que esten dentro de un listado

    ResponderEliminar
    Respuestas
    1. Hola
      revisa comentarios anteriores por que la pregunta está ya casi contestada.. aplica una validación personalizada con una función Y que verifique las dos condiciones:
      1- duplicados (ver Comentario previo -justo encima)
      2-valores de una lista... OJO sin que aparezca el desplegable - quizá con un ESRROR y BUSCARV que localice el valor introducido en la celda en la lista
      Espero te oriente
      Saludos

      Eliminar
  48. Hola!
    Tengo una lista de nombres de archivos y dependiendo el nombre de dicho archivo, debo asignarle un código con letras y números por ej: P_002.
    Intenté hacerlo siguiendo el ejemplo que muestras más arriba pero no está funcionando. Por favor podrías guiarme?
    Gracias!

    ResponderEliminar
    Respuestas
    1. la lista es bastante extensa y el nro. de código depende puntualmente de si en la fila que contiene los nombres hay un texto u otro.

      Eliminar
    2. Claro! te envié un correo. Muchas gracias!!

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.