How to automate or move cell values when a date is in the past week?

engrmarkgerald

Here is a tracker of a 10-weeks look ahead of number of hours to spend in project from Monday-Friday which are highlighted in blue cells.

The Start Week and End week changed if a date is based on current week. Which means 9-Jun-2024 will be the current week by next week , correct? The problem now is the no. of hours is static and the idea is to move the cell values from 2nd week -10 week look ahead upwards which will overwrite one row above it where the 10 week look ahead will now be cells to be updated every current week was completed?

Thank you in advance.

Paul Newcome

My suggestion would be to have all weeks listed in a master sheet as static rows. Then create a helper column with a formula to flag current week, 2nd, 3rd, 4th week, etc.. Finally you would create a report filtered to only show rows that have this helper column flagged with the week number.

Paul Newcome

My suggestion would be to have all weeks listed in a master sheet as static rows. Then create a helper column with a formula to flag current week, 2nd, 3rd, 4th week, etc.. Finally you would create a report filtered to only show rows that have this helper column flagged with the week number.

engrmarkgerald

Hi Paul, thank you for your answer. Yes, i agree on that since I've created a tracker like that for all dates until end of the year, however, this will create more rows in the sheet especially if there will be a lot of in progress projects. I'll duplicate my current tracker and try your suggestion as well and check what is more efficient to the team instead of copy and paste of 2nd-look ahead to 10th upwards.

Cheers!!!!

Many thanks