Comparing Emails in different sheets

Mounika

I am using Smartsheet, to collect data on some challenges that my department would like to conduct, and there will be four challenges a year (one for every months) for the participants who registers (registration is only once per participant)

First sheet (Registration Sheet) collects the registration information via form named as “Registration form”. To all participants who are registered, an email will be sent to complete the challenge (with Smartsheet form  link (Challenge form) each time when the new challenge period begins).

This link will be same for all the challenges (despite, the link will be sent everytime when the new challenge round begins), but the only fields inside the link will be unique for each challenge. The data captured via this Link will be captured into Challenge sheet (second sheet).

In both the sheets, Email address is the only common field, and the participants will enter same email address in both the forms, so I am connecting both the sheets using this email address column to keep all the information under one roof (the registration details like First Name, profession etc with their challenge answers, in challenge sheet)

After 45 days of the challenge period begins, I want to send an reminder emails to all the participants who have not completed the challenge for that round. Now, how do I compare the entire list of emails in registration sheet with list of emails present in Challenge sheet within in a specific time frame, and get a list of emails, that are not present in Challenge sheet (only for that time period) ?

@Genevieve P. Could you please have a look into this, and help me with a solution? Thanks in advance!

bisaacs

Hi @Mounika,

I would do a couple of things to make this work. First, I would create a helper column with a column formula that checks to see if there are emails entered for both the Registration and the Challenge Sheet:

=IF(COUNTIF([Challenge Email]:[Challenge Email], [Registration Email]) = 0, "Incomplete")

I would then create another helper column that counts the number of days between the start of the challenge period and today:

=TODAY() - [Challenge Date]@row

Finally, I would create an automation workflow that runs daily. After the initial block, create a condition block that checks if the # of days that has passed is greater than or equal to 45, AND if the email helper column equals "Incomplete". Then if those conditions are met, it sends an alert to the Registration email.

Hope this helps!

Mounika

Hi @bisaacs

Thank you so much for responding!

However, I am not collecting Date via form, however, I've created a column type "Created Date".

And within the challenge, I've created an other dropdown column "Challenger Term" which will be hidden field in the form. And this will be changed when new challenge begins and fields within that form are changed, to C1, C2, C3, etc based on the challenge round. Is it possible to create a Reference from other sheet which has all the registration emails and compare it with email address in challenge sheet and giving the condition of this column "Challenge term"?

bisaacs

Hey @Mounika,

I think a screenshot showing what the sheets look like would be helpful. Please be sure to hide any sensitive information