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

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
    jessica.smith ✭✭✭✭✭
    edited 03/07/24


    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))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!