Уважаемые модераторы, не спешите удалять или игнорировать этот пост - он является НЕ тысячным клоном себе подобных, НО попыткой и просьбой о реализации ДЕЙСТВИТЕЛЬНО УНИВЕРСАЛЬНОГО, решающего БОЛЬШИНСТВО вопросов по данной популярной теме (кроме того сюда всегда можно будет отправить по этой теме).
Начну с формы ведения. Предлагаю в качестве платформы создания таких списков - "умные" таблицы, как учит Николай Павлов, т.к. они сразу решают множество проблем по работе с базой данных и являются универсальными практически для любых потребностей (сами растягиваются, сами формируют именованые диапазоны полей и т.д.). Наиболее близкие к желаемому примеры найдены здесь, за авторством ber$erk аж в начале 2013 года: и здесь (первый вариант очень узко заточен, а второй отказывается работать с большим количеством данных (хотя очень крутой и универсальный)...
Короче говоря, создавать 2х уровневые выпадающие списки мы умеем вот по этой теме , а сделать так, чтобы в каком-либо диапазоне НА ЛИСТЕ отсутствовали дубликаты можно, предварительно сделав сводную на основе необходимого списка и ссылаться уже на неё (автообновлять можно простым макросом) (найдено здесь )
Для удобства объяснения введу такие понятия для примера: 1 (первый) уровень списков - исходный начальный диапазон (овощи и фрукты); 2 уровень (помидоры и огрурцы - овощи, а апельсины и мандарины -фрукты; 3 уровень - соответственно дальше по уточнениям и т.д.)
С первой и ОСНОВНОЙ проблемой я столкнулся, когда искал способ удалять дубликаты ИЗ ФОРМИРУЕМОГО (СМЕЩ+ИНДЕКС+ПОИСКПОЗ) выпадающего списка, то есть НА ЛЕТУ (в то же время есть способ удаления дубликатов из списка НА ЛИСТЕ (но хотя бы НЕ формулой массива) здесь .
Вторая проблема возникает, когда внутри уровня частично или полностью пересекаются формируемые списки (например овощи-красные-помидоры и фрукты-красные-яблоки) - здесь признак 2 уровня дублируется для уровня выше (первого), поэтому вновь сформированный зависимый выпадающий список по критерию "красные" выдаст либо И помидоры, И яблоки (если сортировка будет произведена по 2 уровню), либо ТОЛЬКО "помидоры" (т.к. овощи первые по алфавиту), если будет проведена необходимая сортировка справа-налево.
Решением будет являться создание дополнительного столбца после каждого критерия, кроме первого (причём и в справочнике, и в листе выбора), в котором будут сцепляться все предыдущие критерии. Соответственно, выбор следующего уровня будет осуществляться по сцепке всех предыдущих.
Это, на мой взгляд, основные проблемы, которые возникают при формировании ЗНАЧИТЕЛЬНЫХ каскадных выпадающих списков. Решение вижу только макросами и UDF, т.к. набор и сложность формул и списков, чтобы ЭТО реализовать, отобьёт любое желание. Чтобы не требовалось сортировки, сводных (желательно даже обойтись без создания вручную формульных диапазонов). Как вариант файл-шаблон с таблицами и макросами (заполнил таблицы на нужное количество уровней и вперёд) Файл с примерами прикреплён. Слежу за темой...
Начну с формы ведения. Предлагаю в качестве платформы создания таких списков - "умные" таблицы, как учит Николай Павлов, т.к. они сразу решают множество проблем по работе с базой данных и являются универсальными практически для любых потребностей (сами растягиваются, сами формируют именованые диапазоны полей и т.д.). Наиболее близкие к желаемому примеры найдены здесь, за авторством ber$erk аж в начале 2013 года: и здесь (первый вариант очень узко заточен, а второй отказывается работать с большим количеством данных (хотя очень крутой и универсальный)...
Короче говоря, создавать 2х уровневые выпадающие списки мы умеем вот по этой теме , а сделать так, чтобы в каком-либо диапазоне НА ЛИСТЕ отсутствовали дубликаты можно, предварительно сделав сводную на основе необходимого списка и ссылаться уже на неё (автообновлять можно простым макросом) (найдено здесь )
Для удобства объяснения введу такие понятия для примера: 1 (первый) уровень списков - исходный начальный диапазон (овощи и фрукты); 2 уровень (помидоры и огрурцы - овощи, а апельсины и мандарины -фрукты; 3 уровень - соответственно дальше по уточнениям и т.д.)
С первой и ОСНОВНОЙ проблемой я столкнулся, когда искал способ удалять дубликаты ИЗ ФОРМИРУЕМОГО (СМЕЩ+ИНДЕКС+ПОИСКПОЗ) выпадающего списка, то есть НА ЛЕТУ (в то же время есть способ удаления дубликатов из списка НА ЛИСТЕ (но хотя бы НЕ формулой массива) здесь .
Вторая проблема возникает, когда внутри уровня частично или полностью пересекаются формируемые списки (например овощи-красные-помидоры и фрукты-красные-яблоки) - здесь признак 2 уровня дублируется для уровня выше (первого), поэтому вновь сформированный зависимый выпадающий список по критерию "красные" выдаст либо И помидоры, И яблоки (если сортировка будет произведена по 2 уровню), либо ТОЛЬКО "помидоры" (т.к. овощи первые по алфавиту), если будет проведена необходимая сортировка справа-налево.
Решением будет являться создание дополнительного столбца после каждого критерия, кроме первого (причём и в справочнике, и в листе выбора), в котором будут сцепляться все предыдущие критерии. Соответственно, выбор следующего уровня будет осуществляться по сцепке всех предыдущих.
Это, на мой взгляд, основные проблемы, которые возникают при формировании ЗНАЧИТЕЛЬНЫХ каскадных выпадающих списков. Решение вижу только макросами и UDF, т.к. набор и сложность формул и списков, чтобы ЭТО реализовать, отобьёт любое желание. Чтобы не требовалось сортировки, сводных (желательно даже обойтись без создания вручную формульных диапазонов). Как вариант файл-шаблон с таблицами и макросами (заполнил таблицы на нужное количество уровней и вперёд) Файл с примерами прикреплён. Слежу за темой...
Изменено: - 30.03.2016 20:30:37
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел)
К сожалению, как и в 90% всех подобных случаев, изменить под универсальные нужды самостоятельно не смогу 
Браво, Юрий, браво!!! 
А то,что столбец-сцепка не задействован это вообще супер (то есть код ищет сам не по ОДНОМУ, а по ВСЕМ предыдущим). Нет, правда - я прям поражён. И это полчаса... Однако, VBA определённо стоит изучать 
)