Note: The other languages of the website are Google-translated. Back to English

 Como criar uma lista suspensa, mas mostrar valores diferentes no Excel?

Na planilha do Excel, podemos criar rapidamente uma lista suspensa com o recurso Validação de Dados, mas você já tentou mostrar um valor diferente ao clicar na lista suspensa? Por exemplo, eu tenho os seguintes dados de duas colunas na Coluna A e Coluna B, agora, eu preciso criar uma lista suspensa com os valores na coluna Nome, mas, quando eu seleciono o nome da lista suspensa criada, o correspondente valor na coluna Número é exibido conforme a captura de tela a seguir. Este artigo apresentará os detalhes para resolver essa tarefa.

doc dropdown diferentes valores 1

Crie uma lista suspensa, mas mostre um valor diferente na célula da lista suspensa


Crie uma lista suspensa, mas mostre um valor diferente na célula da lista suspensa

Para terminar esta tarefa, execute o seguinte passo a passo:

1. Crie um nome de intervalo para os valores de célula que deseja usar na lista suspensa, neste exemplo, irei inserir o nome na lista suspensa nome Box, e depois pressione Entrar chave, veja a captura de tela:

doc dropdown diferentes valores 2

2. Em seguida, selecione as células onde deseja inserir a lista suspensa e clique em Dados > Validação de dados > Validação de dados, veja a captura de tela:

doc dropdown diferentes valores 3

3. No Validação de dados caixa de diálogo, sob o Configurações guia, escolha Lista de Permitir desça e clique em doc dropdown diferentes valores 5 botão para selecionar a lista de nomes que deseja usar como valores suspensos no fonte caixa de texto. Veja a imagem:

doc dropdown diferentes valores 4

4. Após inserir a lista suspensa, clique com o botão direito na guia da planilha ativa e selecione Ver código a partir do menu de contexto, e no aberto Microsoft Visual Basic para aplicativos janela, copie e cole o seguinte código no módulo em branco:

Código VBA: exibe um valor diferente na lista suspensa:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    selectedNa = Target.Value
    If Target.Column = 5 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

doc dropdown diferentes valores 6

Nota: No código acima, o número 5 dentro Se Target.Column = 5 Then script é o número da coluna que sua lista suspensa localizou, o “suspensa" nisso selectedNum = Application.VLookup (selectedNa, ActiveSheet.Range ("dropdown"), 2, False) code é o nome do intervalo que você criou na etapa 1. Você pode alterá-los para o seu necessário.

5. Em seguida, salve e feche este código, agora, ao selecionar um item da lista suspensa, um valor relativamente diferente é exibido na mesma célula, veja a captura de tela:

doc dropdown diferentes valores 7


Demonstração: Criar lista suspensa, mas mostrar valores diferentes no Excel

Kutools for Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!

 


As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório

 

Comentários (42)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Isso pode ser feito em folhas diferentes? Quero dizer, na folha1 o menu suspenso e na folha2 o intervalo. Como eu tenho que codificar isso? Desde já, obrigado. Tina.
Este comentário foi feito pelo moderador no site
E se eu quiser fazer mais de uma lista suspensa que retorne valores diferentes na mesma planilha? Você pode me mostrar um exemplo da codificação para dois ou mais?
Este comentário foi feito pelo moderador no site
Lee Ann

Se você apenas copiar e colar o código do If para o EndIf e alterar a coluna # e a Tabela, deve funcionar:


Sub Worksheet_Change(ByVal Target As Range)
selecionadoNa = Alvo.Valor
Se Target.Column = 5 Then
selectedNum = Application.VLookup (selectedNa, ActiveSheet.Range ("dropdown"), 2, False)
If Not IsError(selectedNum) Então
Target.Value = selectNum
Se acabar
Se acabar
Se Target.Column = 9 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown1"), 2, False)
If Not IsError(selectedNum) Então
Target.Value = selectNum
Se acabar
Se acabar
End Sub

