How do we identify which project manager did not fill out a form from a list of 50 PMs?

jerry123

Hi all,

I have a list of 50 project managers. I also have a form where I want each PM to enter in their projects, how can I identify which PM has not filled this form?

Appreciate the help!!!

Thanks

KPH

There are two parts to this

1. Collect who has completed the form as the form is completed
2. Identify who out of the 50 has not completed

Are you stuck on both parts?

If you have part 1 covered and need help with part 2 - I suggest a separate sheet with a list of the 50 PMs in one column and an INDEX MATCH or VLOOKUP to check each against the sheet the form populates.

Andrée Starå

Hi @jerry123

I hope you're well and safe!

I recently developed a solution for a client that checks it weekly.

• Do you need it just once or by any recurrence?
• Also, are the list and form on the same sheet?
• Do you collect their answers in the same sheet?
• Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

jerry123

Hi @Andrée Starå ! Thank you for your response. I made some progress.

I have 1 sheet with all contact information and another form (with a submitted by automatic field that captures who has submitted the form by login)

now, I have this formula on the contact sheet

For some reason, it doesn't pick up from the automated field from the other sheet.

Thanks for help!

Paul Newcome

You will need the email addresses listed out in the formula sheet. The field on the Form sheet that captures who submitted the form stores the email address, so you need to have a match on that instead of the name in the contact type field.

Andrée Starå

@jerry123

Excellent!

Happy to help!

You'd have to use the email instead. Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

jerry123

Makes total sense. Thank you this worked!!! @Paul Newcome @Andrée Starå

Now, if I wanted to send a reminder to those who haven't completed the form. I added another "People" column and put an =Email@row to make it into a contact column to be able to send the reminder. But since the automation workflow is as below: it will only be triggered when rows are changed... if not I have to run the workflow manually. Are there any better ways to do this?

KPH

Hi @jerry123

You can change the trigger to run by time (use When a date is reached and then custom occurrence) and then add a condition to only run if unchecked.

Andrée Starå

@jerry123

Excellent!

You're more than welcome!

You'd change the Trigger to: When a date is reached.

Did that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

jerry123

Thank you all! @KPH @Andrée Starå @Paul Newcome Always grateful for the Smartsheet community!! :)

Paul Newcome

Happy to help. 👍️