validacion de datos dinamica

Esta consulta es muy particular, sencilla de comprender... pero no tanto para resolver. En realidad soy programador y siempre que necesité "funciones raras" las hice en VBA, aunque tengo que admitir que este tipo de planteamientos de ayudaron siempre a aprender sobre las funciones de Excel, ya que soy un total autodidacta en ello.

Mi lector, futbolero viejo por lo que veo, tiene una tabla con equipos locales:

Con estos ocho equipos arma un cuadrangular. Me dice: "quiero seleccionar cada equipo de una lista y para evitar errores, que el equipo seleccionado no esté mas la proxima vez que elija".

[+/-] Ver el resto / Ocultar

Recordemos los pasos fundamentales: vamos a "Datos / Validación", del cuadro emergente seleccionamos "Lista" y allí indicamos el rango en donde se encuentran los equipos:

Si armamos una tabla para este cuadrangular, sería algo como esto:

en I1 observarán la clásica flecha desplegable de la validación de datos. Y los mas observadores se habrán dado cuenta del error que mi lector quiere evitar: en la tercer línea el cuadro "independiente" se enfrenta a sí mismo.

Visto el problema planteado, cada vez que se seleccione un equipo este debe desaparecer de la lista. Se me ocurrió la siguiente solución, en la cual hago uso de un par de columnas auxiliares.

Como primer medida, armamos la tabla expuesta en la primer imagen del post, la cual contrendrá todos los cuadros de futbol involucrados.

Ingresaremos en la columna C una función matricial que nos detecte si algún equipo fué ingresado al cuadrangular. Esto lo hacemos así: {=O(B1=$G$1:$I$4)}

la funcion recorre el cuadrangular comparándolo contra la tabla de equipos. vemos que boca y river están... y en la columna C aparece VERDADERO. ya empezamos.

Realizaremos un pequeño agregado a la función: la voy a multiplicar por el numero de fila que ocupa cada equipo en la tabla, para ir conociendo su ubicación (si, podemos multiplicar VERDADERO o FALSO por un numero):={=O(B1=$G$1:$I$4)*FILA()}

bien: boca y river están en la segunda y tercer fila, respectivamente. ya vamos alcanzando algo mas, aunque aún nos resta.

Lo mostrado detalladamente hasta ahora es para que se comprenda bien la lógica. Pero necesito invertir esa lógica, ya que deberé conocer mejor los que no están seleccionados. El cambio es mínimo:

=SI(O(B1=$G$1:$I$4);0;FILA())

Explicacion: si el equipo está en el cuadrangular poné 0, si no la fila:

va quedando... los equipos (boca y river) que tengo en el cuadrangular ahora me aparecen con 0, y los restantantes me indican en que numero de fila de la tabla se encuentran.

En terminos del ejemplo, esos ceros me molestan. Así que voy a ordenar la lista (ver esta entrada sobre ordenamiento con funciones) en forma ascendente, para dejarlos al final:

En una nueva columna auxiliar: =K.ESIMO.MAYOR($C$1:$C$8;FILA())

en D tengo los mismos datos que en C, pero ordenados de mayor a menor.

Ahora voy a reemplazar esos números de la columna D por los nombres de cada equipo. Recordemos que esos numeros me están indicando nada mas y nada menos que el nro de fila en la que se encuentra cada cuadro, lo que me permite utilizar esto: (no se asusten, luego explico la función =) )

{=SI(K.ESIMO.MAYOR($C$1:$C$8;FILA())>0;INDICE($B$1:$B$8;K.ESIMO.MAYOR($C$1:$C$8;FILA()));"")}

Explicación: si el resultado de K.ESIMO.MAYOR() (la cual utilizo para ordenar) es mayor a cero, utilizo el resultado de K.ESIMO.MAYOR() como argumento de la función INDICE(matriz, numero de fila) para encontrar los equipos no seleccionados.

Vamos a unas imagenes, para ver esa superfórmula matricial en accion:

ningun equipo está dentro del cuadrangular.

ahora entraron 4 equipos al juego. noten como desaparecieron de la columna D. Nuestro proyecto funciona.

Ahora resta aplicar la valicacion de datos. Seleccionamos las columnas G e I:

seleccionamos "lista" e ingresamos el rango "móvil" que hemos creado. presionamos Aceptar.

Listo el pollo. Veamos como queda todo esto:

en G1 seleccioné un equipo. Pueden ver que ya no está mas en la lista de la validación de datos.

Si repetimos esto:

cada vez mas equipos ingresados al cuadrangular, los cuales desaparecen de la validación.

Al haber utilizado la funcion K.ESIMO.MAYOR() nos aseguramos que los cuadros que aún resten elegir aparezcan al principio, dejando los campos en blanco al final.

Terminado nos queda:

todos los equipos están dentro del cuadrangular, la lista se observa vacía y este proyecto nos permitió que el usuario evite enfrentar a dos equipos iguales.

Link al archivo.

Por cierto... es Argentino Jr o Argentinos Jrs? Eso ahora lo busco en Google.. yo de deportes, menos diez, un cero absoluto. Sabrá la gente de futbol disculparme.... lo mío es la programación. =)