☰

Como faço para criar uma lista suspensa para uma coluna de valores em constante mudança no Excel 2010?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Como faço para criar uma lista suspensa para uma coluna de valores em constante mudança no Excel 2010?

Tenho uma planilha excel com uma coluna há anos, com cabeçalho:

Years
1993
1993
1994
1994
1994
...
2011
2011

Existem valores duplicados para os anos e linhas adicionais serão adicionadas ao longo do tempo.

Eu tenho outra célula que precisa mostrar uma lista suspensa de anos, mas apenas os anos únicos. Tentei usar o recurso de validação de dados no Excel 2011, mas há 2 problemas:

  1. Ele exibe os anos duplicados.
  2. Eu digo para usar a coluna inteira e inclui as células vazias na lista suspensa.

Como obtenho uma lista suspensa de anos que exibirá apenas valores exclusivos, enquanto atualiza automaticamente à medida que linhas adicionais são adicionadas?

Editar: um pouco mais de informação. A lista suspensa é usada em uma planilha separada para exibir dados calculados, como um formulário do Access. O usuário pode escolher um intervalo de anos e os dados serão atualizados de acordo. A planilha original é apenas uma lista de todos os dados.

Responder1

Para esse tipo de validação, eu uso VBA + um truque sujo:

Primeiro entrei no editor VBA com Alt+F11. Depois coloquei meu "Código de Validação de Lista Dinâmica" (tm) :) na respectiva planilha.

Private Sub Worksheet_SelectionChange(ByVal rTarget As Excel.Range)

On Error GoTo noVal

With rTarget.Validation
    .Modify xlValidateList, xlValidAlertStop, xlBetween, Excel.Evaluate(.ErrorTitle)
End With

noVal:

End Sub

Este código atualiza a lista de validação de células com a lista gerada pela fórmula inserida em Dados->Validação->Mensagem de Erro->Título. Dessa forma, cada célula com validação de lista pode ter sua própria fórmula.

Então, adiciono um módulo (Insert->Module) e coloco este código no novo módulo:

Function GenDynList(rRng As Range)

sRet = ""

For Each rCell In rRng
    If Not IsEmpty(rCell.Value) And InStr(sRet, rCell.Value) = 0 Then
        sRet = sRet & "," & rCell.Value
    End If
Next

GenDynList = Mid(sRet, 2)

End Function

Esta função retorna todas as células do intervalo sem espaços em branco ou repetições. Então, em cada célula com validação de lista, adiciono GenDynList(range) no título da mensagem de erro de validação de dados.

Responder2

Bagunçado. Não há uma maneira integrada de fazer isso que seja atualizada automaticamente. Seria mais simples simplesmente criar uma lista separada com todos os anos possíveis nos quais você poderia estar interessado, em vez de tentar limitá-la aos do seu conjunto de dados.

Responder3

Adicione uma tabela dinâmica a partir dos dados de outra planilha. Use Anos como linhas, o resto da tabela é irrelevante. Classifique e filtre as linhas conforme necessário (por exemplo, filtre explicitamente OUT "[em branco]"). Transforme as células onde os rótulos das linhas estão no destino de sua validação de dados.

Use um intervalo nomeado em expansão para sempre usar todos os novos rótulos: http://www.ozgrid.com/Excel/DynamicRanges.htm

Repita para outras colunas, como país, estado, etc. Se você basear todos os pivôs no mesmo intervalo de dados, todos serão atualizados juntos quando você atualizar qualquer um deles. Seu processo agora será: adicionar novos dados, atualizar pivôs, usar validação de dados atualizados.

Considerações adicionais: Usar uma tabela para os dados de origem facilitará a atualização das Tabelas Dinâmicas, pois elas sempre usarão a tabela inteira. Como alternativa, use um intervalo nomeado em expansão como fonte de dados para evitar problemas ao adicionar mais linhas.

Observe que você pode usar um intervalo de uma planilha diferente como fonte para validação de dados se fizer isso definindo um intervalo nomeado. Você não pode usar um intervalo normal referindo-se explicitamente a outra planilha.

informação relacionada