Список проверки данных Excel с использованием формулы с фильтрацией

У меня есть следующая книга:

Рабочий лист Аккаунты:

Accounts worksheet

Рабочий лист Сообщения:

Posts worksheet

Я хотел бы знать, можно ли определить проверку данных типа List для всего столбца B в листе Сообщения с помощью формулы, чтобы всплывающее окно Data Validation отображалось только Учетная запись Идентификаторы из Аккаунтов, столбец Веб-сайт соответствует столбцу Веб-сайт выбранной строки в Сообщения strong > и которые имеют значение Активен в столбце Статус?

В словах SQL-ish или LINQ-ish:

SELECT Id FROM Accounts WHERE Website = @SelectedPostRow.Website И Status = Active

Знаки на втором изображении показывают, какие значения должны отображаться в раскрывающемся списке.

Ответы

Ответ 1

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

В столбце A вы можете указать число, которое может быть оценено. Я получил его с (ячейкой A2):

= ЕСЛИ (С2 = $J $2,1,0) * ЕСЛИ (Е2 = "Активный", 1,0) * СТРОКА()

В столбце B ранга число и исключить нежелательные строки (B2):

= ЕСЛИ (А2 = 0,0, RANK (А2, А: А))

Затем вы можете VLOOKUP в столбце H, используя перечисление в столбце G (введен вручную). Формула для H1:

= ЕСЛИОШИБКА (ВПР (G2, $B $2: $D $9,3, FALSE), "")

Теперь вы можете установить свою проверку на основе столбца H

screenshot

PS: в формулах могут быть небольшие ошибки, поскольку я перевел их с итальянского языка, и я не могу проверить их на английском языке.

Ответ 2

Да, вы можете это сделать, но для этого требуется некоторая поддержка.

Сначала для каждого из параметров вашего сайта вам нужно создать именованный диапазон для параметров, которые будут в раскрывающемся списке, который вы хотите создать.

Чтобы сделать это, просто выделите список ячеек и щелкните правой кнопкой мыши, выберите "Имя диапазона"

Затем вам нужно создать список поиска для имен вашего веб-сайта для названных диапазонов возможностей

Затем в вашем источнике проверки данных используйте forumla, как это:

=indirect(vlookup(a1,$i$8:$j$13,2,false))

тогда whala, раскрывающийся список изменяется в зависимости от значения веб-сайта.

Теперь, если вам также нужно автоматизировать именованные биты диапазона, вы можете изменить их, чтобы охватить целые столбцы, а затем использовать сводную таблицу, чтобы вытащить данные. Просто нужно было бы независимый стержень для каждого варианта веб-сайта.

Каждый раз, когда вы извлекаете свежие данные, вам нужно обновить опорные точки, но он будет функционировать.