Каскадирование зависимых раскрывающихся списков — это забавный и распространенный трюк, позволяющий второму раскрывающемуся списку изменять свои параметры на основе выбора, сделанного в первом раскрывающемся списке. Обычно это достигается с помощью функции ДВССЫЛ ().

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

ПРОБЛЕМА: * Эти две «уловки» не работают вместе. Если вы используете формулы динамического именованного диапазона для создания списка команд, а затем используете этот именованный диапазон в качестве источника списка DV в ячейке A1, вы не можете использовать метод ДВССЫЛ (A1) для выбора зависимого именованного диапазона с таким же именем. как выделенный текст в A1.

РЕШЕНИЕ: * Обходной путь состоит в том, чтобы вообще не создавать формулы динамического именованного диапазона. Вместо этого вы перемещаете все динамические действия в формулу «Источник» зависимой проверки данных.

НАСТРОЙКА:

  1. На листе списков все ваши списки будут располагаться бок о бок в столбцах, настройте их следующим образом:

1

{пусто} 2. Мы создаем именованный диапазон под названием AnchorCell, щелкнув A1 и введя это имя в поле имени, как показано выше.

Это позволяет нам позже создать формулу проверки данных, которая будет работать в Excel 2003.

  1. Мы создаем динамический именованный диапазон, называемый командами, нажав CTRL-F3 и определив имя с помощью формулы RefersTo:

= OFFSET (Составы! $ A $ 1,,, 1, COUNTA (Составы! $ 1: $ 1))

2

Это позволяет вам добавлять новые столбцы (команды) в любое время, не меняя ничего другого, все это будет продолжать работать и включать ваши новые команды.

ПРИМЕЧАНИЕ. Нет пустых столбцов, это справочный лист, держите его в порядке.

{пусто} 4. Далее, ничего особенного, мы используем именованные команды rangeTeams в качестве источника списка для нашего столбца. Проверка первичных данных на листе Select *:

3

После применения он предоставляет список команд из строки 1 нашего листа списков:

4

{пусто} 5. А вот и волшебство. Формула списка проверки данных в B2 выполняет всю тяжелую работу, используя функции СМЕЩЕНИЕ () и ПОИСКПОЗ, чтобы найти команду, выбранную в столбце A в строке 1 листа Ростеров *, а затем создать раскрывающийся список только из элементов в этом столбце. В B2 формула DV будет выглядеть так:

OFFSET (AnchorCell, 1, MATCH ($ A2, Teams, 0) -1, COUNTA (OFFSET (AnchorCell,, MATCH ($ A2, Teams, 0) -1, 50, 1)) — 1, 1) Вы должны потратьте некоторое время на чтение файлов справки по смещению, чтобы параметры имели для вас смысл:

= СМЕЩЕНИЕ (ссылка, строки, столбцы, [высота], [ширина])

{пусто} 6. После применения вторичный список создается на основе выбора, сделанного в ячейке столбца A:

5