Условное форматирование на основе частоты в столбце и значения соседней ячейки (для каждой предыдущей записи)

Question

Я пытаюсь отформатировать столбец (T), где имена вводятся с использованием раскрывающегося списка проверки данных.
Каждое имя присваивается проекту, поэтому в предыдущем столбце (S) я использовал условное форматирование, чтобы дать индикатор состояния финальной фазы проекта (ввод числа 2 дает зеленый светофор, указывающий завершенную фазу).

В идеале я хотел отключить возможность выбора любого имени с рабочей нагрузкой из четырех или более проектов (рабочая нагрузка фиксируется на отдельном листе, откуда берется список имен).
Но после многих проб и ошибок (больше ошибок, чем что-либо другое!) Я не думаю, что это возможно.
В качестве обходного пути, то, что я надеялся сделать, это отформатировать столбец T, чтобы, когда в четвертый раз в этом столбце было указано чье-то имя, а состояние завершающей фазы каждого из этих четырех проектов было неполным (т.е. меньше, чем 2) ячейка становится янтарной или появляется значок в условном форматировании.
Добавление имени пять или более раз без изменения статуса ранее назначенных проектов приведет к появлению красной ячейки или значка.

В этих примерах данных у меня есть два выбора менеджеров проектов, Джо и Боб, которым ранее были назначены четыре проекта. Джо не завершил заключительную фазу "достижения результатов" последних четырех проектов, которым он был назначен, поэтому у него все еще есть активная рабочая нагрузка из четырех проектов, обозначенных желтыми точками, которые создаются путем ввода 1 в столбце S. У Боба нет активная рабочая нагрузка со всеми четырьмя его ранее назначенными проектами, помеченными как завершенные, обозначенные зеленой точкой, которые создаются путем ввода 2 в столбце S. В этом примере я хочу настроить его так, чтобы любая попытка добавить Джо в T9 в качестве Менеджер проекта для нового проекта выдаст сообщение об ошибке, не позволяющее мне назначить его для других проектов, или T9 будет условно отформатирован, чтобы показать, что Джо превысил рабочую нагрузку. Это будет корректироваться в зависимости от завершения старых проектов Джо и увеличения его возможностей. Поскольку у Боба нет активных проектов, я мог бы добавить его без проблем.

Редактировать в ответ на ряд комментариев (за что я очень благодарен!)

• В настоящее время нет никакого взаимодействия между столбцом S и столбцом T вообще.
• Я изо всех сил стараюсь привести пример того, как бы я хотел, чтобы результаты были похожи. Как я уже говорил выше, в идеале я хотел бы, чтобы установка, предотвращающая выбор менеджера проекта в столбце T, если его имя уже присутствовало в столбце T в четырех предыдущих случаях, и ячейка, смежная с каждым экземпляром этого имени в столбце S, содержит 1 (желтая точка).

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

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

Answer 1

Я решил пойти по пути условного форматирования в качестве потенциального решения. Решение довольно простое:

1. Создайте отдельную таблицу, возможно, на отдельной рабочей таблице, чтобы подсчитать количество активных проектов, над которыми работает каждый человек. Используйте CountIFS() чтобы обеспечить подсчет на основе нескольких критериев, который в вашем случае означает, что если значением ячейки является "Боб Уайт", а состояние проекта не завершено, то подсчитайте его. Сделайте это для каждого человека, чтобы у каждого человека был активный счет проекта.

   =COUNTIFS(DataSet!$T:$T,A2,DataSet!$S:$S,"<>1")
   

   [изображение выше]

2. Создайте правило условного форматирования для вашего столбца person в основной таблице, которое использует функцию VLookup чтобы вернуть количество активных проектов, и отформатируйте ячейку, если количество активных проектов больше 4.

   =IF(VLOOKUP($T1,Counts!$A:$B,2,FALSE)>3,1,0)
   

   [изображение выше]

   Примените правило к столбцу T [образ]

Результат должен выглядеть так:

        

Примечания. Чтобы сделать максимальное число проектов динамичным, вместо ввода максимального числа проектов в формулы используйте ячейку рабочего листа, чтобы указать максимальное число, и ваши формулы ссылаются на эту ячейку. В таблице счетчиков вы можете просто ввести новые имена и скопировать формулу счета. Дайте мне знать, если у вас есть какие-либо вопросы или вам нужна дополнительная помощь по этому вопросу!