Need help to conditionally format a cell in one cell of a table based on the values of two other tables

[Bretters_at_work]

I am struggling with finding a way to highlight cells in my data preferably using conditional formatting in colour for sorting the following or flagging in some sort. My actual data source has about 8,000 people so manual sorting is not needed and I need to make sure I record each step for future pulls of data.


I am looking for who is done all their training. For example wanda.roman@work.com has completed all she needs for her role. So I would want her roles to highlight in “green” and mark in the column completed Yes


Next I want to see what courses were they supposed to be scheduled into but they haven't registered yet? For example jim.smith@work.com has a role of Referral coordinator but has not registered for the course. And bill.jones@work.com has a role of template builder but has not registered for it either. I would like to highlight those course in “red” and mark in the column completed No.


As a bonus is there a way I could highlight those that took a class not needed for their role for example bill.jones@work.com has taken the supervisor course but does not have that role. Could I highlight his name in “yellow” or I could add in a column that says extra courses taken similar to the above and just say yes or no


I have 3 tables already named


Table 1 Roles is the Roles is the listed roles for the individuals, They can have multiple roles


Table 2 Course is the courses that are listed is the needed education for their roles some roles have multiple courses needed.


Table 3 Learning is the courses taken so far by the individuals in Table 1.


Table 1

