Excel: crie uma lista de validação a partir de valores filtrados na coluna

Question

Eu tenho tentado encontrar uma maneira de fazer isso com suspensões dependentes, mas ainda estou para ter sucesso com minhas tabelas específicas. Essencialmente, estou procurando criar uma lista de validação com base em uma lista de resultados "filtrada" de outra tabela.

Estas são tabelas estruturadas:

Tabela1 (com valores estáticos para serem "filtrados")

id      Items
2       ItemAA
2       ItemAAA
3       ItemZZ
5       ItemXY
5       ItemXYZ
2       ItemCC

Tabela2 (com o menu suspenso Lista de validação)

idFromTable1  RelatedOptionFromFilteredDropdownList
2             ItemAA  (dropdown lists: ItemAA, ItemAAA, ItemCC)
5             ItemXY  (dropdown lists: ItemXY, ItemXYZ)
2             ItemCC  (dropdown lists: ItemAA, ItemAAA, ItemCC)
3             ItemZZ  (dropdown lists: ItemZZ)

Editar : usando fórmulas, não VBA

Edit2 : ID e itens serão contados em centenas ou milhares.

Obrigado

    

Answer 1

Crie uma lista nomeada

1. Primeiro, classifique sua Tabela1 por ID
2. Crie um intervalo nomeado com o nome listlookup (ou qualquer outro).
3. Coloque esta fórmula no listlookup

=OFFSET(INDIRECT(CELL("address", INDEX($B$2:$B$5, MATCH($C1, $A$2:$A$5, 0)))), 0, 0, COUNTIF($A$2:$A$5, $C1), 1)

 ID | Items   | lookup_value_here | dropdown_list_here
 1  | ItemXY
 1  | ItemXYZ
 2  | ItemZ
 2  | ItemZZ

4. Crie a lista suspensa e consulte a lista para listlookup

Explicação

INDEX($B$2:$B$5, MATCH($C1, $A$2:$A$5, 0)) encontra a primeira célula que possui o lookup_value

CELL( ... ) retorna a string referente à célula (por exemplo: $B$2 para lookup_value 1)

INDIRECT( ... ) converte a referência de string para referência de célula

OFFSET( ... , 0, 0, ... , 1) retorna o intervalo da pesquisa (por exemplo: B2:B3 para lookup_value 1)

COUNTIF( ... ) retorna o número de dados que correspondem ao lookup_value

Isso é divertido, porque eu me deparei com esse problema no passado e não consigo resolvê-lo.