How to Record and compare previous status (stoplight color or dates)
I have a report I'm trying to emulate in Smartsheet. The report has critical milestones in column a, and stoplight colors in Column b, and c. Column b shows the prior week and Column c shows the current week. The intent of the report is to show if a milestone has trended the same, improved, or gotten worse by comparing the change in colors over time.
How would I capture the prior week information from a sheet so I can compare it to the current week?
Any suggestions would be greatly appreciated.
Hudson
Best Answer
-
Hi @Hudson_TMR
The way I would personally do this is to add in a "Helper Column" to the sheet: I'd add a Dropdown Text column with the three colour options. The reason I would need it to be a Dropdown type of column is so that I can then use it in a Change Cell Workflow.
See: Change the Value of a Cell in an Automated Workflow
In the Change Cell Workflow, I would pick a time and date during the week to copy over the current symbol colour (ex. every Friday at 5pm) so it records this past week's colour.
See: Create a Time-Based Automated Workflow
The Condition Paths identify what colour word to put in my Status column, based on the colour that's in my "Current Week" column.
See: Condition Blocks: Filter What Your Automated Workflows Send
Then you can hide this Dropdown column in your sheet, and add a simple Column Formula to your Previous Week colour column, like so:
See: Set Formulas for All Rows with Column Formulas
As long as the text is the same ("Green", "Red", "Yellow"), the formula will translate that into a stoplight colour ball.
Every Friday the Text column will update, which will update your "Previous Week" column so as you work in your sheet the next week you can compare how your current week is progressing.
Will this work for you?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Hudson_TMR
The way I would personally do this is to add in a "Helper Column" to the sheet: I'd add a Dropdown Text column with the three colour options. The reason I would need it to be a Dropdown type of column is so that I can then use it in a Change Cell Workflow.
See: Change the Value of a Cell in an Automated Workflow
In the Change Cell Workflow, I would pick a time and date during the week to copy over the current symbol colour (ex. every Friday at 5pm) so it records this past week's colour.
See: Create a Time-Based Automated Workflow
The Condition Paths identify what colour word to put in my Status column, based on the colour that's in my "Current Week" column.
See: Condition Blocks: Filter What Your Automated Workflows Send
Then you can hide this Dropdown column in your sheet, and add a simple Column Formula to your Previous Week colour column, like so:
See: Set Formulas for All Rows with Column Formulas
As long as the text is the same ("Green", "Red", "Yellow"), the formula will translate that into a stoplight colour ball.
Every Friday the Text column will update, which will update your "Previous Week" column so as you work in your sheet the next week you can compare how your current week is progressing.
Will this work for you?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank You!! Really appreciate your response. I'll give it a demo and see if it works out, although I can't see any reason it wouldn't.
Cheers,
Hudson
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!