Предположим, у вас в таблице есть проекты и подпроекты, и вы хотите сделать выпадающий список, в котором подпроекты показываются только для выбранного проекта. Показываю, как легко и быстро сделать зависимые «выпадайки».
Готовим списки
Создадим лист "Списки", в котором укажем проекты и подпроекты.
Проекты перечислим в строке 2. Подпроекты – в стоблик под каждым проектом. Строку 1 оставим для служебных формул, которые будут указывать диапазон, в котором перечислены подпроекты.
Формула в строке 1 (формула вводится в A1 и протягивается по всей строке, где есть проекты):
=АДРЕС(3; СТОЛБЕЦ())&":"&АДРЕС(СЧЁТЗ(A2:A1048576)+1, СТОЛБЕЦ())
=ADDRESS(3,COLUMN())&":"&ADDRESS(COUNTA(A2:A1048576)+1,COLUMN())
Делаем «выпадайки»
Если вы еще не делали выпадающие списки, рекомендую сначала ознакомиться со статьей https://zen.yandex.ru/media/excel/kak-sdelat-vypadaiuscii-spisok-v-excel-chast-1-prostaia-5f2bd9839bd10957f03c9d92
Создаем новый лист и делаем табличку, где будут выпадайки. Подсказка: если данные оформить таблицей (Создать таблицу), то при добавлении новых строк к ним будут применены те же правила, что и во всем столбце, включая правила валидации данных. Настраиваем валидацию данных для выпадающего списка в колонке «Проект»:
В колонке «Подпроект» нужно будет указывать список динамически. Продумаем, как мы это будем делать:
- С помощью ПОИСКПОЗ (MATCH) определяем какой по порядку выбран проект в ячейке слева
- С помощью СМЕЩ (OFFSET) получаем описание диапазона из строки 1 листа Списки
- С помощью функции ДВССЫЛ получаем диапазон.
Записываем формулу в окно валидации данных для ячейки B2:
=ДВССЫЛ("Списки!"&СМЕЩ(Списки!$A$1,,ПОИСКПОЗ(A2,Списки!$A$2:$D$2,0)-1))
=INDIRECT("Списки!"&OFFSET(Списки!$A$1,,MATCH(A2,Списки!$A$2:$D$2,0)-1))
Все готово!
Выкладываю файл с примером: https://disk.yandex.ru/i/Tqt9z9ZaqLZEyw