Weekly snapshot of related sheet info

Frank Falco

I have an Issues sheet that lists all issues raised during a project, when they are raised and when closed. It has a checkbox column that is checked when these issues need to be visible in a weekly report, which will of course change from week to week. There is also a Weekly Report sheet that the Project Manager adds a row to each week, and it has a date column (along with other status columns etc) that is the week that the report pertains to.

I use a JOIN(COLLECT({Issues Desc}, {Issues In Weekly Report}, 1) to collect all the issue descriptions in the Issues sheet that have the In Weekly Report check box checked. So when they add a row for their weekly report it collects the issues data into a column via a column script with the join(collect.. script.

I want to store the issues that were in play for the week the Weekly Report row was created.

I have tried various combinations of automation, date ranges and checkboxes to try to automate this but without an automation mechanism to copy the column value from one row to another for the Weekly Report row I cannot work out a way to do this without replacing all the previous weekly issues with the current one.

Can someone suggest a way to automate so that it only updates the current week row in the weekly report and does not replace the previous weeks?

Andrée Starå

Hi @Frank Falco

I hope you're well and safe!

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!