Фильтрация выпадающего списка на основе выбора в другом выпадающем списке

Хотите ли вы иметь два вспомогательных столбца (не зависящих от количества менеджеров)?

(Это решение было протестировано в Excel 2013).

Я предположу, что у вас есть 16 строк данных, в A2:B17. Соответственно скорректируйте литералы в моем ответе. Предполагаю, что вы используете столбцы G и H в качестве вспомогательных столбцов.

Введите:

• 0 в G1,
• =MATCH($E$1,INDEX($A$2:$A$17,G1+1):$A$17,0)+G1 в G2,
• =IFERROR(INDEX($B$2:$B$17,G2), "*Zaphod*") в H2, и
• = "H2:H" & MATCH("*Zaphod*",H2:H17,0) в H1.

(Замените *Zaphod* любой строкой, которая никогда не появится в ваших данных.) Выделите G2:H2 и перетащите/заполните вниз достаточно глубоко , чтобы содержать список сотрудников для любого менеджера. Затем установите проверку для E2 на список, =INDIRECT(H1).

Объяснение:

• G2, G3, G4 и т.д. будут содержать номера индексов в пределах A2:A17 от имени менеджера в E1. (Это будет номер строки минус один).
• INDEX($A$2:$A$17,G1+1) идентифицирует клетку ниже предыдущего совпадения. Например, для данных в вопросе,
  • В строке 2 это указывает на A2 (поскольку G1 равен нулю).
  • В строке 3 это указывает на A6 (так как A5 содержит "Гари"), и
  • В строке 4 это указывает на A7 (так как A6 содержит "Gary").
• INDEX($A$2:$A$17,G1+1):$A$17 идентифицирует остальную часть столбца A (ниже предыдущего совпадения).
• =MATCH($E$1,(the above),0) находит первое вхождение E1 в этом диапазоне; т.е. следующее вхождение. Это будет код ошибки #N/A, если следующего вхождения нет.
• Добавьте G1, чтобы преобразовать его обратно в индекс относительно A2.
• INDEX($B$2:$B$17,G2) получает имя сотрудника соответствующее n-му вхождению имени менеджера в E1. Если нет n-го вхождения, ошибка #N/A будет передана сюда.
• IFERROR позволяет нам поместить фиктивное значение после реальных значений.
• MATCH("*Zaphod*",H2:H17,0) возвращает номер индекса в пределах H2:H17 фиктивного значения. Это будет номер строки минус один, и, таким образом, это номер строки последнего реального значения.
• Прибавьте вышеуказанное к H2:H, чтобы получить диапазон допустимых значений.

Я предполагаю, что вы знаете, как сделать проверку для имени менеджера E1. Если нет, смотрите Динамический список и подсчет экземпляров.

Рекомендую посмотреть эти видео для лучшего погружения в вопрос:

Прикрепленное видео 1 - Связанные выпадающие списки в Excel

Прикрепленное видео 2 - Взаимные выпадающие списки в Адало. Adalo Dropdowns Select & Filter