Dynamic Conditional formating based with a custom formula-CodePudding

Ok, it's a bit of a head scratcher for me.

I am working on a work load spreadsheet and I need to highlight people with different colours depending on their workload so that I could see at glance who's loaded too much and who still has capacity. Capacity number is just a number of project she or he can handle can be 3 to 5. I store this data in a separate sheet "Resources". 3 colours of load are enough

The problem I am facing is conditional formatting in GS doesn't support pulling data from a separate sheet. Id' rather keep all the raw data outride of the main Overview sheet. But if it's impossible maybe helper columns next to each group of workers would work as well However, it's important be able to copy the whole row and paste at the end for new projects.

Plus I have difficulty to figure out how to create a formula that works for all names, rather than creating colour conditions for each single name.

Any suggestions?

Here is an example spreadsheet of table and bellow what I envision it should like. I need to be able to see in the Overview sheet at glance who's at their capacity. So as you can see Bobbie is on 6 projects, so he has to be RED. Tom is only on one project and the colour should be something like blue or green. Hope that makes sense

CodePudding user response：

Okay, I think I found a way to do this. However, it needs a couple of Conditional format rules if you want to do a kind of gradian.

The good thing is that it will not require to be linked to a name. Even if you change the names, it will not affect it.

The rule will look something like this:

The one that has a capacity of up to 5 will look like this after adding the rules:

For the ones for developers, I added the 3 columns at the same time, so the formula takes the 3 columns for the capacity count.

I edited the Sheet called "Copy of Overview." I added the formatting for the C and D columns.

Reference:

Helper table

Paste this formula anywhere to get the helper table with the count of each worker occurrence in the range C2:F

= ArrayFormula({
  "Name", "Work Load";
   FILTER(UNIQUE(FLATTEN(Overview!C2:F)),UNIQUE(FLATTEN(Overview!C2:F))<>"",UNIQUE(FLATTEN(Overview!C2:F))<>"-"), IF(
   FILTER(UNIQUE(FLATTEN(Overview!C2:F)),UNIQUE(FLATTEN(Overview!C2:F))<>"",UNIQUE(FLATTEN(Overview!C2:F))<>"-")="",,
   COUNTIF(FILTER(FLATTEN(Overview!C2:F),FLATTEN(Overview!C2:F)<>""), FILTER(UNIQUE(FLATTEN(Overview!C2:F)),UNIQUE(FLATTEN(Overview!C2:F))<>"",UNIQUE(FLATTEN(Overview!C2:F))<>"-")))})

Conditional formatting & formulas - Simple

Similar to Giselle Valladares, answer "2022-08-12 22:00:41Z"
Simple but have a Limitation: a single white space is also highlighted

Color "Formatting style"	Apply to range	Formula
Green	C2:G	=COUNTIF(C2:F,C2)=1
Blue	C2:G	=COUNTIF(C2:F,C2)=2
yellow	C2:G	=COUNTIF(C2:F,C2)=3
Red	C2:G	=COUNTIF(C2:F,C2)>=4

Conditional formatting & formulas - Complex

Try this

Color "Formatting style"	Apply to range	Formula
Green	C2:G	=ArrayFormula(REGEXMATCH(Overview!C2:G23, TEXTJOIN("\|",,FILTER(FILTER(FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-"),FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")<>""),FILTER(IF( FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")="",, COUNTIF(FILTER(FLATTEN(Overview!C2:G),FLATTEN(Overview!C2:G)<>""), FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-"))),IF( FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")="",, COUNTIF(FILTER(FLATTEN(Overview!C2:G),FLATTEN(Overview!C2:G)<>""), FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")))<>"")=1))))
Blue	C2:G	=ArrayFormula(REGEXMATCH(Overview!C2:G, TEXTJOIN("\|",,FILTER(FILTER(FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-"),FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")<>""),FILTER(IF( FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")="",, COUNTIF(FILTER(FLATTEN(Overview!C2:G),FLATTEN(Overview!C2:G)<>""), FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-"))),IF( FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")="",, COUNTIF(FILTER(FLATTEN(Overview!C2:G),FLATTEN(Overview!C2:G)<>""), FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")))<>"")=2))))
yellow	C2:G	=ArrayFormula(REGEXMATCH(Overview!C2:G, TEXTJOIN("\|",,FILTER(FILTER(FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-"),FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")<>""),FILTER(IF( FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")="",, COUNTIF(FILTER(FLATTEN(Overview!C2:G),FLATTEN(Overview!C2:G)<>""), FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-"))),IF( FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")="",, COUNTIF(FILTER(FLATTEN(Overview!C2:G),FLATTEN(Overview!C2:G)<>""), FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")))<>"")=3))))
Red	C2:G	=ArrayFormula(REGEXMATCH(Overview!C2:G23, TEXTJOIN("\|",,FILTER(FILTER(FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-"),FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")<>""),FILTER(IF( FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")="",, COUNTIF(FILTER(FLATTEN(Overview!C2:G),FLATTEN(Overview!C2:G)<>""), FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-"))),IF( FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")="",, COUNTIF(FILTER(FLATTEN(Overview!C2:G),FLATTEN(Overview!C2:G)<>""), FILTER(UNIQUE(FLATTEN(Overview!C2:G)),UNIQUE(FLATTEN(Overview!C2:G))<>"",UNIQUE(FLATTEN(Overview!C2:G))<>"-")))<>"")>3))))