Conditional Formatting

In the attached spreadsheet, I already have conditional formatting for two of the tables (left and middle). On the right, I want to have a visual depiction of the data on the other two tables. The numbers are referencing a code for a training program and are in time order (first to complete on top, then last on bottom). What I want is:

For AG5:AR77 I want it to turn green if the name in AG2 (in large text on top) has an x in either of the tables to the left. For example, if Mathisen has 2150 with an X, I want all the 2150's on the right most table to turn green, but only when Mathisen is in AG2.

The only way I can think of doing this is manually putting conditional formatting in each individual cell with an AND forumla. Thoughts?

View attachment Excel Trial 2.xlsx

I figured it out. Had to do a COUNTIFS and retun the value into another cell, then use that for the conditional formatting. Seems pretty stable, since the data won't be changing, just the x's. Let me know if you can think of a better way!

You could do it in CF with a formula of

=(COUNTIFS($B$3:$B$56,AG4,INDEX($C$3:$O$56,0,MATCH($AG$2,$C$2:$O$2,0)),"x")+COUNTIFS($R$3:$R$56,AG4,INDEX($S$3:$AE$56,0,MATCH($AG$2,$S$2:$AE$2,0)),"x"))>0