Não estou dizendo que esta é a maneira correta, mas funcionou na minha versão de teste. Estou usando o Excel 2013
Este comentário foi feito pelo moderador no site
Apenas tentei. E funcionou!! Obrigada.
Este comentário foi feito pelo moderador no site
Oi, ajuda, não funciona, você pode colar aqui todo o código para 2 colunas?
Este comentário foi feito pelo moderador no site
Tony: Correto, mas o próximo passo é evitar encorajar: a) violações do Princípio DRY (e, portanto, aumento de: a.1) chances de erros e custos para corrigi-los e a.2) custos de possíveis mudanças/melhorias futuras) por não incentivando a programação "Copy-Paste", mostrando como ela pode ser "refatorada" para reduzir o código duplicado e b) constantes codificadas literalmente ("mágica" "número" / "string"), declarando e usando constantes nomeadas que são muito mais difícil de digitar incorretamente sem acionar um erro do compilador. As únicas diferenças entre os dois blocos de código copiados e colados são os números das colunas e os nomes dos intervalos, portanto, tanto quanto possível, tudo o mais não deve ser duplicado, portanto, em vez disso, o código deve, por exemplo, ser:

' -- NOTA: Declare "EmptyString" em um módulo de declarações globais.
Public Const EmptyString como String = ""

' -- Coluna #s. NOTA: Na prática, por exemplo, "Col5Header" seria, por exemplo, "ProductID" e "Col9Header" seria, por exemplo, "SalesID".
Private Const Col5HeaderColumnNumber As Integer = 5
Private Const Col9HeaderColumnNumber As Integer = 9

' -- Nomes de intervalos das listas suspensas das colunas
Private Const Col5HeaderDropDownRangeName as String = "col5HeaderDropDownRangeName"
Private Const Col9HeaderDropDownRangeName as String = "col9HeaderDropDownRangeName"

Sub Planilha_Alterar _
( _
Alvo ByVal como intervalo _
)

' -- BEGIN Para =colunas aplicáveis, insira o ID para a descrição selecionada nas listas suspensas.
Dim dropDownListRangeName como String
dropDownListRangeName = EmptyString
Selecione Alvo do Caso. Coluna
Caso Col5HeaderColumnNumber
dropDownListRangeName = Col5HeaderDropDownRangeName
Caso Col9HeaderColumnNumber
dropDownListRangeName = Col9HeaderDropDownRangeName
End Select ' -- Case Target.Column
Se (dropDownListRangeName <> EmptyString) Então
Dim selectedId como String
selectedId = Application.VLookup(selectedNa, ActiveSheet.Range(dropDownListRangeName), 2, False)
Se não for IsError(selectedId) Então
Target.Value = id selecionado
End If ' -- Not IsError(selectedId)
End If ' -- (dropDownListRangeName <> EmptyString)
-- END Para =colunas aplicáveis, insira o ID para a descrição selecionada nas listas suspensas.

