Hojas de cálculo en Excel - página principal

Validación con datos en otra hoja

Seguramente muchos de vosotros os habéis encontrado con una limitación, a la hora de crear una lista de datos para validar lo que un usuario introduce en una celda. Me refiero concretamente al origen de los datos para generar una lista de validación. En concreto, excel no nos permite crear una lista de validación, si los datos figuran en otra hoja del libro.

Me estoy refiriendo concretamente a esto que figura en la imagen, enmarcado en rojo. Excel no nos permite seleccionar otra hoja, si es que en ella tenemos la lista de datos:


En realidad existe un método para saltarse esa limitación, de una forma bastante sencilla.

Imaginemos que tenemos una lista de nombres de personas, en la Hoja2, y que deseamos colocar una lista desplegable en la Hoja1, con esos nombres de la Hoja2. Si lo hacemos de la forma tradicional, no nos funcionará, y excel nos mostrará un bonito mensaje de advertencia como este:


La solución es tan sencilla, que os sorprenderá. Tan solo tendremos que seleccionar los nombres que tenemos en la Hoja2, y a continuación introduciremos un nombre en el apartado llamado Cuadro de nombres. Es decir, le vamos a poner un nombre a ese rango de datos, donde tenemos los nombres y apellidos que utilizaremos en nuestra lista de validación. Concretamente para introducir un nombre para ese rango de datos, deberemos hacerlo arriba a la izquierda, donde nos sale la coordenada de situación de la celda donde estamos (esta es la forma más sencilla de darle un nombre a un rango de datos). Si os fijáis en la imagen siguiente, hay que introducir un nombre, en el espacio indicado en el área de color rojo (en este supuesto, estamos situados en la celda C10, por eso sale indicada así en la imagen):


Para introducir el nombre, tal y como comentaba antes, deberemos seleccionar el rango de datos que tenemos en la Hoja2, e introducir un nombre para al mismo, tal y como se muestra en la siguiente imagen (yo le he dado el nombre de personas, aunque le puedes poner cualquier otro que consideres oportuno):


Ahora solo nos quedará volver a la Hoja1, y en la celda que nos interese, crear la lista de validación, llamando al origen o a la fuente de datos por el nombre que le hemos dado al rango de celdas, precedido del signo igual, tal y como figura en la siguiente imagen:


Una vez hayamos introducido el nombre del rango de datos donde tenemos los nombres y apellidos, ya tendremos todo listo, y podremos seleccionar de la lista, alguna de las personas que tenemos en la Hoja2, tal y como figura en la siguiente imagen:


De esta forma, tendremos solucionado el tema de disponer de una lista con datos en una hoja, y la validación de datos en otra distinta, sin necesidad de tener que ocultad filas ni columnas, si es que hemos utilizado la misma hoja para todo.

Finalmente comentaros que para nombrar un rango de datos, tenemos otras opciones aparte de esa que os he comentado, y que es la más rápida y sencilla. Por ejemplo podemos definir un nombre para un rango de datos, desde el menú Insertar, seleccionando Nombre, y a continuación Definir….

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo, para que podáis ver en funcionamiento como se crean listas de validación, con los datos en otra hoja distinta.



31 comentarios:

Anónimo dijo...

gr0x

Anónimo dijo...

Excelente tu explicación yo ya tenia mucho tiempo tratando de hacer eso y no podía gracias de verdad me ayudo a lograrlo ya lo pude hacer y era algo tan sencillo solo me faltaba el signo igual (=) lo coloque y me funciono tal como explicas, Mil Gracias. Saludos

El pilt®afilla - www.3piesalgato.com dijo...

Muchas gracias por vuestros comentarios. Me alegro que os haya sido útil.

Saludos.

Marta Gomez Rozas dijo...

muchas gracias...estoy haciendo un curso y esto me deja como reina

El pilt®afilla - www.3piesalgato.com dijo...

Me alegro que te sirva todo esto. No sé si sacarás buena nota, pero que conste que yo lo he intentado :-)

Un saludo

arquitectos e ingenieros dijo...

SOY NORMA DEL CHACO,ARGENTINA
GENIALLL!MIL GRACIAS, ESTOY HACIENDO UN CURSO ON LINE Y SIN ESTE DATO HUBIERA PERDIDO EL CURSO

