Excel: обновить значение в именованном диапазоне

Question

У меня есть несколько ячеек в документе Excel, заполненных из именованного диапазона (data/validation/list source = MyNamedRange):

Здесь диапазон A1:A3 называется Foobar

B5:B6 проходит проверку данных с источником, установленным на «Foobar»

Я хотел бы иметь возможность обновлять содержимое ячейки A2, от Bar до Quux, и видеть, как автоматически содержимое ячейки B5 обновляется до Quux, поскольку его источник был изменен.

Это может быть выполнено с помощью макроса, но я не знаю, как это кодировать.

Любые намеки, пожалуйста?

Answer 1

Это кажется опасным, но я не вижу никаких проблем с этим. В основном, если вы что-то меняете в Foobar, он ищет каждую ячейку на листе с проверкой данных. Если DV указывает на Foobar, а значение отсутствует в списке, то это должно быть значение, которое было изменено. Это сработало с моим ограниченным тестированием. Дайте мне знать, если вы видите какие-либо недостатки.

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

Обратите внимание, что это относится к модулю рабочего листа, а не к стандартному модулю. Как всегда, протестируйте код на копии вашей рабочей книги.

Answer 2

Для этого вам нужно будет использовать VBA или иметь дополнительную ячейку вычисления для каждого из B5:B6, которая обнаружит, что значение ячейки больше не содержится в указанном диапазоне, и пометит это.

Я имел обыкновение поддерживать огромную рабочую книгу, в которой были тысячи таких зависимостей, это был настоящий кошмар для отладки.

Эта ссылка содержит больше способов обойти одностороннюю природу связи между диапазоном источника проверки и целевой ячейкой.

Answer 3

Теперь вы сохраняете значение A2 в B6. Но вам нужно хранить ссылку на A2. Затем B6 будет обновляться автоматически. Я думаю "= A2" (без кавычек) как значение поля должно сделать это.