Member 10696161 Ответов: 2

Как сделать динамический выпадающий список?

Я пытаюсь создать динамический выпадающий список в MS Excel O365. Я сделал 2 таблицы на 2 разных листах. Ниже приведена таблица в листе "рабочие" :

https://i.stack.imgur.com/khSb0.png[^]

И в листе "Order_status" :

https://i.stack.imgur.com/g5H4I.png[^]

Что касается этих таблиц, то я вставил эти данные вручную. Теперь я хотел бы создать динамический выпадающий список, который в "ID_Worker" я получаю данные из листа "Workers" и когда я выбираю ID_WORKER в листе "Order_status" :

а) не только отображает ID_WORKER, FNAME, LNAME (например, 1 Paul Boy)

б) автоматически записывает данные в столбцы ID_WORKER, FNAME и LNAME.

Что я уже пробовал:

Я сделал динамический выпадающий список, в котором он выбирает только ID_Workers из листа "рабочие". И эту исходную формулу я написал Вот так:

=Workers!$A$2:$A$1048576


Я объясню, что я хотел бы сделать (на примере):

1) в разделе "статус заказа" есть 3 столбца: "ID_WORKER", "FNAME", "LNAME", но данные в этом листе пусты.

https://i.stack.imgur.com/JIIHe.png[^]

2) Когда я нажимаю на ячейку в столбце "ID_WORKER", то она показывает примерно следующее: "1 Paul Boy".

3) Затем я выбираю значение в этом столбце позже в столбце "FNAME" и "LNAME" должно быть записано автоматически: в "FNAME" - "Paul", а в "LNAME" - "Boy".

Я искал какие-то решения, но до сих пор понятия не имею, что делать? Есть идеи? Thx за любую помощь! :)

CHill60

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

2 Ответов

Рейтинг:
9

Member 10696161

Ладно я поступил по другому:

1. Я создал таблицу с рабочими и назовите его "tblWorkers".
2. Я выбрал правильный диапазон для импорта выпадающий список с ID_WORKERS, тот зашел во вкладку Данные, инструментальные данные, проверка данных, позволяют:список & источник: 

=INDIRECT("tblWorkers[ID_WORKERS]")


3. Есть формулы для

а) имени:
=IF(E2<>"",IFERROR(VLOOKUP(E2,tblWorkers[#All],COLUMN(tblWorkers[FNAME]),FALSE),"Not Matched"),"")


б) lname в:
=IF(E2<>"",IFERROR(VLOOKUP(E2,tblWorkers[#All],COLUMN(tblWorkers[LNAME]),FALSE),"Not Matched"),"")

Рейтинг:
1

CHill60

Это просто достигается с помощью VLOOKUP.
Например в ячейку B2 вашего пустого листа поместите формулу 

=VLOOKUP($A2,Workers!$A:$C, 2, 0)
а в ячейку С2 вашего пустого листа положите формулу 
=VLOOKUP($A2,Workers!$A:$C, 3, 0)
Обратите внимание, что его лучше (быстрее) использовать Индекс и ПОИСКПОЗ[^] если у вас есть много столбцов, смотрящих вверх на одну и ту же строку.

Все, что вам нужно сделать, это перетащить формулы вниз по странице.

Кроме того, вы можете настроить формулы или очистить содержимое, если число будет удалено, поместив некоторый VBA за лист.

Щелкните правой кнопкой мыши на листе, который вы хотите заполнить, и выберите пункт "Просмотр кода".
Это приведет вас к пустому кодовому модулю для листа. Вставьте следующий код
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
        If Len(Target.Value2) > 0 Then
            Target.Offset(0, 1).Formula = "=VLOOKUP(" & Target.Address & ",Workers!A:C, 2, 0)"
            Target.Offset(0, 2).Formula = "=VLOOKUP(" & Target.Address & ",Workers!A:C, 3, 0)"
        Else
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 2).ClearContents
        End If
    End If
End Sub
Теперь, когда вы выбираете элемент из выпадающего списка в столбце а, столбцы В и С будут заполнены именем и фамилией из листа "рабочие". Если вы удалите содержимое ячейки в столбце А, то формулы будут удалены из Столбцов В и С.

Будьте осторожны - этот код не обрабатывает диапазон ячеек, изменяемых одновременно, например, если вы вставляете набор значений в столбец A

Maciej Los

5ed!