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

Bretters_at_work

New Member
Joined
Aug 22, 2019
Messages
2
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

CompleteEMAIL_ADDRROLEROLE2ROLE3
jim.smith@work.comAdmitting ClerkRegistration SupervisorReferrals Coordinator
wanda.roman@work.comTemplate BuilderReferrals Coordinator
bill.jones@work.comAdmitting ClerkTemplate Builder

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>



Table 2

RoleCourse
Admitting ClerkBasic Access
Admitting ClerkRegistration
Referrals CoordinatorReferrals
Registration SupervisorBasic Access
Registration SupervisorRegistration
Registration SupervisorSupervisor
Template BuilderTemplates 101

<colgroup><col><col></colgroup><tbody>
</tbody>


Table 3

Course_NameEmail_Address
Basic Accessbill.jones@work.com
Registrationbill.jones@work.com
Supervisorbill.jones@work.com
Basic Accessjim.smith@work.com
Registrationjim.smith@work.com
Supervisorjim.smith@work.com
Referralswanda.roman@work.com
Templates 101wanda.roman@work.com

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Aug 22, 2019
Messages
2
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top