Formatação condicional baseada na frequência na coluna e no valor da célula adjacente (para cada entrada anterior)

Question

Estou tentando formatar uma coluna (T) na qual os nomes são inseridos usando uma lista suspensa de validação de dados.
Cada nome é atribuído a um projeto, portanto, na coluna anterior (S), usei a formatação condicional para fornecer um indicador de status da fase final do projeto (inserir o número 2 fornece um semáforo verde indicando uma fase completa).

O ideal é desativar a possibilidade de selecionar qualquer nome que tenha uma carga de trabalho de quatro ou mais projetos (a carga de trabalho está sendo capturada em uma planilha separada da qual a lista de nomes está sendo retirada). Mas depois de muita tentativa e erro (mais erro do que qualquer coisa tbh!) Eu não acho que isso é possível. Como solução geral, o que eu esperava fazer era formatar a coluna T para que o nome de alguém fosse listado na coluna pela quarta vez e o status da fase final de cada um desses quatro projetos não estivesse completo (ou seja, menos que 2) a célula fica âmbar ou aparece um ícone na formatação condicional.
Adicionar um nome cinco ou mais vezes sem alterar o status de projetos atribuídos anteriormente produziria uma célula vermelha ou um ícone.

Em este exemplo de dados , eu tenho duas opções de gerentes de projeto, Joe e Bob, ambos com anteriormente foram atribuídos quatro projetos. Joe não completou a fase final de "obtenção de resultados" dos últimos quatro projetos que recebeu, então ele ainda tem uma carga de trabalho ativa de quatro projetos, indicada por pontos amarelos que são criados ao inserir um 1 na coluna S. Bob não tem carga de trabalho ativa com todos os seus quatro projetos previamente designados marcados como completos, indicados por um ponto verde que são criados inserindo um 2 na coluna S. Neste exemplo, eu quero configurá-lo para que qualquer tentativa de adicionar Joe a T9 como o O gerente de projeto de um novo projeto produziria uma mensagem de erro impedindo-me de atribuí-lo a mais projetos ou o T9 seria condicionalmente formatado para mostrar que Joe excedeu a capacidade de carga de trabalho. Isso se ajustaria com base no fato de Joe concluir projetos mais antigos e aumentar sua capacidade. Como Bob não tem projetos ativos, eu poderia adicioná-lo sem problemas.

Editar em resposta a um número de comentários (que eu sou muito grato por!)

• Atualmente, não há interação entre a coluna S e a coluna T.
• Estou com dificuldades para fornecer um exemplo de como gostaria que os resultados parecessem. Como eu disse acima, idealmente gostaria de uma configuração que impeça que um gerente de projetos seja selecionado na coluna T se o nome deles já estiver presente na coluna T em quatro ocasiões anteriores e a célula adjacente a cada instância desse nome na coluna S contiver um 1 (ponto amarelo).

Eu sei que isso é super complicado, ou pelo menos parece que é para mim, mas tornaria a minha vida muito mais fácil se isso pudesse ser feito. Estou esperando muito do excel ou há alguém lá fora que possa me ajudar? Até mesmo uma solução alternativa seria muito apreciada.

Espero que isso não esteja duplicando uma pergunta ou discussão em outro lugar do site, mas estou procurando há algum tempo e não consigo encontrar nada que funcione para mim. Dito isso, uma razão para isso pode ser devido à complexidade do que estou tentando alcançar.

    

Answer 1

Eu decidi usar a rota de formatação condicional como uma solução potencial. A solução é bastante simples:

1. Crie uma tabela separada, talvez em uma planilha separada, para contar o número de projetos ativos em que cada pessoa está trabalhando. Use CountIFS() para fornecer uma contagem com base em vários critérios, que, no seu caso, é se o valor da célula for "Bob White" e o status do projeto não for concluído. Em seguida, registre-o. Faça isso para cada pessoa, para que cada pessoa tenha uma contagem de projetos ativa.

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

   [imagem acima]

2. Crie uma regra de formatação condicional para sua coluna de pessoa em sua tabela principal, que use uma função VLookup para retornar o número de projetos ativos e formate a célula se o número de projetos ativos for maior que 4.

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

   [imagem acima]

   Aplique a regra à coluna T . [imagem]

O resultado deve ser assim:

Notas: Para tornar o número máximo de projetos dinâmico, em vez de digitar o número máximo de projetos nas fórmulas, use uma célula da planilha para designar o número máximo e faça com que suas fórmulas façam referência a essa célula. Na tabela de contagens, você pode simplesmente digitar novos nomes e copiar a fórmula de contagem. Deixe-me saber se você tiver alguma dúvida ou precisar de mais ajuda sobre isso!