How to assign task to multiple contacts and track for each contacts completion

KMeyer · 04/01/22

Hi,

I'm building a sheet of tasks where some tasks will be assigned to multiple contacts and each assignee/contact will need to acknowledge that they've reviewed/completed the task. Multiple Contacts is enabled on the "assigned to" column and the preference is to have only one column to track assignees. Looking for help on how to add a "status" column that each assignee would need to mark as done and once all assignees have completed their review it marks the task as complete. TIA!!!

Genevieve P. · 04/04/22

Hi @KMeyer

There are a few different ways to do this, depending on if you have a set list of contacts or if it will vary each time.

If the contacts will vary and you don't have a pre-set list, what I would do in this instance is have another Multi-Select dropdown where each user can add a value in the cell, saying for example "Gen - Reviewed" or something similar. You would want to educate them to include their name in the list so you can see who you are waiting on.

Then you can use a COUNTM formula to see if the number of users selected in your Assigned To is the same as the values submitted in the multi-select dropdown.

Ex:

=IF(COUNTM([Assigned To]@row) = COUNTM([Reviewed MultiSelect]@row), "All Reviews Submitted"

You could either have this formula be the one to output "Complete" in the Status column, or if you're manually adjusting some other status values then you could use this formula output to trigger a Change Cell workflow to update the Status.

Cheers,

Genevieve

Genevieve P. · 04/04/22

Hi @KMeyer

There are a few different ways to do this, depending on if you have a set list of contacts or if it will vary each time.

If the contacts will vary and you don't have a pre-set list, what I would do in this instance is have another Multi-Select dropdown where each user can add a value in the cell, saying for example "Gen - Reviewed" or something similar. You would want to educate them to include their name in the list so you can see who you are waiting on.

Then you can use a COUNTM formula to see if the number of users selected in your Assigned To is the same as the values submitted in the multi-select dropdown.

Ex:

=IF(COUNTM([Assigned To]@row) = COUNTM([Reviewed MultiSelect]@row), "All Reviews Submitted"

You could either have this formula be the one to output "Complete" in the Status column, or if you're manually adjusting some other status values then you could use this formula output to trigger a Change Cell workflow to update the Status.

Cheers,

Genevieve

KMeyer · 04/04/22

Thank you! This is a great idea! The contacts will be defined and the same, so curious what your thoughts are in this scenario?

Genevieve P. · 04/05/22

Hi @KMeyer

If the Contacts will all be the same, I would set up a number of Checkbox columns: one per contact. (You can hide these columns in the sheet if you don't want to show them in the main grid).

Then in your Update Request you can ask each contact to check their specific "Reviewed" box so you can track who has completed their part of the work.

In your Status column, you can COUNT how many of the checkboxes for this row are checked, versus how many people were assigned.

Cheers,

Genevieve

KMeyer · 04/06/22

Ok, great, Thank you so much for the guidance!

Ronish6997 · 06/29/23

Hi @KMeyer Did you able to implement the solution suggested by @Genevieve P.?

I"m having a similar issue and am new to Smartsheet if you can elaborate on it that would be helpful.

Ronish6997 · 06/29/23

@Genevieve P. Could you share the formula for the second scenario? If the Contacts will all be the same, I would set up a number of Checkbox columns: one per contact.

Genevieve P. · 06/30/23

Hi @Ronish6997

No problem! I'm happy to help clarify.

To COUNT how many rows have been checked, you'll want to look to see how many = 1 (or checked). It would be easiest if all the checkbox columns are next to each other. Then you can reference a section of your row as a range, like so:

=COUNTIF([First Checkbox]@row:[Last Checkbox]@row, 1)

See: COUNTIF Function / Create and Edit Formulas in Smartsheet

Cheers,

Genevieve

Ronish6997 · 06/30/23

I found your previous response helpful, but I have a query and need some assistance with it.

Here's the situation: I assigned tasks to five people and created an individual checkbox column for each. My goal is to ensure that unless every person selects their respective checkbox, the main "Done" column remains unchecked.

In other words, if all checkboxes are selected, the "Done" column checkbox should be marked as 1. Otherwise, it should remain unchecked. Could you please guide me on how I can achieve this?

Thank you for your support.

Genevieve P. · 07/03/23

Hi @Ronish6997

Thanks for explaining your situation further!

In this case, what you can say is IF the COUNT of Checkboxes = 5, then check the box. Otherwise, if it doesn't = 5 (meaning at least one of the boxes is un-checked), then don't check the box.

Try this:

=IF(COUNTIF([First Checkbox]@row:[Last Checkbox]@row, 1) = 5, 1, 0)

Cheers,

Genevieve

How to assign task to multiple contacts and track for each contacts completion

Best Answer

Answers

Categories