Excel: создать список проверки из отфильтрованных значений в столбце

Question

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

Это структурированные таблицы:

Таблица1 (со статическими значениями для "фильтрации")

id      Items
2       ItemAA
2       ItemAAA
3       ItemZZ
5       ItemXY
5       ItemXYZ
2       ItemCC

Таблица 2 (с раскрывающимся списком «Список проверки»)

idFromTable1  RelatedOptionFromFilteredDropdownList
2             ItemAA  (dropdown lists: ItemAA, ItemAAA, ItemCC)
5             ItemXY  (dropdown lists: ItemXY, ItemXYZ)
2             ItemCC  (dropdown lists: ItemAA, ItemAAA, ItemCC)
3             ItemZZ  (dropdown lists: ItemZZ)

Изменить: используя формулы, а не VBA

Edit2: и ID и элементы будут рассчитаны на сотни или тысячи.

Спасибо

Answer 1

Создать именованный список

1. Сначала отсортируйте ваш Table1 по ID
2. Создайте именованный диапазон с именем listlookup (или любым другим ).
3. Поместите эту формулу в listlookup

=OFFSET(INDIRECT(CELL("address", INDEX($B$2:$B$5, MATCH($C1, $A$2:$A$5, 0)))), 0, 0, COUNTIF($A$2:$A$5, $C1), 1)

 ID | Items   | lookup_value_here | dropdown_list_here
 1  | ItemXY
 1  | ItemXYZ
 2  | ItemZ
 2  | ItemZZ

4. Создайте выпадающий список и отошлите список к listlookup

объяснение

INDEX($B$2:$B$5, MATCH($C1, $A$2:$A$5, 0)) находит первую ячейку, которая имеет lookup_value

CELL( ... ) возвращает строку, ссылающуюся на ячейку (например: $B$2 для lookup_value 1)

INDIRECT( ... ) преобразует строковую ссылку в ссылку на ячейку

OFFSET( ... , 0, 0, ... , 1) возвращает диапазон из поиска (например: B2:B3 для lookup_value 1)

COUNTIF( ... ) возвращает количество данных, соответствующих lookup_value

---

Это весело, потому что я сталкивался с этой проблемой в прошлом и не могу ее решить.