Question

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

Genevieve P. · Accepted 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