Notifying a Cell About Changes in a Different Cell

happinessisland

I want to notify people that are assigned to a parent task to be notified when any of the subtasks are completed. How would I do that?

Lauren Dominique

Hi! Below is my recommendation. It’s a bit of a multi-step process due to the way Smartsheet needs to be able to collect and read contact information to send automations.

1. Create a new “Master Contact Sheet”. Rename “Primary Column” to “Email Address”. Make “Column 2” a Contact Column and rename it to “Contact”. Enter in the information for the folks from your team who are part of this project. If you already have a sheet that looks like this elsewhere in your Smartsheet account, you can leverage the pre-existing sheet instead of creating one new.
2. Return to the sheet at hand. Create a helper column (we’ll call it “Parent Assignee Contact”) to reference the parent cell you’re looking for. Let’s say the name of the column you are referencing is “Assigned”. You are going to use a cross-sheet reference to search for the “Assigned” name in the “Contact” column of the “Master Contact Sheet” and return the associated email address for that person. The formula will look something like this: =INDEX({Master Contact Sheet Email Address}, MATCH (PARENT([Assigned]@row), {Master Contact Sheet Contact}, 0))
3. Once you have it entered, convert it to a column formula. If that’s working correctly, you’ll see the email of the person assigned to the parent row in the “Parent Assignee Contact” cell for all the respective child rows.
4. Then, the last step will be to create an automation to alert someone whenever a row is added or changed and the Status field changes to Complete. You’ll set the person to be notified as the contact in the “Parent Assignee Contact” column.

Please let me know if you continue to have any trouble or would like me to expand upon or clarify any of the above, and I’d be happy to do so!

Hope this helps - Happy Smartsheeting! 😊

Best,

Lauren

Lauren Dominique

Hi! Below is my recommendation. It’s a bit of a multi-step process due to the way Smartsheet needs to be able to collect and read contact information to send automations.

1. Create a new “Master Contact Sheet”. Rename “Primary Column” to “Email Address”. Make “Column 2” a Contact Column and rename it to “Contact”. Enter in the information for the folks from your team who are part of this project. If you already have a sheet that looks like this elsewhere in your Smartsheet account, you can leverage the pre-existing sheet instead of creating one new.
2. Return to the sheet at hand. Create a helper column (we’ll call it “Parent Assignee Contact”) to reference the parent cell you’re looking for. Let’s say the name of the column you are referencing is “Assigned”. You are going to use a cross-sheet reference to search for the “Assigned” name in the “Contact” column of the “Master Contact Sheet” and return the associated email address for that person. The formula will look something like this: =INDEX({Master Contact Sheet Email Address}, MATCH (PARENT([Assigned]@row), {Master Contact Sheet Contact}, 0))
3. Once you have it entered, convert it to a column formula. If that’s working correctly, you’ll see the email of the person assigned to the parent row in the “Parent Assignee Contact” cell for all the respective child rows.
4. Then, the last step will be to create an automation to alert someone whenever a row is added or changed and the Status field changes to Complete. You’ll set the person to be notified as the contact in the “Parent Assignee Contact” column.

Please let me know if you continue to have any trouble or would like me to expand upon or clarify any of the above, and I’d be happy to do so!

Hope this helps - Happy Smartsheeting! 😊

Best,

Lauren

happinessisland

Hi Lauren,

Thanks for your response! It works :)