Notification without changes in row or column
We have a sheet that track onboarding activity for new clients. Two date columns in the sheet are Follow-up Due and Follow-up Actual. I would like to send a notification or reminder to the assigned employee on the Follow-up due date if the Follow-up Actual is blank so they are only receiving notifications about open items. There is no change to a row or column to initiate a notification and a reminder does not allow for an 'if' statement. Any thoughts on how to accomplish this?
Comments
-
I use a helper column. I create a hidden date column and use the formula formula to produce. =IF(ISBLANK([Actual Due Date]@row), [Follow Up Due]@row, "Completed")
See if that works for you. Then you build your reminder around the helper column. if there is no Actual Due Date then you will have a date to pull a reminder from. If there is an actual date the word completed populates the date field and the reminder will not fire.
Hope that helps!
-
It is unfortunate that we still do not have the Notification features for Reminders. Very.
That said, to accomplish this, I create a [Reminder Date] column. The column uses a formula to populate the date. For example:
=IF([Open Item]@row, MIN([Due Date]@row, DATEONLY(Created@row) + 14), "")
This will be blank if the item not considered open (there are more than "complete" in this system).
If [Open Item] is checked, then this reminder occurs the earlier of the [Due Date] or 14 days after the task is created.
The Reminder then keys off that date.
If the Reminder is based on some formula using TODAY(), then you'll need to account for that. I used to use Zapier for that:
https://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/
I hope that helps.
Craig
UPDATE: Looks like Mike and I were typing at the same time and that he types faster than I do.
-
Thank you! I created a new hidden column and it works!
-
Glad the solution works for you.
@Craig. Two heads are better than 1!