End Sub
Este comentário foi feito pelo moderador no site
Nada mais frustrante do que digitar uma pergunta detalhada apenas para surpreendê-la. Se você digitar o código errado de 6 dígitos para verificar seu humano, ele apagará a mensagem postada. Pode querer consertar isso. Agora meu comentário é o seguinte: tentei fazer exatamente a mesma coisa que você mostrou no vídeo e instruções escritas e tudo que recebo é quando seleciono um nome na lista é o nome e não o número. Além disso, como isso está funcionando, já que a validação de dados deve limitar as opções apenas ao que está na lista. Como isso está enganando o sistema? No passado, sempre tive que atribuir o código vba ao botão ou a um atalho, como esse código é ativado? Como você testa para ter certeza de que está funcionando?
Este comentário foi feito pelo moderador no site
Como a fórmula funciona quando você deseja adicionar os dados em uma planilha separada na pasta de trabalho? Eu quero esconder os dados.
Este comentário foi feito pelo moderador no site
Mude aqui mano!
selectedNum = Application.VLookup(selectedNa, Worksheets("YourSheetName").Range("dropdown"), 2, False)
Este comentário foi feito pelo moderador no site
"YourSheetName" faz referência à planilha que contém o intervalo de dados ou a planilha onde quero usar a lista dropdwon?
Este comentário foi feito pelo moderador no site
Como a fórmula funciona quando você deseja listar os dados em uma planilha/guia separada na pasta de trabalho?
Este comentário foi feito pelo moderador no site
isso não funciona nas versões atuais do excel - desatualizado. A validação de dados, em seguida, a lista não aparece mais no vba, pois um objeto do Excel já tentou várias vezes e não aparece.
Este comentário foi feito pelo moderador no site
Neste exemplo, e se você quiser ver um valor em cada uma das células em 5, mas colocar o valor na célula adjacente em 6
Este comentário foi feito pelo moderador no site
Como o código precisaria ser alterado se eu quisesse criar uma referência/link no E1 para a fonte da lista suspensa com base no valor selecionado?
O benefício seria que, no caso de uma mudança na fonte suspensa (por exemplo, "Henrik" => "Hendrik", a mudança seria automaticamente refletida em E1.
Este comentário foi feito pelo moderador no site
Alguém sabe como fazer isso funcionar no Google Sheets?
Este comentário foi feito pelo moderador no site
eu quero selecionar várias opções da lista suspensa.
resultado assim: AA1001,BB1002
é possível?
Este comentário foi feito pelo moderador no site
Você encontrou uma solução?
Este comentário foi feito pelo moderador no site
Se os dados da lista estão em outro lugar, qual seria o código? Gracias.
Este comentário foi feito pelo moderador no site
como buscar un valor hacia la izquierda
Este comentário foi feito pelo moderador no site
Private Sub Worksheet_Change (ByVal Target As Range)
selecionadoNa = Alvo.Valor
Se Target.Column = 5 Then

Sheets("Nombre de la hoja en donde esta la lista").Activate
selectedNum = Application.VLookup (selectedNa, ActiveSheet.Range ("dropdown"), 2, False)
Sheets("Nombre de la hoja en donde this trabajando").Activate
If Not IsError(selectedNum) Então
Target.Value = selectNum
Se acabar
Se acabar
End Sub
Este comentário foi feito pelo moderador no site
Alguém sabe como pesquisar o valor da direita para a esquerda
Este comentário foi feito pelo moderador no site
Preciso usar o mesmo dropdown em mais de uma coluna, qual seria o código?
Este comentário foi feito pelo moderador no site
Oi!
Isso é realmente útil! Obrigada!
Estou rodando na situação em que o celular não atualiza automaticamente ou ao usar a função de atualização. Eu tenho que clicar em outra célula e depois clicar novamente na célula em trabalho para que ela exiba o valor.
Atualmente estou trabalhando no Office Standard 2019. Alguém sabe se esse problema está relacionado à versão do Excel que estou usando?
Este comentário foi feito pelo moderador no site
Oi,
O código estava funcionando bem se estivéssemos definindo a lista e criando a lista suspensa na mesma planilha.
Mas como podemos conseguir definir a lista de valores e códigos em uma planilha e a lista suspensa criada em outra planilha?
Este mesmo código não está funcionando, pois está mostrando um erro nesta linha ("selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)").
Além disso, tenho um requisito como, se eu tiver várias listas definidas em uma planilha com ID e nomes e várias listas suspensas em outra planilha, onde um valor suspenso depende do valor selecionado em outra lista suspensa.

Espero que tenha entendido minha dúvida.

Por favor me ajude a resolver este problema.
Este comentário foi feito pelo moderador no site
Isso me ajudou imensamente, obrigado. Como minha tabela estava em uma planilha diferente da minha caixa de listagem, adicionei algumas linhas de código para fazer isso e também para evitar que a tela piscasse.

Application.ScreenUpdating = False
Sheets("SheetWithTableOnIt").Ativar

Sheets("SheetWithDropDownListOnIt").Ativar
Application.ScreenUpdating = True
Este comentário foi feito pelo moderador no site
onde exatamente você adicionou esses códigos?
Este comentário foi feito pelo moderador no site
Recebo um erro de compilação: Erro de sintaxe na linha "If Trarget.Column = 6 Then" quando tento usar o código? Alguma ideia do porquê?
Este comentário foi feito pelo moderador no site
Ele está funcionando, mas quando você sai de um arquivo e abre novamente ele não está funcionando... ele não pode ser salvo como .xls apenas como .xlsm existe alguma solução para isso? Obrigado
Este comentário foi feito pelo moderador no site
Olá, Marko, Depois de copiar e colar o código em sua pasta de trabalho, ao salvar o arquivo, você deve salvá-lo como um formato de pasta de trabalho habilitada para macro do Excel, por favor, tente, obrigado!
Não há comentários postados aqui ainda
carregar mais
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL