Como eu crio uma lista suspensa para uma coluna de valores em constante mudança no Excel 2010?

Question

Eu tenho uma planilha do Excel com uma coluna há anos, com um 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 para os anos, mas apenas os anos únicos. Eu tentei usar o recurso de validação de dados no Excel 2011, mas tem 2 problemas:

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

Como faço para obter uma lista suspensa de anos que exibirá apenas valores exclusivos, enquanto atualizo automaticamente à medida que linhas adicionais são adicionadas?

Editar : um pouco mais de informação. A lista suspensa é usada em uma folha 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 folha original é apenas uma lista de todos os dados.

    

Answer 1

Para este tipo de validações, eu uso o VBA + um truque sujo:

Primeiro, digite o editor do VBA com Alt + F11 Então, eu 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 da célula com a lista gerada pela fórmula inserida em Dados- > Validação- > Mensagem de erro- > Título. Desta forma, cada célula com validação de lista pode ter sua própria fórmula.

Em seguida, adiciono um módulo (Insert- > Module) e, em seguida, insiro 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 no intervalo sem espaços em branco ou repetições. Então, em cada célula com validação de lista, eu adiciono GenDynList (range) na Título da mensagem de erro da validação de dados.

    

Answer 2

Sujo. Não há nenhuma maneira interna para fazer isso que atualiza automaticamente. Seria mais simples criar uma lista separada com todos os anos possíveis em que você poderia se interessar, em vez de tentar limitá-la àqueles em seu conjunto de dados.

    

Answer 3

Adicione uma Tabela Dinâmica dos dados em outra planilha. Use anos como linhas, o resto da tabela é irrelevante. Classifique e filtre as linhas conforme necessário (por exemplo, explique explicitamente OUT "[blank]"). Faça as células em que os rótulos de linha estão no destino de sua validação de dados.

Use um intervalo nomeado de expansão para usar sempre todos os novos marcadores: link

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

Outras considerações: O uso de 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 de 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 uma 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.