Weekly snapshot of related sheet info

Frank Falco
Frank Falco ✭✭✭✭✭✭
edited 11/22/21 in Formulas and Functions

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?


✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!