Excel - Como criar listas de picking dinamicamente

Question

Aqui está a planilha A:

Parent    Kid        Age
------    ---        ---
John      Scott      5
John      Lucas      7
John      Elisabeth  12
Victoria  Jason      3
Victoria  Amy        5
Jenifer   Ashely     13
Jenifer   Jared      17

E a planilha B:

Parent    Kid        Team
------    ---        ----
John      Elisabeth  Lions
Jenifer   Ashely     Sharks
Jenifer   Jared      Panters

Na planilha B, quero que meus usuários possam escolher o nome da criança em uma lista de seleção (usando a validação de dados). Os valores dessa lista de seleção devem ser orientados pelos nomes de crianças inseridos para esse pai na planilha A. Por exemplo, se eu selecionar a célula A2, a lista de seleção deverá conter:

Scott
Lucas
Elisabeth

Posso fazer isso com uma fórmula regular ou devo usar o VBA?

    

Answer 1

Acabei criando um intervalo nomeado chamado Kids com a seguinte fórmula:

=OFFSET(WA!$B$2, MATCH(INDIRECT("A" & ROW()), WA!$A$2:$A$8, 0) - 1, 0, COUNTIF(WA!$A$2:$A$200, INDIRECT("A" & ROW())), 1)

Em seguida, defina a lista de validação para =Kids .

Caso alguém se pergunte, usei INDIRECT("A" & ROW()) porque o uso do A1 retornou um erro (meu excel-fu não é strong o suficiente para entender o porquê). Fazendo WB! A1 funcionou bem, mas eu não queria ir por esse caminho, porque eu tenho que reutilizar esse intervalo de nome em outras planilhas dessa pasta de trabalho. Se há uma maneira melhor, eu ficaria feliz em ouvir isso.

    

Answer 2

Eu não sei exatamente se é a maneira mais simples, mas se é certo, os pais estão agrupados na Planilha A (então é como se você tivesse escrito, e não como, por exemplo.

John     Scott
Victoria Jason
John     Lucas

), você pode considerar o uso dessa solução (suponho que os dados da sua planilha começam com "John" na célula A2, WA corresponde à planilha A e WB corresponde à planilha B):

Primeiro, adicione outra coluna, chamada offset em WA. Como "Age" é C column, eu coloco na coluna D . A fórmula é =Row(A2)-Row($A$2) e é propagada para baixo. Você deve obter 0 , 1 , 2 ... Estes são deslocamentos de cada linha em relação ao primeiro.

Usaremos esse valor para nosso deslocamento usando a função VLOOKUP . No WB, digamos que você insira o nome do seu pai na célula A1 , em B1 nós iremos obter o (s) nome (s) da criança. Então, na coluna auxiliar, digamos C , colocamos a fórmula

=VLOOKUP(A1; WA!$A$2:$D$xxx; 4; FALSE)

os parâmetros significam:

• A1 é o valor da célula que você está pesquisando - o nome do pai
• WA!$A$2:$D$xxx denota o intervalo em que você pesquisa (substitua xxx pelo número de linhas obtidas)
• 4 significa que queremos recuperar o valor da quarta coluna, que é WA!D neste caso, e esse é nosso deslocamento
• FALSE significa que queremos realizar a pesquisa exata, pois a coluna WA!A é (ou pode ser) não classificada.

O valor que você obtém é o valor em que seu intervalo deve começar. Agora precisamos encontrar seu final, isso significa quantas linhas existem. A maneira mais simples pode ser adicionar outra coluna no WA, digamos E , onde colocamos uma fórmula simples contando quantos filhos um pai tem:

=COUNTIF($A$2:$A$xxx; A2)

Em seu exemplo, você receberá 3 , 3 , 3 , 2 , 2 , 2 , 2 .

Você deve copiar este valor para WB, coluna D , usando novamente:

=VLOOKUP(A1; WA!$A$2:$E$xxx; 5; FALSE)

Agora, no WB, temos na coluna A nome do pai, na coluna B inserimos um filho, na coluna C existe deslocamento do primeiro filho do pai, e na coluna D número de filhos que o pai tem.

Agora vá até o gerenciador de nomes e adicione o novo nome Kids cobrindo o intervalo:

=OFFSET(WA!$B$2:$B$xxx; WB!D1; 0; WB!E1)

onde os parâmetros significam:

• WA$B$2:$B$xxx - é o intervalo que contém nomes filhos,
• WB!D1 (nota: é relativo!) é o deslocamento, então onde a lista filho inicia no intervalo anterior em WA,
• 0 significa que não queremos fazer nenhum deslocamento nas colunas,
• WB!E1 diz quantas linhas queremos obter (ou seja, quantos filhos).

Tendo esse nome Kids , adicione agora em WB!B1 a validação de dados, defina para lista e a origem é =Kids . Agora, propague-o para baixo.

Isso é tudo.

Se os seus dados no WA não contiverem pais agrupados, você poderá obter isso classificando-os.