#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Conditional reminder

Options
edited 12/09/19

What is the formula that will send me a reminder if a task due date is in the past and the progress ball is red?  I have my team track task progress with the RYG balls. If a task has a due date and it hasn't been completed by that due date (as stated by the red RYG ball) I would like to receive an email or alert telling me that the task is past due and still incomplete.

• ✭✭✭✭✭
Options

Dion:

Something like the below formulation will work, you can tweak it to your exact needs and dump it into a hidden column.  In addition, it needs a checkbox column and a color ball column, so if the due date is less than today and the checkbox is open, then returns the result "red".  Instruct your color ball column to read the result of the formula.   I would lokc the color ball column.

=(IF(AND([Due Date]4 >TODAY(), IF(Checkbox4, "Red")))

Once you have that under control, then use the Alerts gadget on the bottom ribbon to send out a ping when the ball turns red.  Sorry, it's on a sheetwide basis only,  not row by row.  There may be other ways, but this is what came immeidately to mind.

• ✭✭✭✭✭✭
Options

Dion,

Slightly diferent to Jeremy but the same principle.

=IF(AND([Due Date]4 >=TODAY(), [Progress]4= "Red"),[Overdue Date]4=[Due Date]4,"" )