Medianoche Tropical dijo...

Saludos, por favor dime si se puede generar una lista de validación usando en lugar de hoja2 del mismo libro, una hoja en otro libro de excel, realmente no he logrado hacerlo. me sucede que debo referenciar en lista datos que están en otro libro
desde ya gracias

El pilt®afilla - www.3piesalgato.com dijo...

Batsig, me alegro mucho que te haya servido.

MARCOS GRANADO dijo...

VERY VERY VERY MUCH TANKS YOU

NO SE HABLAR INLGES PERO NO SABIA COMO AGRADERTELO

EN SINTESIS

GRACIAS

Unknown dijo...

gracias hombre solucioné mi problema!!

Camaleon dijo...

De verdad que es de gran ayuda lo que colocas aqui, simpre habia querido hacer eso, ya que en la generacion de archivos planos va de maravilla. de verdad gracias.

Saludos desde Colombia (º_º)

Unknown dijo...

Soso grande pa!! mil gracias era lo que buscaba!

Saludos desde Honduras,C.A

Anónimo dijo...

queria saber si hay alguna manera de que esa lista que generaste, entre dos hojas de excel, muestre los datos en orden alfavético.
Muchas gracias

Anónimo dijo...

Hola,es práctica la soluciòn que planteas pero sabés si se puede hacer una lista cn datos de otra hoja, y que al desplegarla muestre los datos en orden alfavético y omitiendo blancos??

Gracias

El pilt®afilla - www.3piesalgato.com dijo...

Eso puedes hacerlo, a partir de la información contenida en estos 2 artículos:

Ejemplo para rellenar un combo con datos únicos: Sacándoles provecho a los combobox.

Ejemplo para validar listas dependientes, y ejecutar un macro al cambiar un dato del desplegable: Validación de listas dependientes

Tan solo tienes que crear un macro que ordene los datos, y llamarlo desde el desplegable como en ese segundo ejemplo.

Saludos.

Dexter dijo...

Muchas gracias.
Me sirvió para facilitar el ingreso de datos en una Planilla de Excel.

El pilt®afilla - www.3piesalgato.com dijo...

Me alegro que te haya sido de utilidad.

Anónimo dijo...

EXCELENTE VIEJO ME ESTABA SACANDO UN OJO CON ESTA PENDEJADA, NI EL TUTORIAL DE EXCEL LO EXPLICA TAN FACIL.....

Anónimo dijo...

Muchas gracias por tu aportacion, no sabes como me hacia falta algo parecido, y espero algun dia podr aportar algo, que lo veo dificil ya que eres todo un maestro, saludos.

Jiugo

Unknown dijo...

Muchas Gracias

Anónimo dijo...

Excelente y fácil de hacer....

muchas gracias

Anónimo dijo...

haha muchas gracias amigo hasta unos años despues tu aporte sigue ayudando a mas gente haha y si me resultaba muy mportante tomar listas de validacion en otras hojas e excel gracias y salu2

Anónimo dijo...

Tremendo aporte. Actualmente el excel del office 2010 lo puede hacer, sin embargo, los que tenemos office 2007 o inferiores nos quedamos con los crespos hechos al querer usar esta herramienta tan importante. Gracias por compartir tu conocimiento.

Daniel Escudero dijo...

Hola Javier, me asalta una duda, ¿es posible que al hacer click en un nombrede la lista (guiandome por el ejemplo que diste) este me envie a una hoja con sus datos personales como por ejemplo direccion de esa persona, su edad, etc.?

Saludos!

El pilt®afilla - www.3piesalgato.com dijo...

Hay muchas formas de hacer eso.

Imagina por ejemplo que tenemos un libro con 4 hojas que se llaman: Hoja1, Hoja2, Hoja3, y Hoja4.

Imagina que en la Hoja1, tenemos un desplegable en la celda D5, que tiene una lista con los nombres de esas 4 hojas. Si queremos desplazarnos a una hoja en concreto (suponiendo que cada hoja sea en tu caso, una persona, alumno, cliente, o lo que se trate), copiaremos esto en la Hoja1 que es donde tenemos el desplegable (no en un módulo):

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells = Range("D5") Then
Sheets(ActiveCell.Value).Select
End If
End Sub