[TABLE="width: 680"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Complete[/TD]
[TD]EMAIL_ADDR[/TD]
[TD]ROLE[/TD]
[TD]ROLE2[/TD]
[TD]ROLE3[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]jim.smith@work.com[/TD]
[TD]Admitting Clerk[/TD]
[TD]Registration Supervisor[/TD]
[TD]Referrals Coordinator[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]wanda.roman@work.com[/TD]
[TD]Template Builder[/TD]
[TD]Referrals Coordinator[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]bill.jones@work.com[/TD]
[TD]Admitting Clerk[/TD]
[TD]Template Builder[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Table 2

[TABLE="width: 558"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Role[/TD]
[TD]Course[/TD]
[/TR]
[TR]
[TD]Admitting Clerk[/TD]
[TD]Basic Access[/TD]
[/TR]
[TR]
[TD]Admitting Clerk[/TD]
[TD]Registration[/TD]
[/TR]
[TR]
[TD]Referrals Coordinator[/TD]
[TD]Referrals[/TD]
[/TR]
[TR]
[TD]Registration Supervisor[/TD]
[TD]Basic Access[/TD]
[/TR]
[TR]
[TD]Registration Supervisor[/TD]
[TD]Registration[/TD]
[/TR]
[TR]
[TD]Registration Supervisor[/TD]
[TD]Supervisor[/TD]
[/TR]
[TR]
[TD]Template Builder[/TD]
[TD]Templates 101[/TD]
[/TR]
</tbody>[/TABLE]


Table 3

[TABLE="width: 370"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Course_Name[/TD]
[TD]Email_Address[/TD]
[/TR]
[TR]
[TD]Basic Access[/TD]
[TD]bill.jones@work.com[/TD]
[/TR]
[TR]
[TD]Registration[/TD]
[TD]bill.jones@work.com[/TD]
[/TR]
[TR]
[TD]Supervisor[/TD]
[TD]bill.jones@work.com[/TD]
[/TR]
[TR]
[TD]Basic Access[/TD]
[TD]jim.smith@work.com[/TD]
[/TR]
[TR]
[TD]Registration[/TD]
[TD]jim.smith@work.com[/TD]
[/TR]
[TR]
[TD]Supervisor[/TD]
[TD]jim.smith@work.com[/TD]
[/TR]
[TR]
[TD]Referrals[/TD]
[TD]wanda.roman@work.com[/TD]
[/TR]
[TR]
[TD]Templates 101[/TD]
[TD]wanda.roman@work.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

The result would be 8000 arrary formulas and 2 formulas for conditional formats.
And one more array formula for the third point (not yet included).


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64.63px;" /><col style="width:159.68px;" /><col style="width:109.31px;" /><col style="width:145.43px;" /><col style="width:134.02px;" /><col style="width:18.06px;" /><col style="width:145.43px;" /><col style="width:91.25px;" /><col style="width:18.06px;" /><col style="width:91.25px;" /><col style="width:162.53px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td colspan="5" style="background-color:#ccc0da; text-align:center; ">TABLE 1</td><td > </td><td colspan="2" style="background-color:#ccc0da; text-align:center; ">Table 2</td><td > </td><td colspan="2" style="background-color:#ccc0da; text-align:center; ">Table 3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#dbeef3; font-weight:bold; ">Complete</td><td style="background-color:#dbeef3; font-weight:bold; ">EMAIL_ADDR</td><td style="background-color:#dbeef3; font-weight:bold; ">ROLE</td><td style="background-color:#dbeef3; font-weight:bold; ">ROLE2</td><td style="background-color:#dbeef3; font-weight:bold; ">ROLE3</td><td style="font-weight:bold; "> </td><td style="background-color:#dbeef3; font-weight:bold; ">Role</td><td style="background-color:#dbeef3; font-weight:bold; ">Course</td><td style="font-weight:bold; "> </td><td style="background-color:#dbeef3; font-weight:bold; ">Course_Name</td><td style="background-color:#dbeef3; font-weight:bold; ">Email_Address</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >No</td><td >jim.smith@ work.com</td><td style="background-color:#ff0000; ">Admitting Clerk</td><td style="background-color:#ff0000; ">Registration Supervisor</td><td style="background-color:#ff0000; ">Referrals Coordinator</td><td > </td><td >Admitting Clerk</td><td >Basic Access</td><td > </td><td >Basic Access</td><td >bill.jones@ work.com</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Yes</td><td >wanda.roman@ work.com</td><td style="background-color:#92d050; ">Template Builder</td><td style="background-color:#92d050; ">Referrals Coordinator</td><td style="background-color:#92d050; "> </td><td > </td><td >Admitting Clerk</td><td >Registration</td><td > </td><td >Registration</td><td >bill.jones@ work.com</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >No</td><td >bill.jones@ work.com</td><td style="background-color:#ff0000; ">Admitting Clerk</td><td style="background-color:#ff0000; ">Template Builder</td><td style="background-color:#ff0000; "> </td><td > </td><td >Referrals Coordinator</td><td >Referrals</td><td > </td><td >Supervisor</td><td >bill.jones@ work.com</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Registration Supervisor</td><td >Basic Access</td><td > </td><td >Basic Access</td><td >jim.smith@ work.com</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Registration Supervisor</td><td >Registration</td><td > </td><td >Registration</td><td >jim.smith@ work.com</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Registration Supervisor</td><td >Supervisor</td><td > </td><td >Supervisor</td><td >jim.smith@ work.com</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Template Builder</td><td >Templates 101</td><td > </td><td >Referrals</td><td >wanda.roman@ work.com</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Templates 101</td><td >wanda.roman@ work.com</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >A3</td><td >{=IF(SUM(IF(ISNUMBER(SEARCH(IF($C3:$E3<>"",$C3:$E3),$G$3:$G$9)),1,0))=COUNT(IF(MATCH(IF(ISNUMBER(SEARCH(IF($C15:$E15<>"",$C15:$E15),$G$3:$G$9)),$H$3:$H$9),IF($K$3:$K$10=B15,$J$3:$J$10,0),0)>0,1)),"Yes","No")}</td></tr></table></td></tr></table>



The previous example is simulating how you have your data, but honestly I do not recommend having too many array formulas, it will slow your sheet.


Maybe someone can make a shorter formula, but it will still be array formula.


I suggest a macro, which you must execute every time you want to know the status.

 

[Bretters_at_work]

Thank you Dante,

I have been testing it out, I think I need to edit a bit as I scaled it up I was getting errors. I appreciate the help for what I needed it for it seemed to do what you said.

Thank you

 

[DanteAmor]

The formulas for the format conditions, for red:

=$A3="No"

for green.

=$A3="Yes"

In applies to:

=$C$3:$E$5

Note: You must put the data as they are in my example for the formula to work.