✭✭✭✭✭✭

Hi all,

Our director is hoping to start a Smartsheet that lists our recurring annual/semi-annual/biennial tasks, and sends reminders as each due date approaches. I don't anticipate it being difficult, but...

My "work smarter, not harder" mind wants to find a way to automate the recurrence. For example, if our annual audit is due on April 30 every year and the "done" column is checked, I'd ideally like it to add a new row for that task with next year's due date on it.

Any thoughts? Ideas from someone who already does something like this? Any ideas you have are greatly appreciated.

Thanks,

Heather

• ✭✭✭✭✭✭

I would recommend first setting up a sheet with each task listed once, then "Save as New" to duplicate it. The first sheet will be your "Working Sheet" and the second sheet will be the "Dup Sheet".

On the Dup Sheet, we are going to add a text/number column and use this formula:

=COUNTIFS({Working Sheet Task Name Column}, @cell = [Task Name]@row, {Working Sheet Done Column}, @cell = 1)

This will count how many of that particular task has been checked off as "Done".

Also on the Dup sheet we want another text/number column for you to record the frequency of the corresponding task.

In the Due Date column of the Dup Sheet, we want to use a formula to indicate the next due date based on the frequency and referencing the last inserted Due Date on the Working Sheet (MAX).

To pull the most recent due date from the Working Sheet for each task:

=MAX(COLLECT({Working Sheet Due Date Column}, {Working Sheet Task Name Column}, @cell = [Task Name]@row))

Now we can work on the formula based on the frequency. We know we need to use a DATE function so that it outputs a date.

=DATE(yyyy, mm, dd)

The day should be easy enough. We just pull the day from the MAX/COLLECT and drop it in.

=DATE(yyyy, mm, DAY(MAX(COLLECT(.......))))

The month and year though are going to be a little bit trickier. We need to output for 6 months, 12 months, or 24 months depending on your frequency.

12 and 24 months should be easy enough because we are adding either one or two years and pulling the month from the MAX/COLLECT

Year:

YEAR(MAX(COLLECT(.....))) + IF(Frequency@row = "Annual", 1, IF(Frequency@row = "Biannual", 2))

Month:

MONTH(MAX(COLLECT(.....)))

The tricky part is going to be the semi-annual. We can accommodate moving into the next year by saying that if the month is greater than 6 then add 1 to the year and subtract 6 from the month, but if the month is less than or equal to 6 then we can leave the year alone and just add 6 to the month.

Year:

YEAR(MAX(COLLECT(.....))) + IF(Frequency@row = "Annual", 1, IF(Frequency@row = "Bi-Annual", 2, IF(AND(MONTH(MAX(COLLECT(.....)))> 6, Frequency@row = "Semi-Annual"), 1)))

MONTH:

MONTH(MAX(COLLECT(.....))) + IF(Frequency@row = "Semi-Annual", IF(MONTH(MAX(COLLECT(.....)))> 6, -6, 6))

Now that we have that figured out, we can drop it into our DATE function and end up with something like this...

=DATE(YEAR(MAX(COLLECT(.....))) + IF(Frequency@row = "Annual", 1, IF(Frequency@row = "Bi-Annual", 2, IF(AND(MONTH(MAX(COLLECT(.....)))> 6, Frequency@row = "Semi-Annual"), 1))), MONTH(MAX(COLLECT(.....))) + IF(Frequency@row = "Semi-Annual", IF(MONTH(MAX(COLLECT(.....)))> 6, -6, 6)), DAY(MAX(COLLECT(.......))))

Now we are taking the most recent date from the Working Sheet, adding the appropriate number of months and/or years to it, and recording it in the Due Date column of the Dup Sheet.

Last but not least, we set up a Copy Row automation on the Dup sheet that says whenever that Count column on the Dup Sheet (the one we added in the beginning) changes to any value, copy that row over to the Working Sheet. Since the Copy Row automation only copies over static data, it will capture that date from the formula and insert a new row on the Working Sheet with this static date of when it is due next. Now that the MAX date for that task has been updated on the Working Sheet, the Due Date in the Dup Sheet will automatically update and then will sit and wait until there is another box checked in the Done column on the Working Sheet for that task which will then trigger another Copy Row automation, so on and so forth.

