Check box in sheet when email address entered in a form matches an existing email address in a sheet

Ben Handel

Hi,

I'm trying to get my dashboard on employee training to automatically remove rows from a report when a specific user responds using a form. I have the responses from the form automatically moving to another sheet/report, but then I want that user to be removed from the 'yet to respond' report in the dashboard.

My thought was to check a box when the email address entered in the form matches the master employee list, but I'm having trouble getting cross sheet formulas to do this the way that I would like. Any ideas?

Paul Newcome

Try something like this on the sheet being used for the report...

=IF(CONTAINS([Email Address]@row, {Master List Email Address Column}), 1)

This will check the box if the email address is found anywhere in the Email column of the master list.

Paul Newcome

Try something like this on the sheet being used for the report...

=IF(CONTAINS([Email Address]@row, {Master List Email Address Column}), 1)

This will check the box if the email address is found anywhere in the Email column of the master list.

Ben Handel

Thanks so much Paul.

The way I have it set up, I want it to check a column in the master list. I was able to make it work the way I want by doing a little bit of re-arranging of your function by searching how to actually perform a CONTAINS function.

What ended up working for me was :

=IF(CONTAINS(([Email Address]@row), {Employee Training Form Responses Range 1}), 1)

Thanks so much for your help, and I hope my response helps someone else down the line.

☮️

Paul Newcome

My apologies for getting the sheets mixed up, but I am happy to have helped get you on the right track. 👍️

As a note... You actually do not need the parenthesis around [Email Address]@row. It may not seem like to o big of a deal on a formula as straightforward as this one, but extra parenthesis can become a mess fast when you start getting into the more complex formulas.

Kate Kelley

This was just what I needed, thank you!