Smartsheet Tips: How to Create Conditional Reminders

Blog

Smartsheet Tips: How to Create Conditional Reminders

A few weeks ago, we covered the basics of using Update Requests to collaborate with others.  This week, we'll take these concepts a couple steps further and explain a more advanced way to automate these reminders with an approach that you can tailor to your team and processes: conditional reminders.

Sheet Level Reminders

A quick refresher: to remind your team of upcoming deadlines, it’s easy to set a simple sheet-level reminder to send an email to each assigned team member when tasks are due. However, you can take these to the next level with conditional reminders. Conditional reminders will only send a reminder if the task is not complete

How to Create A Conditional Reminder

1. Add a new column, ‘Reminder Date’, to your sheet.

Use the ‘Reminder Date’ column to generate the dates that will send Reminders to your team, rather than using a ‘Start Date’ or ‘End Date’ column.  When adding this column, make sure to set the column type to ‘Date’.
 

2. Use an IF formula. 

Next, use an IF formula in the ‘Reminder Date’ column that will automatically insert the End Date of a task only if it hasn’t been completed.

Depending on your team’s method for tracking task completion, you can use a few different column types to indicate when something is complete.  The most common scenarios that we encounter are either a checkbox column or a percentage column.

** When you’re using a checkbox column to track task completion, your formula will look something like this: =IF(Complete4 = 0, [End Date]4, "")

This formula will check the value listed in row 4 of the ‘Complete’ column to see if it is equal to 0.  In formulas, 0 represents an un-checked box and 1 represents a checked box.  If the box is unchecked, and the task still needs to be completed, the formula will insert the End Date listed in row 4 into the Reminder Date column.  

** When you’re using a percentage column to track task completion, your formula will look something like this: =IF([% Complete]4 <> 1, [End Date]4, "")

This formula will check the value in row 4 of the ‘% Complete’ column to see if it is “not equal to” (represented by <> ) 1 (which is the decimal equivalent of 100%). If so, it inserts the End Date listed in row 4. Otherwise, it leaves the cell blank.

In both of these scenarios, with these formulas, the End Date of a task will appear automatically in the Reminder Date column as long as the task remains incomplete.

Now that you’ve built an IF formula, apply it to the other rows in the Reminder Date column by clicking on the bottom right corner of the cell and dragging down.

3. Set a new sheet-level Reminder

Now, you can set up a sheet-level Reminder that is driven by the Reminder Date, rather than the task End Date.

If you’d like to streamline the look of your sheet, you can hide your new Reminder Date column to cut down on the amount of information displayed in your sheet.  The formulas, and reminders, will still be working “behind the scenes.”

Hungry for more formula tips?  Check out these pointers for building a nested IF statement, learn how to build a formula to count checkboxes, or watch this quick tutorial to get some other ideas for using formulas in your sheets:


Til next week, dig in and explore!

- Support Team

Comments

What can i do if i need to

What can i do if i need to know when in the end date is near of today or maybe my time was over. I don't know if in the software i can use the conditional: If " end date" is greater than "today (automatically)" ...

Re: What can i do if i need to

Kelly Anthony's picture
Hi there, You can use a formula to stay on top of end dates. In order to help you write the correct one, email our team at [email protected] so we can get a better idea of exactly what you're looking for! Thanks, Kelly

Recurring reminders?

I would like to set up recurring reminders at the row level...the use case is that I have a set of docs, and I want to send a notification every six months that says "hey, you own this doc...make sure it's still valid"

RE: Recurring reminders?

Kelly Anthony's picture
Hi Ken, You can use formulas to help set recurring reminders. For your scenario, I'd recommend adding in two different date columns: a 'reminder' date column and a 'last sent' date column. Add a formula to the 'reminder' date column that automatically adds 180 days to the 'last sent' date column. Then, you can set your Reminder for your team on the 'reminder' date column. Please email us at [email protected] if you'd like more detailed help setting this up! Thanks, Kelly

Recurring reminders

Hi...I'm looking for a way to send reminders to the assigned people of tasks every day when the task is not marked complete and the due date has passed. Is this possible? So, it would be something like task not marked completed and due date is in the past, send a reminder every day when this case is true. Is that do-able?

