Coloring cells according to a validation

[Anonymous]

Hello,

I'm generating a table where I group for a given service, several tasks. These tasks have different priority (critical, average and low) and I need to display a color map per service.

If any task is critical, turn it red. (regardless of whether you have medium or low tasks)

If any task is average, and has no critical, its yellow

If all tasks are low, they turn green.

Example:

Service	Task	Color
Service1	Task 1: Critical Task 2: Media Task 3: Media Task 4: Low	Red
Service 2	Taks 1: Low Task 2: Low Task 3: Media	Orange
Service 3	Task 1: Low Task 2: Low Task 3: Low	Green

thank you very much!

[sergio0592]

But the problem with this formula is that for eg with Service 3, it will color green if any task is low whereas requirement for service 3 is "if all tasks are low".

[sergio0592]

Try with

=if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 1"}>} Priority),'critical')>0,lightred(),

if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 2"}>} Priority),'media')>0 and SubStringCount (concat(TOTAL <Service> {<Service={"Service 2"}>} Priority),'critical')=0 ,rgb(255,156,57),

if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 3"}>} Priority),'low')=NoOfRows(),lightgreen())))

[effinty2112 · Accepted solution]

Hi Paola,

You can use

=Pick(Wildmatch(Concat(TOTAL <Service>Task,'|'),'*Critical*','*Medium*','*Low*'),RGB(255,0,0),RGB(255,165,0),RGB(0,255,0))

to give

Service	Task
Service 1	Task 1:critical
Service 1	Task 2:medium
Service 1	Task 3:medium
Service 1	Task 4:low
Service 2	Task 1:low
Service 2	Task 2:low
Service 2	Task 3:medium
Service 3	Task 1:low
Service 3	Task 2:low
Service 3	Task 3:low

Regards

Andrew

[effinty2112]

Not so:

Service	Task
Service 1	Task 1:critical
Service 1	Task 2:medium
Service 1	Task 3:medium
Service 1	Task 4:low
Service 2	Task 1:low
Service 2	Task 2:low
Service 2	Task 3:medium
Service 3	Task 1:low
Service 3	Task 2:low
Service 3	Task 3:critical

[Anonymous]

Hi Jean,

Maybe I'm doing something wrong, I share my document, if you can review it,

Thank you.

[effinty2112]

Dear Jean-Baptiste,

Wildmatch(Concat(TOTAL <Service>Task,'|'),'*Critical*','*Medium*','*Low*')

will return 1 if 'Critical' is a substring of the concatenation of a Service's Tasks. If 'Critical' is not a substring it will return 2 if 'Medium' is a substring and if this is not then it will return 3 if 'Low' is a substring. If none of these is a substring 0 will be returned and the Pick function will  not pick a colour.

Service	Task
Service 1	Task 1:critical
Service 1	Task 2:medium
Service 1	Task 3:medium
Service 1	Task 4:low
Service 2	Task 1:low
Service 2	Task 2:low
Service 2	Task 3:medium
Service 3	Task 1:hot
Service 3	Task 2:cold
Service 3	Task 3:cool

Kind regards

Andrew

[sergio0592]

Indeed, sorry

[effinty2112]

No apologies required! We're all just trying to help each other out.

[Anonymous]

yeeiii... thank you so much!!

Its correct!!!!! but.. now I need put a other validation, whit status.

• Green, if status is Closed/cerrada, or priority is low (like your example)
• Orange and red, if status is "assigned" and  rules applied.

I send you my document.

I really thank you.

Regards.