Привет. Недавно я написал о зависимых выпадающих списках в Эксель. Там эта задача решается достаточно просто с помощью функции ДВССЫЛ (INDIRECT) в валидации данных. К сожалению, в гугл-таблицах нельзя использовать формулу при провреке данных, можно указать только диапазон или список значений (если идет речь о значениях из списка).
Все статьи про зависимые списки в гугл-таблицах, которые я видел, предлагают только один подход: скрипт, который подвязывается на редактирование (onEdit(e)), который определяет в зависимый список и назначает его для валидации данных в соседней ячейке.
Мне этот способ не нравится по нескольким причинам:
- слабо управляемый: если нужно что-то поменять (да хотя бы ячейку/ячейки, где будет выпадайка или откуда берутся списки), нужно лезть в скрипт.
- медленный: после выбора элемента списка зависимый список обновляется не мгновенно, а через 2-3 секунды.
- проблема при работе с несколькими пользователями: чтобы работало у всех, все должны быть авторизованы и принять разрешения скрипта.
- высокий порог вхождения: не все умеют писать скрипты, даже если большая часть кода уже написана, и нужно только исправить несколько строк или переменных.
Решение для гугл-таблиц без скрипта
Ноу-хау моего метода в создании динамических списков для каждой зависимой строки.
Создадим лист "Данные", в котором перечислем все проекты:
Создадим лист, на котором мы будем делать динамические списки, назовем его "Рабочий лист". Сначала сделаем основной список. В ячейке A1 разместим формулу:
=transpose(UNIQUE('Данные'!A:A))
Эта формула выбирает все уникальные названия проектов из колонки А листа с данными и размещает их в строку.
Теперь создадим лист, на котором будут сами зависимые списки. Так его и назовем: "Зависимые списки"
Выбираем колонку A (начиная со второй строки) и в меню "Данные -> Настроить проверка данных" указываем диапазон списка: ='Служебный лист'!$1:$1
Обратите внимание, адрес задан абсолютно (со знаком $), т.е. не зависи от выбранной ячейки.
Возвращаемся на лист "Служебный лист" и готовим зависимые списки. В ячейку A2 вводим формулу (и протягиваем ее вниз):
=if('Зависимые списки'!A2="";"";transpose(query('Данные'!A:B;"SELECT B WHERE A='"&'Зависимые списки'!A2&"'")))
Эта формула выбирает подпроекты, если выбран проект, и размещает их в строку.
Наконец, возвращаемся на лист "Зависимые списки" и настраиваем списки для подпроектов. Выбираем колонку B (начиная со второй строки) и указываем следующий адрес для проверки данных:
='Служебный лист'!2:2
В данном случае адрес относительный, без знака "$", и означает, что для каждой следующей строки ссылка на строку в формуле тоже сдвигается.
Проверяем как работает:
Готово!
Ссылка на пример: https://docs.google.com/spreadsheets/d/1uj_HX-jtqA8WVoaVksjD1XioQ1PC8LKV4YNQOzv6ZCA/edit?usp=sharing