To set up the alerts for upcoming tasks, you would set up the Automations on the Working Sheet on the Working Sheet.

Also on the working sheet... When the Copy Row automation triggers, it will automatically copy over the row with ALL columns from the Dup Sheet. This includes the Count column. You can either leave this visible on the Working Sheet to show how many times that particular task has been completed, or you can hide it for cleanliness. That is up to you.

«1

• ✭✭✭✭✭✭

I would recommend first setting up a sheet with each task listed once, then "Save as New" to duplicate it. The first sheet will be your "Working Sheet" and the second sheet will be the "Dup Sheet".

On the Dup Sheet, we are going to add a text/number column and use this formula:

=COUNTIFS({Working Sheet Task Name Column}, @cell = [Task Name]@row, {Working Sheet Done Column}, @cell = 1)

This will count how many of that particular task has been checked off as "Done".

Also on the Dup sheet we want another text/number column for you to record the frequency of the corresponding task.

In the Due Date column of the Dup Sheet, we want to use a formula to indicate the next due date based on the frequency and referencing the last inserted Due Date on the Working Sheet (MAX).

To pull the most recent due date from the Working Sheet for each task:

=MAX(COLLECT({Working Sheet Due Date Column}, {Working Sheet Task Name Column}, @cell = [Task Name]@row))

Now we can work on the formula based on the frequency. We know we need to use a DATE function so that it outputs a date.

=DATE(yyyy, mm, dd)

The day should be easy enough. We just pull the day from the MAX/COLLECT and drop it in.

=DATE(yyyy, mm, DAY(MAX(COLLECT(.......))))

The month and year though are going to be a little bit trickier. We need to output for 6 months, 12 months, or 24 months depending on your frequency.

12 and 24 months should be easy enough because we are adding either one or two years and pulling the month from the MAX/COLLECT

Year:

YEAR(MAX(COLLECT(.....))) + IF(Frequency@row = "Annual", 1, IF(Frequency@row = "Biannual", 2))

Month:

MONTH(MAX(COLLECT(.....)))

The tricky part is going to be the semi-annual. We can accommodate moving into the next year by saying that if the month is greater than 6 then add 1 to the year and subtract 6 from the month, but if the month is less than or equal to 6 then we can leave the year alone and just add 6 to the month.

Year:

YEAR(MAX(COLLECT(.....))) + IF(Frequency@row = "Annual", 1, IF(Frequency@row = "Bi-Annual", 2, IF(AND(MONTH(MAX(COLLECT(.....)))> 6, Frequency@row = "Semi-Annual"), 1)))

MONTH:

MONTH(MAX(COLLECT(.....))) + IF(Frequency@row = "Semi-Annual", IF(MONTH(MAX(COLLECT(.....)))> 6, -6, 6))

Now that we have that figured out, we can drop it into our DATE function and end up with something like this...

=DATE(YEAR(MAX(COLLECT(.....))) + IF(Frequency@row = "Annual", 1, IF(Frequency@row = "Bi-Annual", 2, IF(AND(MONTH(MAX(COLLECT(.....)))> 6, Frequency@row = "Semi-Annual"), 1))), MONTH(MAX(COLLECT(.....))) + IF(Frequency@row = "Semi-Annual", IF(MONTH(MAX(COLLECT(.....)))> 6, -6, 6)), DAY(MAX(COLLECT(.......))))

Now we are taking the most recent date from the Working Sheet, adding the appropriate number of months and/or years to it, and recording it in the Due Date column of the Dup Sheet.

Last but not least, we set up a Copy Row automation on the Dup sheet that says whenever that Count column on the Dup Sheet (the one we added in the beginning) changes to any value, copy that row over to the Working Sheet. Since the Copy Row automation only copies over static data, it will capture that date from the formula and insert a new row on the Working Sheet with this static date of when it is due next. Now that the MAX date for that task has been updated on the Working Sheet, the Due Date in the Dup Sheet will automatically update and then will sit and wait until there is another box checked in the Done column on the Working Sheet for that task which will then trigger another Copy Row automation, so on and so forth.

