Can I consolidate a weekly single cell update to another cell to keep history viewable?
Hello! We are trying to move our Excel spreadsheet into Smartsheet for project status updates. Current process is that every Tuesday we keep adding comments manually to the Excel cell for at least 6 weeks. I set up a workflow in Smartsheet that sends an update request to the PM every Monday and the comments are added to the "current week notes" column in the sheet. I can set up automation to move that cell data to another column but can't figure out a way to keep that data history for more than the 1 week since the formulas I've tried keep erasing the historical notes. We would want to know the date the comment was made as well. We report out to leadership the project's history so keeping the comments in a readable format is important. First image is current state.
Best Answer
-
@Amber Lange For Test Example:
Main sheet, three columns
History Sheet, with extra auto Created and a Time Stamp + Note column changed to column formula
Back to Main sheet again, join/collect cross referenced to the history sheet, change to column formula
Automation
Result on Main sheet
How the history sheet looks
Answers
-
Simplest way to to right click on the cell and "view history", cut n paste it out.
If you need the history readable in a cell automatically, and you have a unique ID for each project
Create a new blank sheet to collect the the history. Then create an automation that triggers when new comments are added and copies the row to the new sheet.
On this new sheet create a column that joins the notes and the auto modified date/time.
Next In your "Historical Notes" column use a join(collect formula to collect all the note + timestamp from the new history sheet and show then as one big note.
-
It sounds like that would work. We have a unique ID (project number). I'm relatively new to Smartsheet so I'm not sure how to build that formula, especially with the date.
-
@Amber Lange For Test Example:
Main sheet, three columns
History Sheet, with extra auto Created and a Time Stamp + Note column changed to column formula
Back to Main sheet again, join/collect cross referenced to the history sheet, change to column formula
Automation
Result on Main sheet
How the history sheet looks
-
That was very helpful, thank you! Now, I'm being asked to rearrange the comments so the most recent date is at the top of the cell versus the bottom. Is there a way to do that?
-
@Amber Lange I do not believe there is a way to automatically change the order, copy row automation will always send it to the bottom of the history sheet.
If you manually apply a sort to the history sheet it will do what you want in that instance, but any new rows added after that will again go to the bottom and things will be out of order until you manually apply the sort again.