Como eso no es exactamente lo que creo que quieres, y suponiendo que tengas todas esas personas en una única hoja (la Hoja2), con sus datos, podemos hacer algo más sencillo, solo utilizando fórmulas.

Imagina que los datos de esas personas empiezan en A1, hasta A10. Imagina que en la columna B tienes la edad, y en la columna C su dirección. Para obtener esos datos de la persona elegida en el desplegable, no necesitas desplazarte hasta la Hoja2 que es donde tenemos los datos. Nos bastará poner esto en la Hoja1 (que es donde tenemos el desplegable, y concretamente en la celda D5):

Para saber la edad de la persona elegida en el desplegable, pomdremos esto en D6 por ejemplo:

=BUSCARV(D5;Hoja2!A1:C10;2;0)

Y esto en D7 para saber su dirección:

=BUSCARV(D5;Hoja2!A1:C10;3;0)

Saludos.

Daniel Escudero dijo...

Hola Javier, muchas gracias por tu ayuda me ha servido harto, pero.. necesito tu ayuda por ultima vez jaja.

Lo que pasa es que yo ya tengo un excel con los nombres de las personas, con variadas especificaciones (pintados por colores ependiendo de la carpeta donde esten ubicados, números de envios, códigos propios de cada persona, etc), todo listado en columnas, por lo que necesito que (mas que nada por orden y fácil entendimiento) se vean todos los nombres en una columna (así como los tengo, con todas esas caracteristicas), que cada uno de ellos tenga una lista desplegable con sus respectivos datos (edad, direccion, banco, etc.) y que al seleccionar cada uno de esos datos me aparezca en una columna designada para cada uno de estos datos (columna edad, columna direccion, etc.) sus.. datos o caracteristicas (en columna edad, me paezca 24 por ejemplo, en banco santander, etc.).

Eso, me explicaste que se hacia con buscarv, pero la diferencia es que no se como hacerlo para adaptarlo a mi excel ya prediseñado.

Saludos!, y me disculpo por pedir tanto, pero es lo ultimo jaja y te lo agrdezco mucho, que estes muy bien, bye!

El pilt®afilla - www.3piesalgato.com dijo...

Deberás adaptar tu plantilla de Excel, para colocar los datos que quieres obtener, al cambiar cada vez el valor de la lista desplegable, allí donde quieras.

Fíjate en este ejemplo (mira la hoja 2, que es donde están los datos) que te dejo para descargar aquí (al final de esa página verás un botón para descargar el fichero): http://www.2shared.com/document/AS5K09YC/ejemplo.html

Saludos.

Daniel Escudero dijo...

Hola Javier, te has pasado por ayudarme tanto, pero me queda lo ultimo ultimo jaja (ahora si), necesito nuevamente de tu ayuda, respecto a un archivo excel que te envio (archivo en el link, es el mismo ejemplo que me enviaste pero cambiado un poco), ¿como se puede evitar agregar un numero a cada nombre para que la tabla no me muestre lo mismo siempre? y otra pegunta, la mas importante, en la hoja 2 hay una lista referida a la hoja 3, ¿es posible que en la hoja 1, al seleccionar a una persona de la lista, en la tabla de la derecha donde apaecen los datos, me aparezca la lista de la hoja 2 como dato?

http://www.2shared.com/document/uixVeL1q/ejemplo.html

Saludos!

PD: si no me respondes lo comprenderé jaja.

El pilt®afilla - www.3piesalgato.com dijo...

El archivo no se puede descargar desde ese enlace. Te sugiero que postees las dudas en un foro especializado, y más concretamente en el subforo de ofimática, en forosdelweb.com/f90 para no convertir el blog en un chat :-)

Muchas gracias Daniel.

Anónimo dijo...

Gracias, lo había hecho con office 2010, y al pasar al office 2003 me quitó la validación, fue muy útil ya que pude redireccionar la validacion a otra hoja, como tú lo explicas, "colocando un nombre a toda la lista", mis 10 sobre 10. Fti

Anónimo dijo...

Hola, como estas?. Muy buena la explicación, pero necesito llamar los datos de la lista desde otro archivo. Como hago?. Saludos,