Un lector me presenta el siguiente caso: ha creado una lista desplegable que muestra una lista de facturas. ¿Cómo hacer para que las facturas abonadas vayan desapareciendo de la lista desplegable?
Supongamos que esta es la lista de las facturas
En el rango B6:B19 aparecen los números de las facturas y en el rango C6:C19 si han sido pagadas (señalado con la palabra “pagada).
En nuestro ejemplo, queremos crear una lista desplegable que sólo muestre aquellas facturas que no han sido pagadas
Por supuesto, queremos que esta lista sea dinámica, es decir que cuando pongamos “pagada” al lado de una factura, ésta desaparezca de la lista
.
La solución consiste en crear dos columnas auxiliares y una tercera que será la que contenga los valores válidos para la lista.
La solución consiste en crear dos columnas auxiliares y una tercera que será la que contenga los valores válidos para la lista.
Empezamos por crear la primer columna auxiliar en el rango E6:E19.
En la celda E6 ponemos esta fórmula
=SI(LARGO(C6)=0,FILA(),"")
y la copiamos a todo el rango.
Esta columna nos da un “número de orden” que es de hecho un número entero único (el número de fila) que identifica a las facturas que aún no han sido pagadas.
Ahora agregamos una segunda columna auxiliar en el rango F6:F19 con esta fórmula
=INDICE($B$6:$B$19,COINCIDIR(K.ESIMO.MENOR($E$6:$E$19,FILA()-5),$E$6:$E$19))
Como puede verse, sólo las facturas pendientes aparecen en la lista.
Tenemos un pequeño problema y es que cuando una factura ha sido pagada el valor de la fórmula es #NUM!.
Si usáramos el rango F6:F19 para crear la lista de los valores, también el error #NUM! aparecería en ella lo cual es poco elegante.
Una de las soluciones es crear una tercera columna en el rango G6:G19 con la fórmula
=SI(ESERROR(F6),"",F6)
Este rango es el que usamos para definir la lista de valores válidos. Creamos el nombre "lista"
Ahora usamos validación de datos para crear la lista desplegable en la celda C2
Si quitamos la palabra “pagada” en alguna celda, la factura correspondiente aparecerá automáticamente en la lista; si agregamos “pagada”, la factura desaparecerá.
Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.
Technorati Tags: MS Excel
Muy bueno Jorge!!!
ResponderBorrarLo unico que por ahi tienen estas formulas es que al especificar un rango (como vos pusiste en el ejemplo "INDICE($B$6:$B$19...") nos vemos luego obligados a actualizar la formula cada vez que se inserten filas o bien a insertar filas completas antes de, por ej, la fila 19, para que esa actualización se haga automaticamente.
Seria fantastico que la gente de Microsoft se ponga a trabajar en nuevas formulas o comandos para definición de rangos (algo al estilo $b$6:"ultimo dato hacia abajo"). Se entiende? Sera soñar demasiado?
Un abrazo, José
Jorge:
ResponderBorrarGracias eso me da una solución.
te agradezco la premura en tu respuesta.
Saludos
Giovanni
José
ResponderBorrareso existe en Excel, se llama "rangos dinámicos". He mostrado en algunas notas cómo crearlos.
Jorge pesar de no ser partidario de utilizar VBA a menos que sea estrictamente necesario, te dejo este archivo que me parece es una buena opción para resolver el mismo problema.
ResponderBorrarhttp://www.esnips.com/doc/67a3ee6c-7d83-47c5-8650-9dd20fbea6e5/Validacion-con-Tabla-Din%C3%A1mica
Espero sea de utilidad.
Jorge, implementé tu solucion, me ha servido mucho. Te envie un mail con un agregado para que lo veas, que era lo que me faltaba. MUCHAS GRACIAS!!
ResponderBorrarsoy Rudajeli y apenas estoy aprendiendo en esto de excel modifique un poco la formula por que no entendi al gunas cosas como por ejemplo en la formula de K.ESIMO.MENOR se debe colocar Fila()-5 no entiendo porque si coloco otro numero no me funciona y lo otro que no entendi fue la formula =SI(LARGO(C6)=0,FILA(),"") y por eso no la coloque en mi ejercicio a cambio de esa coloque estas en la columna aux 2 coloque esto: =SI(C7="Pagada";"";B7)
ResponderBorraren aux 3 coloques esto:=SI(ESERROR(K.ESIMO.MENOR($E$6:$E$27;FILA()-5));"";K.ESIMO.MENOR($E$6:$E$27;FILA()-5))
y en la validacion de datos utilice la columna de axu 3 y funciono bien
gracias
Estimado Rudajeli, intento una explicación, dado que llegó a mi correo…
ResponderBorrarK.ESIMO.MENOR arroja el el “K” menor valor en un rango o matriz:
Sintaxis : K.ESIMO.MENOR (matriz; k)
Por lo tanto se coloca FILA()-5 porque en el ejmplo se pretende ordenar usando del valor de la fila, y como el rango comienza en la fila 5 se le descuenta “5 filas” al valor desde donde se parte. De este modo, se parte del valor uno FILA()-5 = 1, es decir, cuando “k” valga 1 estaremos buscando el menor valor del rango. A la fila siguiente FILA() vale 7 , entonces FILA()-5 = 2, y entnces aquí “k” valdra 2 y colocará el segundo menor valor, y así sucesivamente.
Con respecto a la función =SI(LARGO(C6)=0;FILA();””) el condicional indica que si no tenemos escrito nada (función LARGO(texto), devuelve el nº de caracteres de un texto) en la C6 entonces colocará el valor de la fila, de lo contrario que no coloque nada. Este rango de valores lo utilizará en la función =INDICE con COINCIDIR.
PD.: la ayuda de Excel en la ventana cuando pide que se ingresen los argumentos de la función con la que estamos trabajando (extremo inferior izquierdo) es muy orientativa. Para examinar una función cualquiera, la colocamos en una celda cualquiera y cuando nos pide los argumentos hacemos clic en “ayuda con esta función”. Saludos cordiales.
Gracias por el ejemplo!!!
ResponderBorrarPor favor, abusando de su paciencia, ¿y si lo que queremos es que en una lista desplegable referenciada a un rango de celdas con celdas en blanco, no aparezcan dichas celdas en blanco en nuestra lista desplegable?.
Es que me estoy comiendo las uñas,... :(
Muchas gracias por su Blog.
Rocio.Fuenlabrada.
Madrid.
Me sumo a la pregunta de Rocio.
ResponderBorrarGracias.
Rocío
ResponderBorraren primer lugar mis disculpas, recién hoy presté atención a tu comentario. En segundo lugar, muy feo eso de comerse la uñas!!!
Y en tercero, ¿te refieres los blancos que aparecen al final de la lista desplegable?
Hola Rocío, te comento que el inconveniente de los “blancos” en el desplegable se soluciona con la formula DESREF aplicándola al rango que Jorge llama “Lista” para hacerlo dinámico, teniendo en cuenta que el “Alto” del mismo vendrá dado por el valor (ó cantidad) de blancos que hay en la columna “Estado”. Esto es posible dado que siempre existirá igual cantidad de blancos en la columna” Estado” que de celdas ocupadas con números de facturas en la columna origen del desplegable (o sea “Lista”):
ResponderBorrar=DESREF(Hoja1!$G$6; 0; 0; CONTAR.BLANCO(Hoja1!$C$6:$C$19); 1)
Sería posible construir una lista desplegable que fuera solamente informativa?
ResponderBorrar¿Podrías explicar con más detalle qué es una lista sólo informativa?
ResponderBorrar