Это обратная сторона вашего запроса — я оставлю читателю в качестве упражнения, как можно получить обратное.
По сути, вы хотите проверить, находится ли значение какой-либо ячейки в списке допустимых значений. Если бы вы вычисляли это как значение листа, вы бы использовали =COUNTIF. То же самое относится и к условному форматированию:
=COUNTIF({LOOKUP_RANGE_ABS_REF}, {VAL_TO_CHECK})
=COUNTIF($D$2:$D, B2)
Вот формула в действии:
Обратите внимание, что T-3000 и колеса не указаны в столбце списка деталей и поэтому не совпадают.
Сравнение с другим рабочим листом в одной книге
Если сравниваемые данные не находятся на одном рабочем листе, ссылка на диапазон {LOOKUP_RANGE_ABS_REF} должна быть заключена в вызов INDIRECT(), т.е.
=COUNTIF(INDIRECT("{OTHER_WORKSHEET_NAME}!{LOOKUP_RANGE_ABS_REF}"), {VAL_TO_CHECK})
=COUNTIF(INDIRECT("some sheet name!$A$2:$A"), B2)
Согласно официальной документации,
Формулы могут ссылаться только на один и тот же лист, используя стандартную запись (='имя листа'!ячейка). Чтобы сослаться на другой лист в формуле, используйте функцию ДВССЫЛ.
Это также отмечено в нескольких других вопросах SO (хотя и с использованием других основных формул, кроме COUNTIF):
В этих ответах отсутствует предостережение о том, что только что добавленная косвенность не устойчива к изменениям значения {OTHER_WORKSHEET_NAME}, например. вы (или какой-либо другой редактор) изменили фактическое имя рабочего листа.
В отличие от традиционных формул, вводимых на листе, здесь нет справочной ссылки во время выполнения, которая будет обновлять статическое текстовое значение, которое вы должны были ввести при создании правила условного формата. Это отсутствие обновления ссылок на самом деле является одной из полезных функций INDIRECT(), поэтому не ждите, что она когда-либо изменится.
Более того, эталонный разрыв не будет сразу бросаться в глаза. Все элементы, которые были добавлены в диапазон форматирования после изменения имени, были рассчитаны по формату с использованием неработающей ссылки, но все элементы, добавленные до изменения имени, сохранят свой текущий формат. Только когда редактируется фактический диапазон, обернутый INDIRECT, любые ранее существовавшие условные форматы будут пересчитаны. Таким образом, вы можете не заметить, что лист был переименован, пока не добавите в список новую допустимую часть.
person
tehhowch
schedule
21.07.2018