Re: Recurring reminders

Kelly Anthony's picture
Hi there, Yes, you can set up recurring reminders for your scenario. Please send us an email at [email protected] so that we can help you out! Thanks, Kelly

Recurring reminders

Hi Kelly, I am trying to set up a reminder that will be sent out ONLY if the task is not checked complete AND the due date is in the past. I don't want the assigned to receive reminders for tasks not checked complete if the due date is in the future. Can you help? Thanks, Mike

RE: Recurring reminders

Emily Esposito's picture
Hi Mike -- We'd be happy to help! Please email our Support team at [email protected] and someone will be able to dig into this further and find the best formula for you. Best, Emily

reminders for multiple sheets

can i set up so that it shows ONE reminder for all sheets that need to be addressed,rather than one per sheet???

RE: reminders for multiple sheets

Hi there, Reminders can only be set up on a per sheet basis, so you cannot have a single reminder for multiple sheets. We may be able to help you with a workaround or better solution to fit your needs if you email our support team ([email protected]). Thanks, Keri

reminders that can not be displayed

Sometimes I have more reminders than can be displayed on email or in the reminder calendar, how can I see the rest of them?

Reminders - Set Date & Time

I need to set a reminder for an exact time, not just a date. Is this possible?

Re: Reminders - Set Date & Time

Kelly Anthony's picture
Hi Heather, Unfortunately, you can't set a reminder for an exact time. I've shared your enhancement request with our product team to consider for future updates. Thanks! Kelly

SETTING A REMINDER FOR A SPECIFIC TIME

I am in the trial phase of Smartsheet and being able to set a specific TIME and date for a reminder would be very helpful to me as well. Thank you.

Re: SETTING A REMINDER FOR A SPECIFIC TIME

Kelly Anthony's picture
Hi Maggie, Thanks for your feedback, I've shared it with our product team. Best, Kelly

I created a Conditional

I created a Conditional Reminder that sends a reminder the same day that a task is marked as 100% completed. I want the reminder to be sent immediately when the condition is satisfied. In my case, I'm sending the reminder to the owner of a task that is dependent on the one that was just completed. Is there some other way to notify people when a task is completed?

Re: I created a Conditional

Kelly Anthony's picture
Hi Ed, Today, there isn't a way to automatically notify someone when the previous task is completed. I've shared your scenario with our product team -- helpful feedback as we look into adding more conditional settings in notifications. Thanks! - Kelly

This functionality is also

This functionality is also something I need for my team workflow. I need a row-level conditional reminder. When certain criteria are checked, an automatic request for additional information needs to be sent to specific team members. For example, in my sheet, each row represents a purchase request. When the purchase request is approved, I would like to automatically send the team member who submitted the request a form asking for fund code details. Thanks.

Assigned To Column

how can you set 1 task to 2 people so they both get an email reminder on the due date? I can only set it for 1 person, otherwise you have to let it email everyone the sheet is shared to which is quite onerous.

Reminders time

Hi, It looks very stupid and inconvenient when notifications sends at night (in my case at 3 am). Is there any solution to change this ???????????????????????

Re: Reminders time

Kelly Anthony's picture
Hi Dima, All reminders and notifications are sent between 12am and 5am based on the time zone you're in. Thanks for letting us know that you'd like to be able to customize the timing, I've shared your feedback with our product team. Best, Kelly

Re: Assigned To Column

Kelly Anthony's picture
Hi Ian, Today, you can only assign a task to one person – but you can schedule reminders at the row level for 2 people and not everyone who is shared to your entire sheet. I've shared your request to be able to assign multiple people to a task with our product team, thanks for the feedback! Best, Kelly

How to set 1 task to 2 people

I usually have 2 columns if there is a task that 2 people own. The columns are labeled - Assigned To: & Assigned To (2nd): or Assigned To (IT) & Assigned To (Business) You can create 2 reminders and/or Alerts in the sheet with the recipient in each set as 1 of the Assigned To: columns.

How to Set Up Multi-Day Reminders

