Conditional reminder

Dion Chapman

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.

Jeremy Myers

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.