To set up the alerts for upcoming tasks, you would set up the Automations on the Working Sheet on the Working Sheet.

Also on the working sheet... When the Copy Row automation triggers, it will automatically copy over the row with ALL columns from the Dup Sheet. This includes the Count column. You can either leave this visible on the Working Sheet to show how many times that particular task has been completed, or you can hide it for cleanliness. That is up to you.

• ✭✭✭✭✭✭

@Paul Newcome, thank you so much for your in-depth response! I'll try it out. I mocked something up yesterday that's similar, but I couldn't get the recurrence to work quite right.

I appreciate your help!

Best,

Heather

• ✭✭✭✭✭✭

happy to help. 👍️

Feel free to keep me posted or ask additional questions in case I didn't explain certain steps very clearly or you need other options/tweaking done.

• ✭✭✭✭✭✭

Okay - I'm really close on this! I've got all of the formulas working properly.

For reference, this is what my due date column ended up being on the Dup sheet:

I ended up adding a column for months and years of recurrence based on the recurrence selected in the drop down menu, so it will automatically put a 1 in Years for Annual, or a 0 in Years and a 3 in Month for quarterly, etc. I then made the formula read that, if the month of the collected max date plus the Months column was greater than 13, then the year would be the sum of (year of collected max date, years column, +1), and the month would be the sum of (month of collected max date, months column, -12). This flowed a little more easily in my mind, and I don't mind having extra columns to hide.

So where I stand right now, I've got almost everything working. The only thing I'm having trouble with is the copy row automation. Here's what I have it set to:

For some reason, it's not copying the row over to the Working sheet when I mark something as "done" in the working sheet. (The formula does, however, update the CountDone column to 1.)

In an effort to keep things clean, I do also plan to have items marked as Done on the Working sheet transferred over to an Archive sheet. I've set that automation up, but added another column to count how many times that task name is listed in the sheet. That way, if I get the copy from Dup to Working automation functioning, it won't move the row marked as done over to the Archive until the Dup sheet has copied the new row over. (Does any of this make sense whatsoever?)

Again, I appreciate your help with this! I'm hoping you'll see a glaring error in my copy row automation, and it'll be an easy fix.

• ✭✭✭✭✭✭

Unfortunately I don't see any issues with the Automation. Maybe try inserting a condition of something like "Task Name is not blank". It shouldn't make a difference, but maybe having something in all portions of the Automation will help.

You may want to be careful with the Archive automation. I would suggest setting it with an additional checkbox column that basically says

=IF(AND(Done@row = 1, COUNTIFS([Task Name]:[Task Name], @cell = [Task Name]@row) > 1), 1)

This will check the box only after the row has been marked off as checked and the new row has been added. You can then set up your Archive automation to run based on this particular checkbox. That just adds a second layer of validation to ensure the new row is brought over from the Dup sheet first before archiving the completed so that the Count Column on the Dup Sheet has a chance to change and trigger the copy row.

I also wonder if the Archive automation could trigger a second Copy Row automation from the Dup sheet since when the row is archived, it will change the count of how many tasks are done. We may want to adjust the Copy Row trigger to have it set so that it only copies over when the count column changes to "1". Since you are archiving, you should never have a 2, and this will keep it from triggering again when the count changes to zero. Does that part make sense?

• ✭✭✭✭✭✭
edited 04/14/21

@Paul Newcome Yes - the extra count task names column was something I already added, to avoid the row being moved to the archive before the Dup sheet CountDone formula was updated.

Good thinking on setting the CountDone automation to 1. It's still not working, though it worked just briefly (amidst all of my trial and error, I wasn't able to tell which change to the automation actually made it work). I've put in a ticket with support to see if they have any suggestions, so we'll see. Meanwhile, I'll just keep trying-and-error-ing. :)

I updated my director on the status of it, and she said that I work miracles. I had to give you the credit, though, because I couldn't have done this without your help!

• ✭✭✭✭✭✭
edited 04/14/21

Do you think this could have something to do with it? I have a DueDate column in both the Dup and the Working sheets...

• ✭✭✭✭✭✭

Haha. No worries on the credit (unless your director wants to send me a check). Hahaha!!

