Validação de dados do Excel entre planilhas

Question

Eu tenho uma lista de IDs e Descrições na Folha 1. Esses dados são usados em uma segunda planilha (Planilha 2) com a qual o usuário pode interagir.

A lista na Folha 1, tem um ID que se repete, mas tem uma descrição diferente. Como eu seria capaz de usar a validação de dados na Folha 2 para combinar todas as repetições do número de identificação 4 em 1, mas permitir que o usuário selecione a descrição (usando uma caixa suspensa) desejada, após a seleção na Coluna A ser feita ?

Eu gostaria de ficar longe das macros para isso - usando apenas funções internas.

Qualquer ajuda é apreciada!

    

Answer 1

Combine as repetições na Folha1. As fórmulas de pesquisa retornam apenas o primeiro valor encontrado, portanto, se você tiver múltiplos do mesmo ID, será necessário criar uma pesquisa com IDs exclusivos. Prepare isso em uma tabela auxiliar e use a tabela auxiliar para a validação de dados se você não quiser ou não puder alterar a tabela original.

É bastante enganador que um identificador (que é chamado por uma razão) não seja único.

    

Answer 2

Você não mencionou a versão do Excel que está usando. Sugiro uma possível solução com base no meu entendimento da sua pergunta. No entanto, também usa funções denominadas IFERROR & COUNTBLANK que eu suponho não estão disponíveis antes do Excel 2007. Portanto, no caso de você ter o Excel 2003, uma abordagem diferente pode ser necessária.

Esta pode não ser a solução ideal, pois usa colunas auxiliares e também uma folha auxiliar!

Neste exemplo, os dados de amostra estão na Planilha1! A2: B10, como visto nesta captura de tela.

Primeiro, precisamos criar uma coluna auxiliar na coluna D para obter a lista exclusiva de seus IDs.

Em D2, coloque a seguinte fórmula e pressione CTRL + SHIFT + ENTER na barra de fórmulas para criar uma fórmula de matriz. Você também precisa deixar uma linha (preferencialmente a primeira linha) como cabeçalho para que isso funcione corretamente.

A fórmula deve ser incluída em Chaves Encaracoladas para indicar que é uma fórmula de matriz.

Arraste-o para baixo até as linhas pretendidas com base na contagem esperada de valores exclusivos na sua coluna principal. Eventualmente, ele começará a retornar espaços em branco na parte inferior, e você poderá parar. Isso cria uma Lista Mestra Única de IDs.

=IFERROR(INDEX($A$2:$A$10, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$10), 0)),"")

Agora,precisamossaberoinícioeofinalexatosdessalistaparapreencheraListadevalidação.

Agora,noG3,coloqueaseguintefórmula.

=INDEX(Sheet1!D2:D10,1)

enoG4coloqueaseguintefórmuladematriz.

=MIN(IF(Sheet1!D2:D10="",ROW(Sheet1!D2:D10),9^99))-2

Não se esqueça de CTRL + SHIFT + ENTER para este.

Agora vá para o Gerenciador de nomes e crie um novo nome chamado MyList

Coloque a seguinte fórmula aqui

=INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$3):INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$4)

Aqui usamos INDEX para retornar uma referência de célula em vez de um valor

InsiraumanovaplanilhachamadaPlanilha2.

Agora,noColA2,crieumaListadevalidaçãoecoloque=MyList.

Agora,todososvaloresexclusivossãoexibidosnaprimeiralistasuspensadeIDs.

IssoconcluiaParte1.

Agora,apróximatarefaépreencherumasegundalistadevalidaçãocombasenosdadosextraídosdacolunaDescrição.

InsiraumaterceirafolhaauxiliarchamadaPlanilha3.Vocêpodesimplesmenteocultarestafolha.

Nesteexemplo,eupegueidadosemSheet3!B2:H10.DecidaonúmerodecolunascombasenonúmeromáximodeduplicatasemseusIDsdalistaprincipal.Alémdisso,deixeaprimeiracolunaAparaqueissofuncionecorretamente.

EmB2,coloqueaseguintefórmulaepressioneCTRL+SHIFT+ENTERnabarradefórmulasparacriareFórmuladematriz.

Arraste-oparabaixoeabaixoconformepretendido.

=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF(Sheet2!$A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$A$2:$A$10)-1,999),COLUMN()-COLUMN($A$2))),"")

DevoltaaoGerenciadordeNomes,crieumnovonomechamadoTrimmedecoloqueaseguintefórmulanele.

=OFFSET(Sheet3!$B1,0,0):OFFSET(Sheet3!$B1,0,COUNTA(Sheet3!$B1:$H1)-COUNTBLANK(Sheet3!$B1:$H1)-1)

IssogeraumalistaexcluindoespaçosembrancoparaqueaListasejacorretamenteaparada.

Agora,naPlanilha2paraCélulasB2,crieumaListadeValidaçãoecoloque=Trimmednela.

Agora,conformeseusdadossãoalteradosnaPlanilha1!A2:A10,alistadeValoresExclusivosérefletidaautomaticamenteemMyListetambémonomeRecortadobuscarásomenteaDescriçãopretendidadacolunaDescriçãodaPlanilha1.

VejaatelaabaixoparaGIFparaterumaideiadecomotudofunciona.