Una demostración sobre este tipo de uso de NOMBRES puede verse en esta nota sobre Formato Condicional, y también en esta nota sobre valores #N/A.
Hoy veremos otro uso de como esta funcionalidad nos permite sobreponernos a algunas limitaciones de Excel.
En muchos de los modelos que desarrollo para mis clientes uso Validación de Datos. Esta funcionalidad permite controlar el tipo de datos permitidos.
Por ejemplo podemos establecer que sólo valores enteros entre 10 y 100 sean válidos para un rango determinado.
La variante que yo utilizo en muchos de mis modelos es "Lista"
Cuando usamos esta variante, en cada celda del rango se abre una lista de valores de la cual. Cualquier valor que no figure en la lista no será aceptado por Excel.
El problema con esta variante reside en que Excel aceptará solamente listas que sen encuentren en la misma hoja de la celda que queremos formar. Por ejemplo, no podemos usar una lista que se encuentre en Hoja2 para formar las celdas en Hoja1.
La solución es definir el rango de la lista como NOMBRE. Veamos esto con un ejemplo.
Supongamos que en Hoja2 tenemos una lista de precios:
Y en Hoja1 un formulario para calcular ofertas:
La idea es utilizar Validación de Datos para generar una lista de la cuál se elegirán los productos. El problema, como ya dijimos, es que Excel no permite utilizar rangos que se encuentren en otras hojas. Y nuestra lista de precios se encuentra en la Hoja2.
Para solucionar este problema hacemos lo siguiente:
1 – entramos en la hoja dos y seleccionamos el rango con los números de catálogo de los productos. En el cuadro nombres introducimos el nombre "productos".
2 – En la Hoja1 seleccionamos el rango A7:A12 y entramos en el menú de Validación de Datos. Elegimos la opción "lista".
3 – Pulsamos la ventanilla "origen" e inmediatamente el botón F3, lo que nos permitirá elegir uno de entre los nombre que hemos creado. Elegimos "producto" y pulsamos "aceptar".
4 – A partir de este momento cada vez que seleccionemos una celda en el rango A7:A12, veremos un pequeño triángulo. Si pulsamos este triángulo se abriera una lista que refleja los productos que se encuentran en la Hoja2.
En las celdas del rango B7:B12 utilizamos la fórmula
=SI(ESBLANCO(A7),"",BUSCARV(A7,lista_de_precios,2,0))
de esta manera, cuando elegimos una valor de la lista, automáticamente aparecerá el precio correspondiente.
Como ven, también hemos creado un NOMBRE que contiene el rango A2:B12 en la Hoja2, es decir el catálogo de productos con sus precios.
La función ESBLANCO nos sirve para evitar valores #N/A cuando no hay números de catálogo en la columna A de la Hoja1.
Categorías: Funciones&Formulas_, Manejo de Datos_, Varios_
Technorati Tags:
NOMBRES, Nombres en Excel
SENCILLO PERO BIEN EXPLICADO.
ResponderBorrarGRACIAS
Por favor necesito ayuda!!!
ResponderBorrarNo puedo hacer que aparesca el precio con la formula que esta explicada me marca #¿NOMBRE? que puedo hacer?
gracias por tu tiempo
Hola, el error #¿NOMBRE? se debe por lo general a que Excel no reconoce el nombre de la función o el nombre que contiene el rango.
ResponderBorrarDebes revisar cómo has escrito las fórmulas.
También puedes enviarme el archivo para ver cuál es el problema.
Estimado, he leido el blog y me parece muy bueno, pero tengo un pequeño inconveniente con una validación que no me doy cuenta como implementar y tal vez me puedas ayudar.
ResponderBorrarQuiero hacer un libro de banco con varias columnas por supuesto, pero lo que importa es que quisiera poder hacer validación de los nros de cheques que se ingresan, siempre y cuando el comprobante emitido sea un cheque, o sea, si por una casualidad coincide un nro. de cheque con un comprobante de otro tipo no me interesa si está repetido, pero sí si el comprobante es un cheque. Espero haber sido claro, si podes ayudarme un poquito, gracias.
Hola Alberto,
ResponderBorrarExcel no puede "saber" si un número de comprobante corresponde a un cheque o no, excepto que uses un rango único de números para los cheques o que en una columna auxiliar se encuentre la información. Por ejemplo en la columna C están los números de comprobantes y en la columna D el tipo de comprobante. En este último caso crearías un formato condicional con la función usando como criterio el valor de la fila en la columna D.
Hola Jorge, tengo un problema. A ver si se explicarlo y si es posible hacerlo.
ResponderBorrarTengo una lista desplegable en una celda con datos de una lista de otra hoja, esto lo hecho con validación de datos y con la función INDIRECTO. En otra celda he puesto una fórmula de manera que aparece un valor asociado al dato introducido en la lista desplegable. A su vez ese valor lo utilizo para calcular otro valor en otra celda. Este último valor calculado sólo puede estar entre un cierto rango de valores. Lo que quiero es que al seleccionar un dato de la lista desplegable me aparezca un mensaje de error (como con validación de datos) cuando el valor de la otra celda no esté dentro del rango.
El problema es que no puedo hacerlo por validación de datos porque ya utilizo esto para hacer la lista desplegable. Si lo hago con validación de datos en la celda donde calculo el valor, no salta el mensaje porque el valor está calculado mediante una fórmula, no introducido.
No se si me he explicado con claridad. Gracias de antemano y felicidades por el blog.
Así es, la limitación de validación de datos es que no responde cuando el valor es copiado o producto de una fórmula.
ResponderBorrarUna solución posible es imitar el proceso de validación de datos con un evento en la hoja. Eventos son uns especie de macro que se dispara cuando algo específico ocurre en la hoja a la cual el evento esta relacionado.
Hola.. mi duda es la siguiente..
ResponderBorrarComo hacer.. cuando para un valor de una lista hay a su ves varios valores posibles..
Es decir necesito una lista q cuando seleccione un valor, aparesca en la celda siguente otra lista relacionada a ese valor.
Tengo una lista con productos.. pero para cada producto hay distintos tipos..
Me parece que la técnica que propongo en esta nota te podría ayudar.
ResponderBorrarMuchas GRACIAS!!!! hace 4 dias que estoy renegando con un menu desplegable donde los datos estan en otra hoja!!!
ResponderBorrarESTIMADO AMIGO REALICE TODOS LOS PASOS INDICADOS Y DE MARAVILLA HASTA ESCRIBIR LA PRUEBA LóGICA =SI(ESBLANCO(A7),"",BUSCARV(A7,lista_de_precios,2,0)) ,SENCILLAMENTE NO ME FUNCIONA USO EXCEL 2002, DE IGUAL MANERA USO LA FUNSION BUSCARV PERO NO LOGRA DAR CON
ResponderBorrarMarko
ResponderBorrarmándame el archivo para que vea dónde está el problema.
Q TAL SIGUIENDO TUS EJEMPLOS LOGRE CREAR UNA LISTA, PERO SOLO ME DA HASTA 50 PRODUCTOS, PODRE CREARLA DE MAYOR CAPACIDAD???
ResponderBorrarLa cantidad de valores en la lista desplegable depende sólo del tamaño del rango que los contiene.
ResponderBorrarFíjate si tienes algún error en la definición.
Hola esto lo andaba buscando seras que puedes subir el archivo y poner el link si puedes grax
ResponderBorrarHola Carlos
ResponderBorrarsucede que no he guardado el archivo y estoy con poco tiempo disponible para rehacerlo. Tal vez dentro de unos días.
Hola Jorge. ¿Es posible que en la entrada de validacion de datos no tengas que escribir completo todos los dígitos (p.e. la referencia completa) sino solo una parte como en cuadro combinado, que conforme vas escribiendo aparecen las referencias que van comenzando por los dígitos escritos?. Gracias.
ResponderBorrarEl tema de los valores que se van "autocompletando" en listas desplegables es una de las consultas que más recibo. Prometo publicar una nota sobre el tema.
ResponderBorrarDeseo crear una lista desplegable en un formulario desplegable pero en el cuadro propiedades no me aparece ListFillRange...cómo debo usar correctamente este comando?
ResponderBorrarQué control estás usando? La propiedad ListFillRange pertenece a algunos controles ActiveX (cuadro de lista, cuadro combinado), como puedes ver en esta nota.
ResponderBorrarMe acabas de salvar la vida. Muchas gracias!
ResponderBorrarMe parecio excelente la nota y la pagina! FElicitaciones! TEngo para hacerte una pregunta respecto de este tema. Como hago que los rangos sean dinamicos? Seguindo tu ejemplo, quiero que al agregar un producto este me aparezca en el menu desplegable automaticamente. Como realizo esto? DEsde ya muchas GRacias
ResponderBorrarDiego
Hola
ResponderBorrarfijate en el ejemplo que pongo en esta nota sobre manejo de pequeñas bases de datos en excel.
Hola Jorge! realicé el ejercicio que propones para validar datos y quedo excelente el problema que tengo es que realice el ejercicio en un mismo libro (como es el ejemplo que pones) lo que yo quiero es en un libro tener los datos de origen y en otro diferente poner la lista desplegable correspondiente a esos datos es posible?
ResponderBorrarNo, no es posible usar nombres definidos en cuadernos remotos.
ResponderBorrarok gracias entonces cual sería la opción
ResponderBorrarHabría que analizar la situación, pero me parece que lompas racional es poner los datos de referencia en una hoja del cuaderno donde quieres usar la referencia. No siempre es razonable poner datos en cuadernos separados. Por ejemplo, puedes tener un cuaderno por año para llevar estadísticas de ventas. Pero lo m´´as eficiente es tener los datos en un solo cuaderno (y tal vez también en una sola hoja).
ResponderBorrarSe me ocurre que vinculando datos de otro libro en una hoja del libro actual tendríamos la base de datos y con ella atender la lista desplegable?
ResponderBorrarHola bueno me parece muy interesante y de gran utilidad este materia pero cuando llego a la formula me arroja un error y no he podido hacer q el precio salga el nombre del producto q coloque Q puedo hacer?
ResponderBorrarMandarme el archivo para que pueda ver dónde está el problema.
ResponderBorrarEstimado Jorge tengo problemas no puedo en la validación de datos poder aplicar la lista me dice que no puedo hacerlo desde otra hoja, que hay que hacer?
ResponderBorrarDe eso se trata justamente la nota. Tienes que definir el rango de la lista (que se encuentra en otra hoja) dentro de un nombre y usar el nombre en la opción Lista de Validación de Datos.
ResponderBorrarJorge, genial tu blog!! es de grandísima ayuda!
ResponderBorrarTengo una duda/problema al tener rellenadas 2 celdas con la validación de datos, como hago para que al modificar la primera de ellas, la segunda pierda el valor que tiene, es decir que me limpie el dato que hay, puesto que este no esta en su lista.
Daniel
ResponderBorraren la nota sobre listas desplegables dependientes puse en uno de los comentarios esto>
1 - en la pestaña Hoja1 haces clic con el botón derecho del mouse y abres Ver Código
2 - en el módulo de la hoja pones este código
Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A5"), Target).Address = Range("A5").Address Then
Range("B5").ClearContents
End If
End Sub
Lo que hace este evento es que cada vez que hay un cambio en el valor de A5 (el país) el contenido de la celda B5 es borrado.
Buenos días Jorge, desde que descubri tu blog tengo una visión muy distinta de Excel, de verdad muchisimas gracias por toda la ayuda que nos brindas a los novatos en el tema.
ResponderBorrarTengo unas dudas en las cuales espero poder recibir tu ayuda:
1. ¿Como puedo bloquear una celda para que el usuario no la modifique y que sólo se actualice al seleccionar un valor en otra celda con lista desplegable?
2. ¿Como puedo bloquear la modificación de una celda al llegar a un número en particular? Es decir si la celda tiene el valor de 100% que ya no se pueda modificar
3. ¿Es posible que 2 listas desplegables se actualicen al seleccionar un valor de otra lista desplegable? Es decir, tengo una lista desplegable que al seleccionar un valor se muestran las opciones en otra lista desplegable, lo que requiere es que se muestren 2 listas desplegables con diferentes valores pero asociados al valor inicial.
Espero que no esten confusas mis dudas y de verdad espero contar con tu ayuda.
De antemano muchisimas gracias por la ayuda brindada, y nuevamente muy agradecida por tu gran blog.
Saludos,
Mari.
Hola Mari,
ResponderBorrar1 - bloqueando la celda con Herramientas-Proteger Hoja y ligando la celda a la que contiene la lista desplegable (supuestamente tiene que haber una fórmulas que usa la celda de la lista desplegable como referencia)
2 - Con Validación de Datos
3- Si, fijate en mi blog en las notas sobre listas desplegables dependientes.
Gracias por los conceptos y me alegro que el blog te resulte útil.
Buenas noches Jorge, gracias por tus respuestas y en tan corto tiempo, pero creo que no quede clara y que no fui del todo clara describiendo mis dudas, te comento:
ResponderBorrar1. En este punto no entiendo a que te refieres con ligar la celda con la lista desplegable; y sí la celda tiene una formula que depende del valor seleccionado en la lista desplegable
2. Intente hacerlo con validación de datos pero no di con la formula
3. Ya he consultado la nota que me indicas,incluso con ella entendi como crear las listas que necesitaba, solo que lo que necesito es mostrar 2 listas al mismo tiempo dependiendo de un valor seleccionado, me crea conflicto porque el nombre de las las listas dependientes debe ser igual del valor que las referencia, pero 2 listas no pueden tener el mismo nombre.
Disculpa mi ignorancia en la materia pero como te indique soy nueva y ando aprendiendo un poco.
Nuevamente gracias por toda tu ayuda y por tu blob.
Mari.
Mari,
ResponderBorrarseguiremos la charla por mail privado. Fijate en el enlace Ayuda (en la parte superior del blog)
Buenas tardes,
ResponderBorrarhe hecho una función de visual basic con la que cálculo la letra del nif.
Ahora me gustaría que en lugar de tener que pasar-le a la función los datos a revisar, esta se ejecutara directamente con la validación de datos.
¿es posible hacer que la validación de datos utilice mi funcion? En caso afirmativo ¿como debería hacerlo?
Muchas gracias
Excel no permite usar UDF (funciones definidas por el usuario) en validación de datos. La alternativa es simular la validación usando el evento Worksheet_Change, por ejemplo.
ResponderBorrarHola Jorge
ResponderBorrarPor favor su ayuda, tengo la lista creada con la opcion "validacion de dato" y solo necesito saber como hacer que al escribir una o varias letras me ubique automaticamente en la referencia deseada dentro de la lista, esto para ahcer mas facil la busqueda de valores dentro de la lista.
Agradecere mucho su ayuda.
Este comentario ha sido eliminado por el autor.
ResponderBorrarFiajte en esta nota.
ResponderBorrarHola Jorge,
ResponderBorrarAntes de nada darte la enhorabuena por el blog que lo he descubierto hoy mismo y llevo todo el día cacharreando con Excel gracias a ti.
Tengo una duda que no se muy bien como hacer. Estoy intentando hacer presupuestos dinámicos, que cojan los precios de una matriz.
Esta matriz tiene por un lado el tipo de producto y por otro la cantidad, y el precio son los valores que dependen de ambos datos.
En el presupuesto hay dos columnas, CANTIDAD (sin validación, se podría poner cualquier dato) y PRODUCTO (Elegido de una lista). En base a estos dos valores quiero que aparezca el precio. He conseguido con la función DESREF y COINCIDIR que aparezca el precio en función del producto elegido, pero no se como condicionarlo a la cantidad.
Espero que me puedas ayudar.
Un cordial saludo,
Federico
Siguiendo tu descripción (tipo de producto en la primer columna de la matriz y cantidad en la primer fila, es así?) tendrías que usar INDICE combinada con COINCIDIR. La función COINCIDIR la usarías dos veces, una para encontrar la fila (tipo de producto) y la otra para la columna (cantidad).
ResponderBorrarJorge estoy trabajando con excel 2010 y revisando vi que el nombre de la función BUSCARV no figura en la lista sino que excel cambio el nombre por el de CONSULTAV, con la función en la celda de precio me salía así #¿NOMBRE? ahora que lo cambié a CONSULTAV me sale #N/A. Igual no logro que me salga el precio. cual es el rango del nombre lista_de_precios??? gracias
ResponderBorrarEn la primera entrega de Excel 2010 Microsoft "rebautizó" varias funciones. Entre ellas BUSCARV que se convirtió en CONSULTAV. Más adelante Microsoft sacó un Service Pack que restauró los viejos nombres.
ResponderBorrarEl nombre se refiere al rango que contiene la lista de precios (en el ejemplo de la nota A2:B14).
Hola Jorge,
ResponderBorrarMe gustaría saber si es posible hacer que en una columna solo se escriban ciertas cosas, por ejemplo en la columna c5 hasta c400
solo se puedan ingresar: pala, perro, taza... etc, y si por alguna razón alguien escribe otra cosa no le permita escribirlo.
No sé si hay que tener alguna base de datos donde salgan las palabras que solo se deben escribir en las celdas c5:c400.
Atte,
Sebastian.
Sebastián, exactamente para eso existe la validación de datos (el tema de la nota). Tenés que usar Validación de Datos--Lista y apuntar al rango que contiene los valores permitidos (o usar un nombre definido que se refiera a ese rango).
ResponderBorrarhola quisiera saber si puedo ponerle nombre a un rango de celdas pero poner como nombre numeros
ResponderBorrarhola quisiera saber si puedo usar números al momento de asignarle nombre a un rango de celdas,
ResponderBorrartengo una lista dependiente de otra pero la primera es un listado de números, gracias..
Los nombres definidos tiene que empezar con una letra, un especio, un underscore (_) o una barra invertida (\).
ResponderBorrarEn tu caso podría ser _12345 por ejemplo.
Luego, para poder usarlo en al lista dependiente tendrás que transformarlo nuevamente en número usando la función SUSTITUIR combinándola luego con INDIRECTO.
Puedes ver un ejemplo en esta nota.