That note makes perfect sense too. I even knew (but apparently forgot) about that too. Ugh. Ok. Lets do some more thinking and brainstorming. Hmm...

It can't be triggered by a cell with a cross sheet reference/cell link in it, but I wonder if it follows through to a cell that references the formula.

So you have the formula in the Count column. What if we have a "Trigger" column that just has

=Count@row

I wonder if that would work for triggering it since the column used for the trigger doesn't contain a cross sheet reference.

• ✭✭✭✭✭✭

Eureka! Found the problem for sure: I had the DueDate column in Dup set as a column formula. If I switch it to a cell formula, it fixes the automation.

Now all of my automations are working beautifully. I'm only having one other issue, but I think it's minor enough to be handled manually. When new tasks are added to the Working sheet, I have an automation copy it to the Dup sheet if it's new (i.e., doesn't already show up on the Dup sheet - thanks to a helper column). When rows are copied from Working to Dup, the DueDate formula doesn't copy down; it is just text.

I think, for the time being, I'll just copy that column down manually. Once we have this set up and running, adding tasks should be a VERY rare occurrence.

As always, I GREATLY appreciate your wisdom and help with this. I am so excited to show my team the magic I have worked (with your help!). I hope you have a great rest of your week.

With much appreciation,

Heather

• ✭✭✭✭✭✭

Great! I'm glad you were able to get it figured out.

The [Due Date] formula not copying down is to be expected since the Copy Row automation that is bringing that new task over from the Working Sheet is treated the same as manually added data.

One option for managing to this would be to set up an Alert on the Dup sheet triggered by a new row being added. This way you don't have to worry about checking it regularly or missing a new row to drag the formula into. You'll get an automatic notification that it needs to be done.

And as always... Happy to help. 👍️😊

P.S. Take all the credit for yourself, get a bonus, then pay it forward. Haha

• ✭✭✭✭✭✭

Yep - that's what I was thinking about doing. Just set an alert to tell me to copy the formula down - that's simple enough!

Ha! If a bonus comes my way because of this process, I'd gladly share a cut of it with you. 😄

• ✭✭

@Heather Duff I have a client that is trying to do the same exact thing but I am a bit of a novice when it comes to Smartsheet, can you send a screenshot or template or your sheet to see if it would work for our needs. The really long due date formula is making my face melt!

Any help would be greatly appreciated.

Thanks

• @Paul Newcome - Can you please help me out in here? Sorry for asking such a beginner question. I'm trying to create the frequency formula and receiving an error(#INCORRECT ARGUMENT SET)

This is what I tried:

1

=DATE(YEAR(MAX(COLLECT(DueDate1))) + IF(Schedule1 = "Annual", 1, IF(Schedule1 = "Bi-Annual", 2, IF(AND(MONTH(MAX(COLLECT(DueDate1))) > 6, Schedule1 = "Semi-Annual"), 1))), MONTH(MAX(COLLECT(DueDate1))) + IF(Schedule1 = "Semi-Annual", IF(MONTH(MAX(COLLECT(DueDate1))) > 6, -6, 6)), DAY(MAX(COLLECT(DueDate1))))

2

=DATE(YEAR(MAX(COLLECT([DueDate]1))) + IF(Schedule@row = "Annually", 1, IF(Schedule@row = "Monthly", 12, IF(AND(MONTH(MAX(COLLECT([DueDate]1)))> 6, Schedule@row = "Semi-Annual"), 1))), MONTH(MAX(COLLECT([DueDate]1))) + IF(Schedule@row = "Semi-Annual", IF(MONTH(MAX(COLLECT([DueDate]1)))> 6, -6, 6)), DAY(MAX(COLLECT([DueDate]1))))

DueDate - is the column with the =MAX(COLLECT({Compliance Tasks list Range 1}, {Compliance Tasks list Range 2}, Tasks1))

Schedule - is the column with the frequency

• ✭✭✭✭✭✭

@Odednavon Try rewriting is without using the MAX/COLLECT in the date function.

=DATE(YEAR([Due Date]@row) + IF(Schedule@row = "Annually", 1, .......................................

• @Paul Newcome Great, thanks. It works now.