Excel: atualiza um valor em um intervalo nomeado

Question

Eu tenho algumas células em um documento do Excel preenchido de um intervalo nomeado (data / validation / list source = MyNamedRange):

Aqui, o intervalo A1: A3 é denominado Foobar

B5: B6 está sob validação de dados com sua fonte configurada como 'Foobar'

Eu gostaria de poder atualizar o conteúdo A2 da célula, do Bar para o Quux, e ver automaticamente o conteúdo da célula B5 atualizado para o Quux, já que sua fonte foi alterada.

Ele pode ser executado por meio de uma macro, mas não sei como codificar isso.

Alguma sugestão, por favor?

    

Answer 1

Isso parece perigoso, mas não consigo ver nenhum problema com isso. Basicamente, se você alterar alguma coisa no Foobar, ele procurará todas as células da planilha que tiverem validação de dados. Se o DV aponta para Foobar e o valor não está na lista, então deve ter sido o valor que foi alterado. Funcionou com meus testes limitados. Deixe-me saber se você vê alguma falha.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rCell As Range
    Dim rFound As Range

    'Only run this when a cell in Foobar is changed
    If Not Intersect(Target, Me.Range("Foobar")) Is Nothing Then

        'Go through every data validation cell in the sheet
        For Each rCell In Me.Cells.SpecialCells(xlCellTypeAllValidation).Cells

            'if the DV in the cell points to foobar
            If rCell.Validation.Formula1 = "=Foobar" Then

                'See if the cell's value is in the Foobar list
                Set rFound = Me.Range("Foobar").Find(rCell.Value, , xlValues, xlWhole)

                'If it's not in the list, it must be the one that
                'changed, so changed it
                If rFound Is Nothing Then
                    Application.EnableEvents = False
                        rCell.Value = Target.Value
                    Application.EnableEvents = True
                End If
            End If
        Next rCell
    End If

End Sub

Observe que isso ocorre no módulo da planilha, não em um módulo padrão. Como sempre, teste o código em uma cópia de sua pasta de trabalho.

    

Answer 2

Você precisaria usar o VBA para fazer isso ou ter uma célula de cálculo extra para cada B5: B6 que detectaria que o valor da célula não está mais contido no intervalo nomeado e sinaliza isso.

Eu costumava ter que manter uma pasta de trabalho enorme que tinha milhares de dependências, era um verdadeiro pesadelo para depurar.

Este link tem mais informações sobre como contornar natureza unidirecional do link entre o intervalo da fonte de validação e a célula de destino.

    

Answer 3

O que você está fazendo agora é armazenar o valor de A2 em B6. Mas você precisa armazenar uma referência para A2. Então, B6 atualizaria automaticamente. Eu acho "= A2" (sem as aspas) como o valor do campo deve fazer isso.