Update triggered by day of month, repeat daily until complete

Sending a single update request does not always generate a response because many people are not good about answering all their emails, and then it gets lost in old email. I'd like to generate a daily update request that begins on a set day each month (like the 14th) and repeats daily until the person completes the update request. So far my only solution is a manual reset of a trigger to generate a daily update request, but that's too complicated for this smartsheet because the update request needs to start on different days for different lines. Some need to start on the 20th, others on the 25th. Is there a formula I can use to change a status field based on the day of the month, then change it again after the update response? I thought I could do it with the TODAY and DAY functions, but TODAY only updates when the sheet is revised or saved. A dormant sheet would miss the trigger.

Find more posts tagged with

Construction

Finance

Update Requests

Comments

Andrée Starå

Hi Dan,

There are a few ways to set it up!

Is it always the same cell(s) that are updated? Are they always empty before the update?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

DanR

Hi Andree. This is a monthly invoice tracking smartsheet. I have 5 categories of invoices that are processed slightly differently. The best example is the standard AIA invoice that bills on the 20th, or the 25th, or the 30th of the month. Five days before the billing deadline of, say the 20th, I'd like to start the daily update request which repeats until the PM responds. The PM is supposed to update their account in the company accounting software, then return to the update form and update the "Monthly Invoice" field using the dropdown list: "Ready to Invoice" or "Do Not Invoice."

The "Monthly Invoice" field will be manually reset each month. I realize this can be used as a trigger, but it would have to be done on the day the daily messages need to start. (I'm currently using it as a condition of the trigger.) Since I have numerous categories intermixed on the sheet, this would be complicated. While I could use filters to make it simpler, it still means someone has to be responsible to actually do it on that particular day. But with vacations and sick days, etc, I can't count on that. I want to completely remove the human trigger. They're not reliable.

The automation allows me to do a monthly update request on one particular day of the month. But that's only one email. I can set it to daily, but then I need something in the sheet to trigger the conditions. I can't find a function that will trigger a change on a selected day of every month.

I shared the sheet to you. I hope this more detailed description helps. Thanks.

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…
Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…