Roll over Monthly checklist

Bharat_Ahuja

I am looking to create a month-end checklist which has list of activities and due dates and tracks the completion status driven by user inputs. What is the best way to a) archive the completed checklist and b) roll over the checklist month over month. The fields are consistent month-over-month with 80% pre-populated in the template and 20% needs to be blank for the users to fill-in e.g. completed on date, reviewed date, remarks etc.

MCorbin

If I'm understanding what you want to do:

You have a template list for the checklist, that's 80% always the same.

At the end of each month, you want to have the checklist there, with the 20% of columns blank for someone to fill out.

What do you want to do with the previous month's data? Do you want to keep that? Or just clear it?

There are 2 ways you can handle this:

1) Keep the Master List (check list rows with the blank fields blank) in a Master Sheet. Create a 2nd "Working" sheet that is a copy of the Master list.

• the master sheet should have a date column with the date formula =today()
• 主单应下ing automations
• - Lock Rows at 12:00 AM Daily
• - Unlock Rows at 1:00 AM Daily
• - On the last day of the month, (after 1 AM), copy all of the rows to your "Working" sheet (which is a copy of the master.
• Use the Working Sheet to fill in the monthly checklist data (the Master sheet always has those fields blank).
• - When the rows are copied from the Master sheet, they'll copy with the last day of the month in your date column because of your today() formula and the Lock/Unlock row automation (which refreshes it).

2) Keep your checklist in 1 sheet. Create a 2nd Archive sheet that is a copy of the master list.

• Have your users fill out the checklist.
• Create the following automations:
• - after the checklist updates are due, and before the next set of checklists are needed, copy all rows to the Archive sheet.
• - After the rows are copied, have an automation clear the 20% of fields that need to be updated by your users.
• From there, you can have users updating the same rows month after month and your Archive sheet becomes your historical record.

Bharat_Ahuja

If I'm understanding what you want to do:<\/p>

You have a template list for the checklist, that's 80% always the same.<\/p>

At the end of each month, you want to have the checklist there, with the 20% of columns blank for someone to fill out.<\/p>

What do you want to do with the previous month's data? Do you want to keep that? Or just clear it?<\/p>

There are 2 ways you can handle this:<\/p>

1) Keep the Master List (check list rows with the blank fields blank) in a Master Sheet. Create a 2nd "Working" sheet that is a copy of the Master list.<\/p>

• the master sheet should have a date column with the date formula =today()<\/li>
• The master sheet should have the following automations<\/li>
• - Lock Rows at 12:00 AM Daily<\/li>
• - Unlock Rows at 1:00 AM Daily<\/li>
• - On the last day of the month, (after 1 AM), copy all of the rows to your "Working" sheet (which is a copy of the master.<\/li>
• Use the Working Sheet to fill in the monthly checklist data (the Master sheet always has those fields blank).<\/li>
• - When the rows are copied from the Master sheet, they'll copy with the last day of the month in your date column because of your today() formula and the Lock\/Unlock row automation (which refreshes it).<\/li><\/ul>

  2) Keep your checklist in 1 sheet. Create a 2nd Archive sheet that is a copy of the master list.<\/p>

  • Have your users fill out the checklist. <\/li>
  • Create the following automations:<\/li>
  • - after the checklist updates are due, and before the next set of checklists are needed, copy all rows to the Archive sheet.<\/li>
  • - After the rows are copied, have an automation clear the 20% of fields that need to be updated by your users.<\/li>
  • From there, you can have users updating the same rows month after month and your Archive sheet becomes your historical record.<\/li><\/ul>","bodyRaw":"[{\"insert\":\"If I'm understanding what you want to do:\\nYou have a template list for the checklist, that's 80% always the same.\\nAt the end of each month, you want to have the checklist there, with the 20% of columns blank for someone to fill out.\\nWhat do you want to do with the previous month's data? Do you want to keep that? Or just clear it?\\n\\nThere are 2 ways you can handle this:\\n1) Keep the Master List (check list rows with the blank fields blank) in a Master Sheet. Create a 2nd \\\"Working\\\" sheet that is a copy of the Master list.\\nthe master sheet should have a date column with the date formula =today()\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"The master sheet should have the following automations\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\" - Lock Rows at 12:00 AM Daily\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\" - Unlock Rows at 1:00 AM Daily\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\" - On the last day of the month, (after 1 AM), copy all of the rows to your \\\"Working\\\" sheet (which is a copy of the master.\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Use the Working Sheet to fill in the monthly checklist data (the Master sheet always has those fields blank).\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\" - When the rows are copied from the Master sheet, they'll copy with the last day of the month in your date column because of your today() formula and the Lock\\\/Unlock row automation (which refreshes it).\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"2) Keep your checklist in 1 sheet. Create a 2nd Archive sheet that is a copy of the master list.\\nHave your users fill out the checklist. \"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Create the following automations:\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\" - after the checklist updates are due, and before the next set of checklists are needed, copy all rows to the Archive sheet.\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\" - After the rows are copied, have an automation clear the 20% of fields that need to be updated by your users.\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"From there, you can have users updating the same rows month after month and your Archive sheet becomes your historical record.\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2022-09-07T22:18:58+00:00","insertUser":{"userID":89971,"name":"MCorbin","title":"Manager, HIT Projects","url":"https:\/\/community.smartsheet.com\/profile\/MCorbin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/80PUK21NFANO\/nKJOX5CKCTKS0.jpeg","dateLastActive":"2023-08-25T16:34:18+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/342742#Comment_342742","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/342742#Comment_342742

Thank you - I guess Option 2 would work best for me given will have to retain the monthly completed checklist and have a user filled up column for the month and have automation to fill up that column for a structured archival