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.
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!