Задача: сделать в ячейке D2 выпадающий список, чтобы пользователь мог выбирать имена из списка (столбец А). Если нужного имени нет в списке, то пользователь может ввести новое имя прямо в ячейку D2 – оно автоматически добавится к столбцу А и начнет отображаться в выпадающем списке в будущем.

Выпадающий список с автоматическим добавлением отсутствующих элементов

Шаг 1. Создаем именованный диапазон

Сначала создадим именованный диапазон, указывающий на заполненные именами ячейки в столбце А – сколько бы имен в списке ни находилось. Для этого жмем на вкладке Формулы (Formulas) кнопку Диспетчер имен (Name Manager) и затем Создать (New).

Вводим имя диапазона (допустим, People) и в строку Ссылка (Reference) вводим следующую формулу: =СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A$1:$A$24);1). в английской версии Excel это будет: =OFFSET(Лист1!$A$1;0;0;COUNTA(Лист1!$A$1:$A$24);1). Эта формула ссылается на все заполненные ячейки в столбце А, начиная с А1 и вниз до конца – до последнего имени.

Шаг 2. Создаем выпадающий список в ячейке

Выделяем ячейку D2 и жмем на вкладке Данные (Data) кнопку Проверка данных (Data Validation). Далее выбираем из выпадающего списка Тип данных (Allow) позицию Список (List) и вводим в строку Источник (Source) ссылку на созданный на шаге 1 именованный диапазон (не забудьте перед именем диапазона поставить знак равенства!):

Не забудьте перед именем диапазона поставить знак равенства!

Чтобы Excel позволил нам в будущем ввести в список и новые имена, снимем галочки на вкладках Сообщение для ввода (Input Message) и Сообщение об ошибке (Error Alert) и нажмем ОК. Теперь у нас есть выпадающий список в ячейке D2. Причем если, например, вручную дописать новое имя в столбце А, то оно автоматически появится в выпадающем списке в ячейке D2, поскольку имена берутся из динамического диапазона People, который автоматически отслеживает изменения в столбце А.

Шаг 3. Добавляем простой макрос

Щелкаем правой кнопкой мыши по ярлычку нашего листа и выбираем Исходный текст (View Source). Откроется модуль листа в редакторе Visual Basic, куда надо ввести такой код:

1
2
3
4
5
6
7
8
9
10
11
12
13

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
	If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$D$2" Then
    	If IsEmpty(Target) Then Exit Sub
        If WorksheetFunction.CountIf(Range("People"), Target) = 0 Then
        lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion)
        If lReply = vbYes Then
        	Range("People").Cells(Range("People").Rows.Count + 1, 1) = Target
        End If
    End If
    End If
End Sub

Если ваш выпадающий список находится не в ячейке D2 или вы назвали диапазон с именами не People, а как-то еще, то подправьте эти параметры в макросе на свои. Всё! Теперь при попытке ввести новое имя в ячейку D2 Excel будет спрашивать и при утвердительном ответе пользователя автоматически добавлять новое имя к списку в столбце А и в выпадающий список в ячейку D2.

Excel будет спрашивать и при утвердительном ответе пользователя автоматически добавлять новое имя