Why will my Google Sheets formula only look to the right?

Question

I'm trying to set up a conditionally formatted training record. I have a separate sheet that shows job titles and X's for required training. Because conditional formatting cannot reference other sheets, in the actual training record I copy the previous sheet (=Sheet1!A1). In the record, there's a drop-down to select job function.

My formula works, but only for the current column and to the right. Column 1 will work for any column (job title), but column 3 can only read job titles on column 3+.

I've tried moving the source data to the right (so the job titles copied from the first sheet start at BB300 instead of B300) but it has the same issue. Locking down the column letter with $ screws up the formula.

=if(AND(B3="",OR(AND(B$2=B$300,B301="X"),AND(B$2=C$300,C301="X"),AND(B$2=D$300,D301="X"))),True,False)

Sample copy of sheet https://docs.google.com/spreadsheets/d/1q0ImPv7FAeDNgz_adOyedcmA2RCot-UzI7nBByMWNiM/edit?usp=sharing

This is a shortened version of the code I'm using, but the idea is that B2 is the job title for the first person, C2 is the job title for the second person, etc. The script checks to see if the job title in B2 matches B300 (the copied requirements from sheet 1) and if so, checks if there is an X. If so, the cell colors (due to the conditional formatting being true) unless there's an X in the cell to show training is completed.

This works appropriately for the first column, but in column 2 (second employee) if I select the job title in column 1 none of the cells flag appropriately (are all blank).

Answer 1

...because conditional formatting cannot reference other sheets...

actually it can. you need to use INDIRECT for that. example:

=A1=INDIRECT("Sheet1!A1")

...to compare if A1 from current sheet Sheet2 is equal to A1 of Sheet1

_____________________________________________________

in your sheet Training Requirements add one row above header row and hide it:

then you can use this custom formula:

=INDIRECT("Training Requirements!"&ADDRESS(3, MATCH(B$2, 
 INDIRECT("Training Requirements!2:2"), 0), 4)&":"&SUBSTITUTE(ADDRESS(3, MATCH(B$2, 
 INDIRECT("Training Requirements!2:2"), 0), 4), 3, ))<>B3

spreadsheet demo