I need to set up task reminders that are automatically sent multiple days in a row. The reminder feature is great but it is currently set up to send out on only one day and then it automatically turns off. Unfortunately, a one-day reminder will get forgotten. Is there a simple way of automatically sending out that reminder for multiple days in a row?

Re: How to Set Up Multi-Day Reminders

Kelly Anthony's picture
Hi Renee, It sounds like recurring reminders (http://www.smartsheet.com/blog/support-tip-recurring-reminders) may be helpful for you. Please feel free to email us at [email protected] if you have any other questions! Thanks much, Kelly

Setting up reminders/alerts using RYG balls

Is it possible to send alerts or set up reminders on the row level using the RYG balls as a trigger?

Re: Setting up reminders/alerts using RYG balls

Kelly Anthony's picture
Hi Kyle, Reminders and alerts are only triggered by date columns, there isn't a way to create a relationship between those notifications and RYG balls. Thanks! Kelly

Dependency Emailing

Just learning about SmartSheet and am impressed. Would it be possible to send an email to a user when the predecessor dependency their task is waiting on is complete? I am not sure you can write these macros to leverage the dependency feature. Thank you.

Re: Dependency Emailing

Kelly Anthony's picture
Hi Nat, Glad to hear you're finding Smartsheet useful. While you can schedule a number of notifications and reminders, you can't specify the conditions you described. I've shared your question with our product team to keep in mind while we work on adding conditional notification functionality like this to the product. If you're interested in learning more about what's possible with Smartsheet, we have a great series of webinars that are really helpful in wrapping your arms around the product – http://www.smartsheet.com/webinars. Thanks, and let us know if you have any other questions! Best, Kelly

Update?

Hello - has conditional notification feature been added yet? I think it would be very useful to let people know that they're up to bat on a project. I'm surprised you didn't have this already, seems like a natural fit for the software. Thanks!

RE: Update?

Emily Esposito's picture
Hi Gerry -- Conditional notifications are on our Product Roadmap (http://www.smartsheet.com/product-roadmap). We'll be working on creating advanced notification rules based on criteria like Tasks Assigned to Me or Status is At Risk. We don't have a hard date yet, but it's coming. Best, Emily

Referencing cells from the sheet in the body of a reminder msg

Is it possible – once a reminder has been triggered to reference a cell in the message for example: *** Column headers - | Unit number | move out date | Construction Start date | *** *** Row 1 data - | 101 | 01/16/2015 | 01/17/2015 | +++ The move out date would trigger the reminder – say 7 days prior – the reminder would be sent to the appropriate parties and would read "Unit 101 is scheduled for move out 01/16/2015 and we are ready to begin construction 01/17/2015" I imagine the format would look something like: Unit {[Unit Number]1} is scheduled for move out {[move out date]1} and we are ready to begin construction {[Construction Start Date]1} Any advice appreciated. Thanks, J

Re: Referencing cells from the sheet in the body of a reminder

Kelly Anthony's picture
Hi Jay, When you schedule a reminder, all of the information in that row is included in the reminder email – so the recipient will be able to see the scheduled move out date and the construction date. You can always customize the reminder message with specific instructions like "see the move out date and the construction start date." Let us know if you have any other questions! Thanks, Kelly

Weekly Due Dates for Conditional Formatting

How do I set up a conditional format to show if a modified date "not" in the last (days). I only see options for a auto+numbered/modified date as "in the last (days)" or "in the next (days)" and I need to set a condition that if something has not been modified for several days it will have a conditional format applied showing this. Thank you

Auto notification on addition of a new row

Hi, Is there a way to send an automatic notification to someone (listed in a contact list type column), as soon as there is a new row entry in smartsheet?

RE: Auto notifications on addition of a new row

Emily Esposito's picture
Hi Amit, thank you for your question! You can set an automatic notification to someone as soon as anything changes in your sheet, which would include a new row entry. However, you cannot currently set a notification for only a new row. For more information about notifications, see this help article: http://help.smartsheet.com/customer/portal/articles/542904-using-notifications. I have also passed along this comment to our Product team. Best, Emily

Reminders - Specific times?

Can you advise if the specific time option to set a reminder for has been added to the most recent update? Is there anyway of knowing if this is a work in progress as I have a lot of tasks that due to just daily reminders options I have to leave in my email inbox to use as a reminder to get back to at a later time until complete. I'd really like to be able to add a specific time or even some how link to iCal to pop up a reminder at the specified time. Thanks!

RE: Reminders - Specific Times?

Emily Esposito's picture
Hi Saul, Unfortunately, you can't set a reminder for an exact time. I've shared your enhancement request with our product team to consider for future updates. Thanks! Emily

Sheet level reminder

I used an IF formula in the ‘Reminder Date’ column that will automatically insert the End Date of a task only if it hasn’t been completed. If I set up a sheet-level Reminder that is driven by the Reminder Date, will the email reminder only go to the person assigned to that row on that particular date or will everyone get the reminder?

RE: Sheet level reminder

Emily Esposito's picture
Hi Lia -- If you are a sheet owner or an Admin, you can choose who receives the reminder. To set a sheet-level reminder, click Alerts at the bottom of the sheet, then click "New Reminder." By default, you are selected to receive this reminder. But, you can change who receives it by clicking on your name and choosing the person from the list. Best, Emily

Reminders that can be sent monthly quaterly semiannual etc.

I want to know if it is possible to set reminders monthly,quarterly, etc that can be marked completed BUT then have them automatically reset again for the next month or quarter?

RE: Reminders that can be sent monthly, quarterly, semiannual

Emily Esposito's picture
Hi Theresa -- At this time, there is not a way to do this in Smartsheet, however there is a workaround that may work for you. You could insert a completion date column and a reminder date column and then use a formula to add a date that's 30 days in the future (for one month) to the reminder date column. Please email our Support team at [email protected] for more assistance/information on this if needed. Best, Emily

Want to set up a Reminder dependent upon a change

I am looking to prompt a Reminder email to be sent out once a certain cell in that row has changed. I had originally set up a Notification, as the functionality works perfectly for what I need, BUT there is no opportunity to link a specific message to the team member in Notifications, like there is in Reminders. Therefore, the team members would get the email, but not really understand why, or what they are expected to do. I know I can set up a Reminder based on a date, but the team member filling in the field prompting the Reminder would prefer to just fill in the field and not have to date it as well. Is there a way to use a hidden column that will be populated with the date that a modification was made - a modification JUST in a single column, not for the entire row - that can then be used to prompt the Reminder. (I also know that auto-generated dates can't be used for Reminders, but I could do a formula in ANOTHER column that will fill in the date based on the auto-generated date in the first hidden column. It's just getting that single-column modification date generated that I'm having difficulty with.) Thank you!

RE: Want to set up a reminder dependent upon a change

Emily Esposito's picture
Hi Angelene, please email our Support team at [email protected] so we can find the best solution for you! Best, Emily

Reminders on Reports

Is it possible to add reminders to reports? I have several staff that work within a sheet, but only want to send a reminder to the person who is assigned the task. How can I do this without having to recreate the master sheet for each person? Thank you for the help. - Anna

RE: Reminders on Reports

Emily Esposito's picture
Hi Anna -- you can do this with a sheet-level reminder. Click Alerts > New Reminder and set the reminder so that it sends an email to the assigned column. See step #1 here for more info: http://help.smartsheet.com/customer/portal/articles/542913. Best, Emily

Reminders

I would also like to send alerts/reminders if a x amount of days have passed since last Modified (Date). Is this possible?

RE: Reminders

Emily Esposito's picture
Hi Derek -- Yes! Please email our Support team at [email protected] and we'd be happy to help. Best, Emily

Conditional Reminder that is not a checkbox or percentage

Hi, I was able to successfully use the conditional reminders on a couple sheets that use checkbox or % Complete. I have an issues log that uses Open, Closed or Escalated as the status. I want to create the conditional reminder to populate the Reminder Date column if the status is Open or Escalated. If the status is Closed I want the field to be empty so as not to send reminder. I tried a number of different formulas but get #UNPARSEABLE error. Any suggestions?

RE: Conditional Reminder that is not a checkbox or percentage

Emily Esposito's picture
Hi Bob -- Please email our Support team at [email protected] and we'd be happy to lend a hand! Best, Emily

Pages

Add new comment