Aprovecharé la solicitud de ayuda un lector sobre un problema, para desarrollar un ejemplo de validación de datos personalizada que se le planteaba, por otro lado muy típico en España:
Usaremos la opción de 'Validación de datos personalizada' para dar solución a este cuestión. Desde el menú Datos > Validación seleccionamos Permitir 'Personalizada', con lo que nos desplegará la opción de 'Fórmula', en la cual escribiremos la prueba lógica que se ha de cumplir para que nos permita introducir un valor en la celda C2, es decir, escribiremos
=C2=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKEF";1+RESIDUO(B2;23);1)
Con lo que tendríamos nuestra celda adaptado a nuestro propósito, que es impedir la incongruencia entre número de DNI y su letra.
Ya sólo nos quedaría configurar las pestañas de 'Mensaje de entrada' y 'Mensaje de error' con algún texto los suficientemente descriptivo.
Aprovechando el tema tratado incluiré otra forma de calcular la letra de los D.N.I.
=ELEGIR(RESIDUO(B2;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N";"J";"Z";"S";"Q";"V";"H";"L";"C";"K";"N")
| ...Tengo una hoja Formulario.xls, en la que los usuarios me han de rellenar unos datos personales. Por ejmplo: -Celda B2 es el DNI, -Celda C2 es la letra de DNI. De forma oculta en otra celda L7 yo calculo la letra con la fórmula, ya de todos conocida de "=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKEF";1+RESIDUO(B2;23);1) " Como hago para validar C2, para que si meten una letra que no es la correcta les salga un mensaje de error... |
Usaremos la opción de 'Validación de datos personalizada' para dar solución a este cuestión. Desde el menú Datos > Validación seleccionamos Permitir 'Personalizada', con lo que nos desplegará la opción de 'Fórmula', en la cual escribiremos la prueba lógica que se ha de cumplir para que nos permita introducir un valor en la celda C2, es decir, escribiremos
=C2=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKEF";1+RESIDUO(B2;23);1)
Con lo que tendríamos nuestra celda adaptado a nuestro propósito, que es impedir la incongruencia entre número de DNI y su letra.
Ya sólo nos quedaría configurar las pestañas de 'Mensaje de entrada' y 'Mensaje de error' con algún texto los suficientemente descriptivo.
Aprovechando el tema tratado incluiré otra forma de calcular la letra de los D.N.I.
=ELEGIR(RESIDUO(B2;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N";"J";"Z";"S";"Q";"V";"H";"L";"C";"K";"N")
buen aporte compañero se lo agradesco
ResponderEliminary funciones mas faciles
ResponderEliminarHola de nuevo.. estoy intentando hacer una validación...
ResponderEliminaren la que en ciertas columnas me acepte solo 5 números que no son consecutivos... como lo son 1,2,3,98,99 como podría hacer una validación personalizada con esos números??
Gracias
Y felicidades por tu foro!
Hola Lune,
Eliminarbueno, no hace falta una validación personalizada, sería suficiente con una Validación tipo Lista, y esas lista componerla con los valores que indicas (separados por ';' punto y coma ).
Slds
Tengo una duda con respecto a la asignacion de un valor numerico a una letra
ResponderEliminarA=10, B=12,C=13
Si se ingresa A en A4 toma el valor de 10
=SI(A4="A";10)
la idea es seguir agregando funciones en la misma celda
=SI(A4="A";10),=SI(A4="B";12),=SI(A4="C";13), ETC.
NO se si es posible.
Gracias
Hola,
EliminarNo sé si te he entendido bien; pero posible es, pero con limitaciones.. ya que sólo es posible anidar hasta 64 funciones, a parte del trabajo que conlleva.
La mejor opción sería contruir una tabla auxiliar con las correspondencias, por ejemplo, columna C:D, en C las letras y en D los valores; así entonces en A3 escribes la letra y en B3 al función
=BUSCARV(A3;C:D;2;0)
Si lo que pretendes es que en una celda concreta escribes una letra, y quieres visualizar un valor concreto, hablaríamos de un tema de formato personalizado, pero el valor real de la celda (independientemente de qué estemos viendo) sería el introducido.
Slds
Gracias por la ayuda, probare la solucion que mencionas.
EliminarHola buenas tardes, ante todo felicitarte por esta pagina.Esto de la informática no es mi fuerte y no sé si es aqui donde debo plantear ni duda pero allá va:
ResponderEliminarTengo en una hoja una base de datos donde la coluna A son fechas y B (nombres de persona)y C incidencias de esa persona.
Debo encontrar la manera de pasar los datos de B y C a un calendario mensual hecho en otra hoja con excel 2003. La formula BuscarV (para comparar las fechas de A y las del calendario)no me sirve porque en un mismo dia tengo más de una inciedencia y sólo me devuelve un unico valor. ¿ccomo puedo hacelo?
Hola, muchas gracias por el apoyo.
EliminarHabría que conocer la distribución de datos en el destino (calendario), pero en principio la combinación de la función INDICE con COINCIDIR te serviría para identificar únicamente una fecha y una incidencia.
Busca en el blog por que hay varias entradas explicando ejemplos de estas funciones.
De todas formas si no das con la solución envíame el fichero a
excelforo@gmail.com
y le echo un vistazo
Slds cordiales
como puedo hacer para validar una celda que solo me permita ingresar numero comprendidos entre 0 y 9, y no letras, con rango de 8 dígitos. como por ejemplo en una celda que contenga valores del DNI puede ser 00230530
ResponderEliminarHola Eder, que tal?
EliminarEspero te encuentres bien.
Suponiendo que sea la celda B2 la que quieras validar, Desde validación de datos personalizada incluye la siguiente fórmula:
=Y(ESNUMERO($B$2);LARGO($B$2)=8)
sólo permitirá números, no letras, y de ocho dígitos.
Espero te sriva.
Un cordial saludo
esta bien funciona, pero para el caso que el numero empezara con "0" como haria tal es el caso de un numero de dni que empieza con 00
EliminarHola!!
EliminarEspero sigas bien...
Prueba con
=Y(ESNUMERO($B$2);LARGO($B$2)>=6;LARGO($B$2)<=8)
Un cordial saludo
Hola ismael,a la verdad ya me deja registrar el campo cuando escribo al principio "0" pero al momento que se muestra en la celda desaparecen los ceros,¿cómo puedo hacer para que muestre elregistro con los ceros al principio? saludos y perdona la molestia
EliminarHola,
Eliminarpuedes darle a la celda un formato personalizado tipo 00000000
con eso será suficiente.
Slds
Buenos días hrno gracias por los datos son excelentes.
ResponderEliminarGracias a ti!
Eliminarun cordial saludo
Hola te escribo desde Chile, he aprendido mucho con tus datos, por eso Gracias. Mi consulta es que tengo que validar una celda con un rango que permita ingresar números >= 1 y <=7 o una letra P. Se refiere a un rango de calificaciones que puede obtener un alumno y si le faltaesa calificación debe ir una P de pendiente. Te agradeceré una ayuda.
ResponderEliminarHola Sergio,
Eliminarmuchas gracias!!
Tendrás que aplicar una Validación de datos personalizada, con la fórmula:
=O(Y(celda>=1;celda<=7);celda="P")
Slds
Hola, quisiera ver si me pudieses ayudar con un problema, veras, quisiera hacer que en una celda solo deje entrar ciertos valores, dichos valores estan en un rango y ademas dependen del resultado de una lista desplegable, suponiendo que la lista desplegable esta en la celda A2, necesito que si A2= "x", entonces solo acepte valores entre 10,000 y 20,000, si A2= "y", entonces solo acepte valores entre 20000 y 15000, y si A2= "z" entonces solo acepte valores entre 20,000 y 35,000, al hacer una validación personalizada introduje la siguiente formula pero no me entendió, me podrías ayudar?
ResponderEliminarO(Y(A2="x";celda>10000;celda<20000);Y(A2="y";celda>20000;celda<35000);Y(A2="z";celda>35000;celda<50000))
Gracias :)
Buenas Tardes Amigo que tal, quisiera saber como hago para implementar una validación personalizada donde me muestre solamente tres letras (J,G,V) con un guion y luego 8 numeros aleatorios, ejemplo (J-12345678). No permita otras letras y una longitud de 8 números.
ResponderEliminarMuchas Gracias
Hola Juan Carlos,
Eliminarpodrías incluir una Validación de datos personalizada con esta fórmula:
=Y(O(IZQUIERDA(D6;2)="J-";IZQUIERDA(D6;2)="G-";IZQUIERDA(D6;2)="V-");ESNUMERO(VALOR(DERECHA(D6;8)));LARGO(D6)=10)
suponiendo D6 como la celda con la validación
Saludos
Hola Ismael Romero que tal, excel me muestra un error por pantalla que dice error en la fórmula, con tres descripciones, puedo resumir tiene que ver con los simbolos =, - o comillas simples " ".
EliminarHola,
Eliminares una fórmula verificada... prueba a escribirla en lugar de copiar y pegarla, o quizá tu configuración tenga como separador de argumentos la coma en vez de punto y coma...
??
Slds
Este comentario ha sido eliminado por el autor.
EliminarBuenas Tardes Ismael, la configuración regional, tenia lo argumentos invertidos, funciona de maravilla.
ResponderEliminarIsmael por casualidad tiene un correo, ya que tengo varias dudas por solucionar.
Muchas Gracias por tus excelentes aportes, y respuesta inmediata.
Hola Juan Carlos,
Eliminarmi correo:
excelforo@gmail.com
lee, por favor, antes de nada las Normas de Uso del blog.
Un saludo
Este comentario ha sido eliminado por el autor.
ResponderEliminarBunos 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.
ResponderEliminarExplicació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
Hola,
Eliminarno está muy claro qué necesitas... pero en tu fórmula hay varios errores de sintáxis:
=SI(J2=10;(A2+B2)*10/100+E2;SI(J2=29;(C2>D2);D2/2+C2;C2/2+D2)+E2)
te lo he marcado en negrita...:
SI(J=10;...
debería ser algo así (adivino)
SI(J2=10;...
y en
SI(J=29(C2>D2)
algo así:
SI(J2=29;(C2>D2)
El resto del planteamiento no lo comprendo.
En todo caso, en otro comentario ya te decía que no comprendía y que si podías exponer el caso de otra forma ;-)
Slds
Hola Ismael que tal; aquí te envió la explicación màs detallada.
Eliminar* Tenemos dos valores J2=15% y J2=20%; se debe validar en la celda J2 exclusivamente los dos porcentaje con distinta formula, si existe un monto distinto a los porcentaje mostrar un mensaje de error donde muestre que solamente son 15% y 12%;
• Si en la celda J2 aparece (15%):
(A2+B2)*10/100+E2
• Si en la celda J2 aparece (20%):
(A2>B2);B2/2+A2;A2/2+B2)+E2)
Nota: Tomando en cuenta que A2 y B2 puede ser cualquier monto definido por el usuario.
Hola,
Eliminarno son compatibles las fórmulas en celdas validadas.. es decir, éstas no se ven afectadas por la regla de validación.
Una celda validada solo afecta cuando vamos a introducir algún dato....
Buenas Tardes Ismael Romero que tal, que gusto volver saber de ti, la presente es para resolver un problema que tiene que ver con el formato de fecha corta Excel 2010: Tengo dos hojas, la primera se encarga de ingresar en una celda una fecha dd/mm/aaaa, al guardar con una macro en la otra hoja en una celda la fecha aparece mm/dd/aaaa, es decir; me intercambia el día por el mes. Verifique la fecha en configuración regional, el formato de fecha en ambas celdas (Hoja1, Hoja2), y todo está bien.
ResponderEliminarMuy amable por tu colaboración y respaldo.
Hola Juan Carlos,
Eliminartrabajar con Fechas es siempre complicado, y más cuando las traemos desde VBA...
Una solución fácil suele consistir en anidar tu variable en VBA con la función CDate(variable), habitualmente funciona bien y te devuelve la Fecha con el formato estándar dd/mm/aaaa
Si no funcionara, además, aplicaríamos la función
Format(Cdate(variable),"dd/mm/yyyy")
en tu código de VB
Saludos
Buenos Días Ismael que tal, muchas gracias por tu respuesta, el código VBA que tengo en la macro guardar es este, donde C3 es la celda de fecha.
ResponderEliminarAqui dejo como coloque el codigo el macro guardar, coloque la fecha dentro el parentesis, ya que no permite colocar la de manera de formato (dd/mm/aaaa)
Texto = CDate("01/01/1900")
Range("C3") = Texto
Range("C3") = CDate("01/01/1900")
...pero todavia me sigue dando problema
Muchas Gracias por tu valiosa acesoria.
Saludo!!!
??
Eliminares suficiente la forma:
Range("C3").value = CDate("01/01/1900")
En caso contrario aplica la función format que te indiqué
Range("C3").value=Format(Cdate("01/01/1900"),"dd/mm/yyyy")
Slds
Ismale coloque este codigo en un nuevo modulo
ResponderEliminarSub Cambiarfecha()
'para col B
Range("B3").Select
While ActiveCell.Value <> ""
'ajustar el formato deseado
Selection.NumberFormat = "dd/mm/yyyy;@"
ActiveCell.Offset(1, 0).Select
Wend
End Sub
... y todavia me presenta el problema.
Estás seguro que la configuración regional que tienes de fecha dd/mm/aaaa?
EliminarHola Ismanel que tal, si la configuración regional esta muy bien configurada, ya lo resolvi, era que tenia una macro para convertir de minuscula a mayuscula, y estaba tomando el rango de fecha, se lo quite y listo.
ResponderEliminarMuchas Gracias por tu apoyo y amabilidad
Saludo!!!!
;-)
EliminarSlds
Buenos Dìas Ismanel que tal, como esta todo, tengo un combo donde aperecen dos datos distintos, al seleccionar cada datos, tiene que colocar en monto en una celda, que contiene una formula.
ResponderEliminarEjemplo: Si en la Celda A1 se coloca el nro 40, la formula =(A1="40");2+3*0.04), si en la celda A1 se coloca el nro 50, la formula =((A1="50");2+3*0.05), me presenta error de sintasis.
Necesito tu valiosa colaboración.
Muchas Gracias
Hola Juan Carlos,
Eliminarentiendo que al validar el valor seleccionado del Combo en otra celda se añade la fórmula indicada...
La idea sería incluir como variable el valor del combobox
celda.formula="=(A1=" & combobox1.value & ");2+3*" & (combobox1.value/1000) & ")"
Espero te de la idea.
Saludos
hola, tengo un problema con una validación, quiero que si una celda me dice "entrada", me deje ingresar en otra celda solo números positivos y si dice "salida"; solo me deje ingresar números negativos, he probado con la funcion si usando >0 o <0 pero a la hora de ingresar el valr siempre me salta como error.. gracias
ResponderEliminarHola,
Eliminarsupongamos en A1 pone 'Entrada' o 'Salida' y en la celda B1 debemos añadir un número positivos negativo según tus indicaciones.
Entonces seleccionas B1 y añades una validación de datos personalizada con la fórmula:
=O(Y(A1="Entrada";B1>=0);Y(A1="Salida";B1<0))
Saludos
Muchas Gracias
ResponderEliminarHOLA!! TENGO UN PROBLEMA, DE PRONTO QUIERO OCULTAR COLUMNAS EN EXCEL Y NO ME LO PERMITE, NO PUEDO SELECCIONAR LA OPCION CON CLIK DERECHO SOBRE LA COLUMNA, ME APARECE INHABILITADA, QUE PUEDE SER??
ResponderEliminarHola que tal!
ResponderEliminarQuizá la hoja esté protegida, sería la primera posibilidad
Ve a la ficha Revisar > grupo Cambios > botón Proteger/Desproteger Hoja
Un saludo
Hola Ismael que tal, tengo una macro que vengo utilizando desde hace 1 año, contiene fecha y texto, ahora existe un detalle en este momento, cuando quiero guardar una fecha que contenga datos de dìas del 1 al 12, me lo esta cambiando por el mes.
ResponderEliminarEjemplo: 07-09-2016 al guardar con la macro me lo esta registrando con 09-07-2016, y asì me sucede con todas las fechas que contengo los dìas del 1 al 12.
Soluciòn a esta problematica...
Muchas Gracias
Hola Juan Carlos,
Eliminarentiendo que la fecha 'falla' cuando la trasladas a la hoja de cálculo...
Intenta aplicar a la variable algún formato:
range("A1").value=format(Fecha,"dd/mm/yyyy")
suele funcionar...
Saludos
Hola disculpa como hago en validacion de datos personalizada para obtener una función que me permita comenzar con Hz y continuar con cualquier número o letra
ResponderEliminarHola,
Eliminarañade =IZQUIERDA(CELDA;2)="Hz"
Saludos!
Buenos Dìas Ismael que tal, aplique la soluciòn que me planteas sigues presentadno la falla
ResponderEliminarLo más sorprendente es que la macro se viene aplicando desde hace un año, y "No existia problema alguno". Es solamente con la ubicaciòn de esa dos celdas de fechas, mientras que las otras celdas de fechas funcionan de maravilas.
Nota: La Macro nunca ha sido modificada en ningùn momento.
Muchas Gracias
Hola,
Eliminarhabría que ver el fichero, quizá el problema esté en esas dos fechas, a lo mejor esas celdas tienen algo raro (fechas que no son tales, sean texto??).
Si falla solo para algunos valores y en otros funciona correctamente, obviamente el fallo está solo en esas celdas.
Revisa que no tengan nada especial...
Saludos
Este comentario ha sido eliminado por el autor.
ResponderEliminarBuenos Días Ismael que tal, como haz estado estoy investigando el proceso de combinar correspondencia de Excel 2010 a Word 2010 con el código QR,
ResponderEliminarEjemplo: Que tome del correlativo (01-01-01-001) hasta N
cantidad.
He buscado por youtube y en varios foro, no ha cubierto mis expectativas. Debido ha que falta que expliquen el paso de la combinación que todavia no lo he encontrado.
Si por casualidad conoces un sitio donde encontrarlo por favor enviame el link.
Muchas Gracias
Saludos