Validación de datos dependiente con DESREF

Veremos hoy una forma muy interesante de montar una validación de datos dependiente, diferente a la clásica de INDIRECTO (ver ejemplo aquí), empleando la función DESREF.

Solo necesitamos una sola base de datos que cumpla la condición de tener los datos ordenados...

El primer paso es obtener un listado de elementos únicos del campo de Regiones...
Para esto usaré la nueva función desbordada UNICOS (o su alternativa clásica).
En E3 escribimos:
=UNICOS(TblaCCAA[CCAA])

Sobre esa fórmula desbordada montamos nuestra Validación de datos tipo lista en la celda G2:

Y ahora la última parte, donde según nuestra elección de la región en G2, se desplegarán solo los elementos correspondientes...
La fórmula buscada sería:
=DESREF(TblaCCAA[[#Encabezados];[Provincia]];COINCIDIR(G2;TblaCCAA[CCAA];0);0;CONTAR.SI(TblaCCAA[CCAA];$G$2))

Vemos su comportamiento ejecutándola en el rango G6...
El funcionamiento es simple, ubica el inicio del rango variable a mostrar en la primera coincidencia de la región buscada en G2
COINCIDIR(G2;TblaCCAA[CCAA];0)
con una altura del rango equivalente al número de veces que aparece repetida dicha región
CONTAR.SI(TblaCCAA[CCAA];$G$2)

Crearemos un nombre definido 'ndProvincias' con esta fórmula

Este nombre definido lo incluiremos directamente en la validación de datos tipo lista de la celda H2

Y listo.. ya disponemos de nuestra validación dependiente, con una gestión asombrosamente simple de nuevos elementos... más flexible que la forma clásica de INDIRECTO.