0

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

1 Answer 1

2

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

test file

summary

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

6
  • 1
    Please include the full solution within your reply. "try this solution: test file" accompanied by an image means nothing to a site user. Please have a look at similar answers Jul 23 at 19:03
  • @marikamitsos please give me some time ... I went for dinner :)
    – Daniele
    Jul 23 at 19:16
  • Sorry. bon appétit :) Jul 23 at 19:19
  • ... and maybe eliminate that downvote
    – Daniele
    Jul 23 at 19:48
  • Of course. Done :) Jul 23 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.