Conditional formatting with two conditions from another sheet

Question

I've looked through the forum but haven't found any solutions that match what I am trying to do.

I have created a form to ask band members whether they will be attending one of our events. Their responses populate the Form Responses 1 sheet in the document below. I've been trying to use Conditional formatting to have their names on the Summary sheet turn green (Attending) or red (Not attending). The idea was to match the name in the Summary sheet with the entries on the Form Responses sheet and have the color change based on their responses. If they haven't responded yet, the name in the Summary lists doesn't change. Is this possible? Thanks for any suggestions you may offer.

https://docs.google.com/spreadsheets/d/1_qHEUKsHwStax2-TR9b6AwbXmb1cZBVe76At7uvZXqs/edit?usp=sharing

Answer 1

If the names of the various characters are unique within the various boxes, you can try this solution:

test file

Explanation

1)With a calculation I created a list of all the names that are in an "attending" state so the cell Summary!H1 contains this formula:

={"Attending";sort(unique(flatten(filter('Form Responses 1'!C2:L,'Form Responses 1'!M2:M="Attending"))))}

2)With another calculation I created a list of all the names that are in an "Not attending" state so the cell Summary!I1 contains this formula:

={"Not attending";sort(unique(flatten(filter('Form Responses 1'!C2:L,'Form Responses 1'!M2:M="not attending"))))} 

Instead, these are the formulas applied for the conditional formatting of GREEN and RED text ( RANGE B4:F ):

GREEN

=COUNTIF($H$2:$H,B4)>0

RED

=COUNTIF($I$2:$I,B4)>0

In this way, any name written in the cells of the FORM and which is in the "Attending" or "Not Attending" states will be colored GREEN or RED respectively in the Summary sheet