I want to duplicate a cell value based on a date action

Tofu_Now

For example, every Monday at 7:00am, I want the value of a cell ( C1) to be "duplicated" and overwrite the value of another cell (E1) on the same row. I want this to effect the entire sheet for these columns.

The idea is that column C tracks the "Actual %" of a project and column E tracks the "Previous Weeks %". Column E will update at the start of each week.

jessica.smith

@Tofu_Now

Here is a conceptual idea to try.

Add a mechanism to track the current week on your project sheet. This could be a =weeknumber(today()) formula or a date column that returns the Sunday of the current week or anything like that.

Set up a helper sheet and create a weekly automation on your project sheet to automatically copy rows from the project sheet to the helper sheet once a week.

Set up at least two cross-sheet references on the project sheet to reference the copied "Actual %" column and the copied "Week" column on the helper sheet.

Then in the "Previos Weeks %" column, use a column formula to Index/Match the previous week's "Actual %" from the helper sheet. It would look something like this: =INDEX({Actual}, MATCH(Week@row - 1, {Week}, 1))