Как изменить значение в раскрывающемся списке Excel

Question

У меня есть столбец, который использует значения из предопределенного набора (раскрывающийся список), реализованный с помощью проверки данных. Теперь, если я изменю какое-либо значение в списке, раскрывающийся список (при его открытии) немедленно предложит это новое значение. Однако таблица не будет автоматически обновляться сама по себе, что означает, что некоторые значения в столбце будут недействительными, пока я не исправлю их вручную.

Я видел решения, основанные на макросах / VBA, но нет ли какого-нибудь умного способа непосредственно в интерфейсе Excel?

Answer 1

Как я уже упоминал в комментариях, единственный способ сделать это - использовать VBA.

Вот один из вариантов. Я добавил комментарии по всему коду. Это предполагает, что вы используете именованный диапазон для списка проверки с именем "Список" и что он находится на том же листе, что и проверяемые ячейки.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant


    Set isect = Application.Intersect(Target, ThisWorkbook.Names("List").RefersToRange)
    If Not isect Is Nothing Then
        ' Get previous value of this cell
        Application.EnableEvents = False
        With Target
            vNewValue = .Value
            Application.Undo
            vOldValue = .Value
            .Value = vNewValue
        End With

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                If .Validation.Type = 3 And .Validation.Formula1 = "=List" And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell
        Application.EnableEvents = True
    End If
End Sub

Вы также можете скачать пример электронной таблицы, которую я собрал, чтобы проверить это. (Содержит макросы!)

Answer 2

но нет ли какого-нибудь умного способа непосредственно в интерфейсе Excel?

Я думаю, что я знаю один - по крайней мере, кажется, чтобы удовлетворить все ваши запросы:

1. Вам необходимо установить динамически изменяемый именованный регион в качестве источника для проверки данных. Это может быть достигнуто с помощью функции OFFSET . Предполагая, что у вас есть список значений для раскрывающегося списка в столбце A Sheet1 (имя имеет значение только для формулы), ячейка A1 имеет заголовок, например, List of values , и значения помещаются, начиная с A2 и ниже, вы должны сделать следующее: Формулы ленты Formulas > Name Manager , создайте новый регион (назовем его « Items) и задайте его регион, НЕ указывающий на ячейки, а вместо этого использующий следующую формулу: =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1) .
2. Задайте правила проверки данных : вместо указания диапазона ячеек для List введите =Items - таким образом, ваш именованный регион будет использоваться в качестве источника элементов списка.
3. В результате вышеизложенного вы получите действительно динамический список, который отвечает всем вашим требованиям: вы можете свободно изменять / добавлять элементы в столбец A, и эти изменения будут немедленно отражены в раскрывающемся списке при следующем его использовании. В то же время старые значения останутся нетронутыми.

Я использую это решение около 2 лет в моей работе. Надеюсь, вы тоже найдете это полезным!

PS вот фактический пример файла: Dynamic Dropdown