How to set up columns to track status by week and not erase when moving to next week?

brandon1

I have a project with tasks organized by rows. There is a column for status of each task. I've created columns for each week progressing into the future where I want to log the status at the end of each week in each column. My issue is that once we are in a new week, it leaves the previous column (week) blank.

How do I keep the data logged in each week?
Here is the formula I use in each week column. For reference, row 244 is the week start date, and row 245 is the week end date

=IF(AND($[Current Date]@row >= [1/5/25]$244, $[Current Date]@row <= [1/5/25]$245), $[Workflow / Status]@row, "")

Paul Newcome

You would need to set up a copy row automation that runs weekly and copies the rows over to a second sheet. You would then need to use an INDEX/COLLECT to bring over the appropriate week's status for each task from the static data on the second sheet.

brandon1

That's a great idea! I will try this. Thank you

Paul Newcome

Happy to help. 👍️

brandon1

Okay, I've set it up and have been messing with it and I think I'm still having an issue with collecting the data. The formula works for when the IF statement is true, but gives #INVALID VALUE for weeks in the past where IF statement is false.

{STATIC Calendar View} is the extra sheet created. Range 2 is the current week column where the status was copied. Range 4 is a column that displays modified date, so that I can collect the copied data from the end of that week.

=IF(AND($[Current Date]@row >= $[1/5/25]244, [Current Date]@row <= $[1/5/25]245), $[Workflow / Status]@row, INDEX(COLLECT({STATIC Calendar View Range 2}, {STATIC Calendar View Range 4}, <=$[1/5/25]245), 1))

Paul Newcome

Are you able to provide screenshots?

brandon1

brandon1

Got it figured out Paul! I edited some of the references so hopefully it makes more sense. I needed to add another criteria to the collect and identify which row to pull the data from

=IFERROR(IF(AND($[Current Date]@row >= [1/5/25]$244, $[Current Date]@row <= [1/5/25]$245), $[Workflow / Status]@row, INDEX(COLLECT({STATIC Calendar View 1/5/25}, {STATIC Calendar View Modified}, <=[1/5/25]$245, {STATIC Calendar View Primary}, $[Primary Column]@row), 1)), "DATA NOT FOUND")

Paul Newcome

Glad you were able to get it sorted.