Como crear lista disponible en excel que dependa de los valores de una tabla.
Tengo en una hoja de excel una tabla con 5 valores, y en otra hoja, otra tabla en la cual en una de sus celdas tengo una validación de datos con los valores de la tabla, yo lo hice eligiendo el rango de celdas pero en la tabla de donde depende dicha lista puede verse ampliada y yo quisiera saber como lo puedo automatizar que si me pongo en el ultimo registro de la tabla y añado un nuevo registro automáticamente se me actualice la lista despegable de la que depende.
1 respuesta
.17/08/16
Buenas, Kike
Si te hubiera entendido correctamente, lo que yo suelo hacer, es definir rangos dinámicos. Esto es, rangos que se auto-definen en función de los elementos que contengan.
Por ejemplo, en tu caso, suponiendo que los datos que van en rango llamado "Ubicac" estuviera en la columna A de una hoja Tablas,
Al definirlo deberías ingresa en la casilla inferior (donde vá la dirección del rango), de la siguiente manera:
=DESREF('Tablas'!$a$2;;;CONTARA('Tablas'!$a$2:$a$500))
Una vez definido, de esta manera, el rango "Ubicac" comenzará en la celda B2 y tendrá tantas filas de alto como elementos cuente en el rango que lo contiene. Cuida que no haya líneas en blanco entre los rubros.
Luego, en la Lista de Validación al usar este nombre de rango, ceñido a los elementos que contenga, no deberás ver líneas en blanco.
Por otra parte, si agregas elementos a aquella tabla, no necesitas redefinir el rango, pues lo hará la fórmula conque lo definiste.
Espero que sea lo que buscas. Si no, pregúntame de nuevo.
Saludos
Fernando
.
Muchas Gracias por contestar, para aclararme mejor vamos a suponer que mi tabla es la de la imagen de abajo, donde debería poner esa fórmula? me lo podrías explicar en base a este ejemplo?
Como nombrar el rango en que celda y donde colocar la formula:
=DESREF('Tablas'!$a$2;;;CONTARA('Tablas'!$a$2:$a$500))
Un saludo
Muchas gracias por contestar ante todo, me podrías ayudar un poco mas? Podrías explicarme lo del rango como he de hacerlo (en mi caso cree una tabla que se llamo Tabla2 y fui metiendo registros de prueba) y donde poner la formula =DESREF('Tablas'!$a$2;;;CONTARA('Tablas'!$a$2:$a$500)), con un ejemplo sobre la foto de abajo?
Es decir en que celda he de poner la fórmula y dar nombre al rango etc, gracias de nuevo por adelantado.
Disculpa, había preparado la siguiente imagen y olvidé incluirla en la respuesta:
En realidad, no ingresas la fórmula en una celda, sino que defines un nombre rango que varía según la cantidad de elementos.
En el menú Formulas, busca la opción Asignar nombre. O, más simple, presiona Ctrl + F3.
Con el botón "Nuevo" aparecerá un cuadro de diálogo como el de la imagen, donde colocas el nombre del rango y luego, en "Se refiere a:" colocas la fórmula y presionas Aceptar.
Esa fórmula cuenta cuántas etiquetas hay en el rango y le asigna esa dimensión a la extensión del rango.
Si prevés tener más de 500 elementos, puedes modificar ese parámetro en la fórmula.
Ten cuidado de que no haya textos por debajo de tu lista que pueda confundir ese conteo.
Como sabes, luego puedes usar =Tabla2 en una lista de validación de datos para elegir los elementos que tengas en tu tabla.
Ojala haya sido más claro ahora.
Saludos
Fernando
.
Muchas gracias ya he conseguido realizar lo correctamente, te podría pedir para terminar con esto dos preguntas más? Como te comente esto lo necesito para incluirlo en una celda dentro de otra tabla en otra hoja como se ve en la imagen de abajo.
Pues bien la pregunta siguiente con este tema es que yo para copiar la formula es arrastrarla las celdas abajo pero como podría hacer para que a medida que se vaya incrementando la tabla con nuevos valores esa celda contenga automáticamente la lista despegable sin tener que tener que arrastrarla del anterior?
Y la otra pregunta es parecida yo en la celda A4 puse el valor 1 y en la celda A5 quería poner la formula A4+1 para que me de el valor 2 y así sucesivamente pero esto claro esta como lo anterior que se haga automático según va creciendo en valores la tabla.
Muchas gracias por tu ayuda por anticipado de nuevo
Un saludo
.
Hola, Kike
A menos que no te haya entendido bien, las celdas que agregues y las anteriores tendrán siempre disponibles todas las opciones que figuren en tu rango dinámico.
Bastaría que pruebes en colocar una celda con la validación, luego agregas un elemento a tu tabla y vuelves a ver en esa celda si ese nuevo elemento está agregado al final. Verás que sí.
Simplemente, porque todas esas celdas con validación hacen referencia al rango dinámico que creaste.
Me confunde más la segunda pregunta porque la numeración de elementos tiene que ver con lo que figura en el campo "descripción" (mesa, cama, nevera) y no con la ubicación.
A menos que lo que quieras sea una numeración consecutiva de elementos dentro de cada ubicación.
Si, eventualmente hubiera acertado, prueba con esta fórmula en A4:
=CONTAR.SI(B$4:B4;B4)
Nota que el signo $está sólo en el extremo del rango. Esto significa que contará los casos iguales desde el principio hasta la fila actual, sin importar qué haya más abajo.
Luego copia esta celda y pégala hacia abajo.
En las celda siguiente deberías ver algo como esto:
=CONTAR.SI(B$4:B5;B5)
Y así sucesivamente.
Espero que sea lo que querías, pero consultame de nuevo, si no.
Saludos
Fernando
Pd: Ten en cuenta que el sitio no actualiza correctamente los post. Una pena porque genera confusiones. Tendremos que tener paciencia.
.
Hola de nuevo fejoal, gracias de antemano, quizás no me he explicado bien la verdad, el tema de la tabla para la validacion de datos esta perfecto y lo comprendo y sé que si meto un nuevo registro aparecerá automáticamente. Lo que me refiero con la pregunta anterior es en la tabla de la otra hoja cómo puedes ver en la celda B4 es donde uso la validacion de datos pues si suponemos que en esta tabla solo hay un registro pues cuando inserte uno nuevo en la celda B5 apareciera ya la lista despegable sin tener que arrastrarla de la anterior.
y la segunda parte pues más o menos lo mismo es como un número consecutivo para saber el número de registros que tiene la tabla simplemente.
un saludo
.
Ah, entiendo: se trata sólo de la comodidad de no tener que copiar dos celdas a donde agregues un registro.
En tal caso, agrega un módulo de Visual Basic (Alt F11 para activar el Editor de VBA), con la siguiente macro:
Sub AgregaCel() RangoCop = "A4:B4" Range(RangeCop).Copy ActiveCell End Sub
La celda A4, tendrá tu fórmula:
=A3 + 1
Así, en la columna A y en la fila donde quieres que se agreguen ambas celdas ejecuta esa macro.
Puedes agregar un botón o asignarle un atajo de teclado. Para esto, presiona Alt + F8
Selecciona allí (un sólo click sobre) la nueva macro ingresada y presiona el botón "Opciones". En la pantalla siguiente ingresa la letra a la que quieres asociar la macro. Por supuesto evita aquellas que ya están siendo utilizadas por MS Excel como atajo (v.g. Ctrl + C, Ctrl + X, etc)
Luego de aceptar, cuando presiones Ctrl más la letra indicada, la macro se ejecutará instantáneamente.
