lunes, 2 de marzo de 2015

Listas Desplegables "Excluyentes"

"En mi entrada de datos, tengo una serie de celdas con lista desplegable generada con validación de datos. Me gustaría que al ir seleccionando referencias de la lista, dichas referencias desaparecieran del listado disponible en el resto de celdas".

Para solucionar este problema, tendremos que utilizar varias funciones y herramientas así que, como dijo Jack el destripador, vamos por partes...

Partimos del siguiente ejemplo:
En el rango B3:B16 queremos tener listas desplegables en las que aparezcan los colores de la lista1 pero con la peculiaridad de que a medida que vayamos seleccionado colores de la lista éstos desaparezcan de la siguiente lista desplegable (como se muestra en la imagen a continuación):

A medida que vamos eligiendo colores en la entrada de datos, en las siguientes listas sólo quedan disponibles los colores no seleccionados todavía.
Empezamos dándole el nombre Datos al rangoB3:B16. Hecho esto, generamos la tabla que se puede ver en el rango G2:H11. La primera columna (columna G) es un número de orden de los elementos existentes (los colores). En la columna H vamos a formular. Nos situamos en H3 y escribimos:
=SI(O(Datos=E3);"";E3)   y pulsamos Ctrl + Shift + Enter ya que se trata de una fórmula matricial. Aparecerá así: {=SI(O(Datos=E3);"";E3)}
Esta fórmula comprobará los valores seleccionados en el rango B3:B16 (Datos) y hará que dichos valores desaparezcan (los convierte en "") de la lista existente en H3:H11. A continuación preparamos la siguiente zona de procesos:
Nos situamos en J3 y escribimos la fórmula:
=SI(H3="";0;G3)   y copiamos hasta J11.
En K3 escribimos:
=K.ESIMO.MAYOR($J$3:$J$11;G3)   y copiamos hasta K11. Con esta fórmula ordenamos de mayor a menor los valores obtenidos en la columna J.
Seleccionamos G3:H11 y le creamos el nombre Lista2. Vamos a L3 y escribimos:
=SI.ERROR(BUSCARV(K3;lista2;2;FALSO);"")    y copiamos hasta L11. Con esta fórmula estamos buscando en la tabla llamada Lista 2 los colores que se corresponden con los números de la columna K:
Si nos fijamos en la imagen, lo que hemos conseguido es generar una lista ordenada (Lista3) de los colores que todavía no hemos elegido. A partir de aquí podemos hacer dos cosas, a saber: generar directamente la lista desplegable en las celdas B3:B16 a partir del rango Lista3 (en este caso aparecerán sólo los colores no elegidos pero con espacios en blanco en la lista); o generar un rango dinámico para ajustar la lista desplegable a sólo los colores que aparecen en la Lista3. Vamos a ver las dos opciones. En cualquier caso empezamos por crear el nombre Lista3 para el rango L3:L11. 

Opción 1: Seleccionamos B3:B16 vamos a Validación de datos/Permitir: Lista y en Origen escribimos  =Lista3  y aceptamos:
Como se puede comprobar, a medida que vamos eligiendo colores desaparecen de la lista pero queda el espacio en blanco dentro el desplegable.

Opción 2: Creamos un rango dinámico para evitar filas en blanco dentro del desplegable. Para ello seleccionamos el rango B3:B16 vamos a Validación de datos/Permitir: Lista y en Origen escribimos:
=DESREF($L$2;1;;CONTAR.SI($K$3:$K$11;"<>0"))  y aceptamos (para más información sobre esta fórmula véase el post Lista Desplegable con Rango Dinámico):

5 comentarios:

  1. Buenas noches de nuevo Kiko, de nuevo ando con un nuevo problemilla. Resulta que quiero crear desplegables dependientes, en el caso de que en rango de celdas sea nombrado con una sola palabra no tengo problema pero en el caso de varias palabras no puedo hacerlo, intento poner las palabras separadas por _ y luego añadir una nueva columna y con la formula sustituir quitar ese _ pero no funciona. Me podrías aportar ideas de como solucionarlo? Gracias

    ResponderEliminar
    Respuestas
    1. http://lareboticadeexcel.blogspot.com.es/2015/03/desplegables-dependientes-con-nombres.html

      Eliminar
  2. Hola Lucía. Debes crear una tabla con los nombres originales en la primera columna y, en la segunda columna, los nombres con guión bajo sustituyendo el espacio en blanco. Así podrás utilizar un BUSCARV anidado dentro de la función INDIRECTO y problema resuelto. Te lo cuento en mi post de hoy.

    ResponderEliminar
  3. Tengo una lista de 70 "Profesores" que tiene intercalados espacios vacíos (no debo quitarlos) y eso hace 300 registros. Necesito elegir a los "Profesores" repetidamente (no eliminarlos del listado) mediante listas desplegables en 376 celdas. El problema son los espacios vacíos en la lista desplegable. Gracias

    ResponderEliminar
    Respuestas
    1. Hola Manuel. Puedes encontrar solución en:
      http://lareboticadeexcel.blogspot.com.es/2013/02/lista-de-valores-unicos-con-formulas.html
      Aunque el objetivo es generar lista de valores únicos, lo puedes aplicar a tu caso para agrupar los profesores y que no queden espacios en blanco en medio. Ya me dirás si pudiste resolverlo. Un saludo

      Eliminar