How do I use contents of one cell to modify the conditional formatting of another cell?

Question

I have a table of cells filled with either Y or N and another table of cells filled with either A or B. I want the contents of the Y/N table to determine which columns are included in a subsequent content check. For example, the desired rule would produce the following behaviors:

• A1, B1, and C1 all contain Y, so $E5 will display whether $A5, $B5, and $C5 all contain A.
• A2 and B2 contain Y but C2 contains N, so $F5 will display whether $A5 and $B5 all contain A.
• A3 and C3 contain Y but B3 contains N, so $G5 will display whether $A5 and $C5 all contain A.

Here's a sheet depicting the above behavior.

The desired rule would only bother to check the contents of an A/B table cell if a corresponding Y/N table cell contained Y.

Words are hard, so here's a rephrasing of the desired behavior: Compare each A/B table row to each Y/N table row. If all columns containing Y (on the Y/N table) also contain A (on the A/B table), mark the that row in a corresponding column as green. If any Y column does not contain A, mark that row in the corresponding column as red.

It's fine if there is an intermediary table that needs to be constructed, as long as it can all react automatically to changes to the Y/N and A/B table contents. I considered writing a unique rule for each column, but that would be completely impractical given how often the Y/N table will change.

If there's a simpler method that can achieve the sample results than the method I'm describing, that's welcome, too.

Answer 1

Here's one approach you may test out:

Apply to range: H8:J14

Custom formula is:

=let(Σ,filter($D8:$F8,chooserows($D$4:$F$6,column()-7)="Y"),or(countif(Σ,"A")=0,countunique(Σ)>1))