Listas suspensas dinâmicas do Excel baseadas em uma matriz

Question

Estou procurando controlar as opções disponíveis em uma série de listas suspensas (criadas usando a validação de dados) com base em uma matriz de controle.

Na captura de tela de exemplo abaixo, a primeira lista suspensa permite a seleção de feature1 ou feature2. Se feature1 for selecionado, os Xs na matriz indicam que os recursos 4 e 5 não são permitidos na lista suspensa 2 e a lista suspensa 2 deve exibir apenas os recursos 3 e 6 como possíveis seleções. Se o recurso 3 for selecionado na segunda lista suspensa, a lista suspensa 3 exibirá somente o recurso 8 como uma opção. O recurso 7 não está disponível se o recurso 3 for selecionado e o recurso 9 for excluído em virtude da seleção do recurso 1 no início.

Espero que isso faça algum sentido. Eu estaria interessado em idéias de povos sobre a melhor abordagem / opções disponíveis para alcançar este nível de controle. Eu usei intervalos nomeados e a função indireta para obter resultados semelhantes, mas não ao usar uma matriz para especificar as opções disponíveis. Eu gostaria de evitar espaços em branco nas listas suspensas.

matriz de controle

    

Answer 1

A captura de tela abaixo mostra como fazer isso usando uma lista dinâmica de valores para seus menus suspensos.

A primeira lista de valores, List Values 1 , é definida com fórmulas usando as opções das duas primeiras listas suspensas. Esta lista implementa a lógica mostrada na sua matriz. Se eu não traduzi seu texto corretamente em fórmulas, ajuste-as conforme necessário.

Asegundalistadevalores,ListValues2,criaumalistaordenadadinâmicadosvaloresdeListValues1,colocandotodososvaloresembranconofinal.AsfórmulasemE14eE18são:

=IFERROR(INDEX(E$5:E$8,AGGREGATE(15,6,(ROW(E$5:E$8)-ROW(E$4))/(E$5:E$8<>""),ROWS($E$14:$E14)),1),"")
=IFERROR(INDEX(E$9:E$11,AGGREGATE(15,6,(ROW(E$9:E$11)-ROW(E$8))/(E$9:E$11<>""),ROWS($E$18:$E18)),1),"")

Por fim, duas fórmulas criam matrizes dos valores não em branco em List Values 2 e são atribuídas aos nomes List_2 e List_3 , que são usados para especificar os valores de lista para os Dropdowns 2 e 3. Essas fórmulas são mostrados na imagem.

Espero que isso ajude e boa sorte.

por 20.03.2018 / 15:48