Using automation on conditional formatting

Ty Werven

Hello.

I am wanting to set up an automation rule that will turn all of the information in these cells to 0 at 6AM each morning. I also do not want it to ruin the conditional formatting on each cell.

The columns go all the way up to a count of 532. As you can see, 0=green, yellow=1, and 3>= red. As mentioned, I would like to know if their is an automation I can use to change all of these numbers into zeros at 6AM each morning without changing the conditional formatting for the cells. For clarity the values in each cell get pulled into a dashboard.

Thanks!

jmyzk_cloudsmart_jp

Hi @Ty Werven

Change cell values automation does not break conditional formatting.

I tested the automation to change cell values and found that it does not break the conditional formatting. I also found that you can set up to 20 cells' change value automation in one workflow. So, I needed to create two workflows to reset the 32 values to zero (I tested with the trigger for when the rows are changed, but the result should be the same for the time-based trigger).

Metric widget loses conditional formatting

As you can see in the dashboard image below, the metric widget loses conditional formatting. So, I used the web content widget to display a published sheet (as shown in the lower left corner).

When I check the "Zero Reset" checkbox of the second row of the first sheet in the image below, the call values get changed to zero, and the second sheet's Visitor Count column's values in the lower left corner get the reset values, and the format changes If I copy the Sample Data, the first row of the first sheet, the values in the second sheet get back to as shown below, and get the colors with the conditional formatting.

https://app.smartsheet.com/b/publish?EQBCT=502dde63e5af48b2933743ad3b62acb0

Automation

Conditional Formatting

Did you or will you create the conditional formatting rules for all 32 columns? You may have to make 4 x 32 = 128 rules!

I don't know if I would reach the limit if I created such a large number of conditional formatting rules, so I created a second sheet just for conditional formatting.

With the following formula, the sheet gets the Visitor Count from another sheet.

=INDEX({Auto Zero Reset w/o Breaking Conditional F : Test}, 1, [Primary Column]@row - 500)

The range is from the second sheet in the above image.

Ty Werven

@jmyzk_cloudsmart_jp

Hi. So if we use widgets you are saying that you can not keep conditional formatting? I already have a Dashboard made with all 32 widgets.

And yes, we have made all of those conditional formatting rules!

I originally had this connected to a data dumping sheet as you can see by the blue triangles in the top picture. We found that filling out forms to display the data would be too slow of a method.

So, we decided that just entering numbers into that top formula sheet to spawn data in the metric sheet would work enough. The goal is just to get it to all turn to 0's at 6AM. But if you think it will change everything to black numbers then I do not think it will work.

We also want to use these individual metric widgets and not display a page.

jmyzk_cloudsmart_jp

@Ty Werven

First, I was wrong to comment that conditional formatting would be lost in the Metric widget. I found the "Use formatting from source" menu, as shown in the image below.

Use formatting from the source.

Then, I tested if the formatting would be lost after the zero reset by automation. As shown below, automation zero reset does not remove the conditional formatting created using the "Use formatting from source" feature.

• The image below shows the dashboard image with different types of Metric widgets.
  • The first "Cell by Cell from Vertical" is created from my vertical visitor list.
  • The second "From Vertical List" is created from a range of cells in my vertical list.
  • The last on the lower right is created from the original sheet. This metric widget is closest to yours, except it does not divide into individual cells.

Before Zero Reset

After Zero Reset

Then, I triggered the zero reset automation.

After the refresh, all the metric widgets get the format for zero in the dashboard.

After I restore the sample data

Then, I restored the sample data. After refreshing the dashboard, I got the formatting as expected.