У меня зазвонил телефон. На другом конце трубки оказался молодой человек, который искал рецепт для каскадных списков следующего вида: Регион - Должность - Имя сотрудника. Ясно, что должностная сетка в регионах либо полностью, либо весьма сильно пересекается. И для таких случаев мой предыдущий рецепт не годится, так как строки выпадающего списка текущего уровня определяются не только лишь предыдущим уровнем, а ДВУМЯ предыдущими уровнями (важен и Регион, и Должность, дающие комбинацию, определяющую список имён конкретных сотрудников).

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

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

Файл примера

Скачать

Особенности

1. Для каждого столбца создан свой именованный диапазон для выпадающего списка. У нас 3 столбца, поэтому соответственно: столбец Регион - именованный диапазон SelectRegion, Позиция - ИД SelectPosition, Сотрудник - ИД SelectPerson.

   

2. Для ячеек через Данные - Проверка данных - Проверка данных... прописаны соответствующие ИД в качестве источников строк выпадающих списков. Вот пример для столбца Регион:

   

3. Как и в предыдущей статье справочные данные оформлены в виде умной таблицы tblLists

   

4. Рабочая таблица называется tblEntry, но тут она может быть и обычным диапазоном без изменений в формулах, поэтому решайте сами, что вам больше подходит.

Формулы

Формулы привычно сосредоточены в именованных диапазонах.

ИД SelectRegion (ячейка A2)

Осуществляется проверка, что в столбце Сотрудник текущей строки нет выбранного значения (если есть, то список не формируется для обеспечения целостности данных). Если ячейка пуста, то через формулу СМЕЩ формируется диапазон для выпадающего списка на основе столбца умной таблицы tblLists[Регион]. Напоминаю, что мы не можем просто указать tblLists[Регион], так как не хотим видеть пустые строки в выпадающем списке.

ИД SelectPosition (ячейка B2)

Всё полностью аналогично.

ИД SelectPerson (ячейка C2)

Данная формула проверяет через сочетание формул И() и НЕ(), что столбцы Регион и Позиция заполнены (если они не заполнены, то мы не сможем определить нужный столбец). Поскольку столбец мы определяем динамически, через конкатенацию строк значений ячеек Регион и Позиция (между ними знак подчёркивания), то результат этой конкатенации мы не можем напрямую указать между tblLists[ и ], поэтому придётся воспользоваться функцией ДВССЫЛ (INDIRECT). На рисунке вы видите, что за исключением двух идентичных обведенных фрагментов, которые означают динамический диапазон через ДВССЫЛ, вся остальная конструкция соответствует тому, что уже обсуждалось.

Надеюсь, это было полезно!

Читайте также:

• Создание выпадающего списка в ячейке

• Каскадные выпадающие списки

• Универсальные динамические каскадные выпадающие списки без VBA!

• Динамические каскадные списки на основе сводных таблиц

• Выпадающий список с контекстным поиском