I read an old thread from 2018 about this, but is there a plan to provide users the ability to do conditional formatting using a RYG color scale based on high/low values?
I haven't heard anything about this but I have used the current conditional formatting with stacked if statements to achieve the same result. It can be somewhat lengthy to build, but it works just fine for me.
Hi Jeff,
Great idea! That would be a great addition to Smartsheet features.
Please submit an Enhancement Request when you have a moment.
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
Depending on your setup, have you thought about building a table out and using an INDEX/MATCH? Have you looked into RANKEQ or RANKAVG?
.
I have a sheet where I built out a table to assign a number of 1 - 10 based on a number in a different column. I basically use MIN and MAX to set my ranges then some basic formulas to establish the highpoint of each rank. Then I use a MIN(COLLECT(......)) to pull the lowest range for each number that is higher than my other cell and use that number in an INDEX/MATCH to pull my rank.
Basically it looks like the below. For ease of explanation, I am going to say I am using 10 different ranks, my MIN is 1 and my MAX is 100.
TABLE:
Rank Range
1 10
2 20
3 30
4 40
5 50
6 60
7 70
8 80
9 90
10 100
=INDEX(Rank:Rank, MATCH(MIN(COLLECT(Range:Range, Range:Range, @cell >= Number@row)), Range:Range, 0))
So if my number is 55, the MIN/COLLECT will grab the 60 and use that to MATCH on for the INDEX.
Definitely a lot easier to edit than a long nested IF.
And since I use formulas to adjust my Range values based on the MIN and MAX found in my Number column, I have essentially automated ranking my numbers as 1 - 10. From there, conditional formatting is very easy to set up.
Yeah I thought of that while typing my answer, but the solutions I built were before those formula were introduced so I haven't tried building it yet. You are right though, that would be a much simpler way to get the desired result.
Went back and looked at my old formula and sheet I was thinking of. It is still working well, but is almost comical in length, and it only posts 3 colors (red yellow green)
=IFERROR(IF(ISBLANK([Completion Date 1]10), IF([Due Date 1]10 < TODAY(), 11), IF([Completion Date 1]10 > [Due Date 1]10, 12)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 2]10), IF([Due Date 2]10 < TODAY(), 21), IF([Completion Date 2]10 > [Due Date 2]10, 22)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 3]10), IF([Due Date 3]10 < TODAY(), 31), IF([Completion Date 3]10 > [Due Date 3]10, 32)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 4]10), IF([Due Date 4]10 < TODAY(), 41), IF([Completion Date 4]10 > [Due Date 4]10, 42)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 5]10), IF([Due Date 5]10 < TODAY(), 51), IF([Completion Date 5]10 > [Due Date 5]10, 52)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 6]10), IF([Due Date 6]10 < TODAY(), 61), IF([Completion Date 6]10 > [Due Date 6]10, 62)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 7]10), IF([Due Date 7]10 < TODAY(), 71), IF([Completion Date 7]10 > [Due Date 7]10, 72)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 8]10), IF([Due Date 8]10 < TODAY(), 81), IF([Completion Date 8]10 > [Due Date 8]10, 82)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 9]10), IF([Due Date 9]10 < TODAY(), 91), IF([Completion Date 9]10 > [Due Date 9]10, 92)), "") + ", " + IFERROR(IF(ISBLANK([Completion Date 10]10), IF([Due Date 10]10 < TODAY(), 101), IF([Completion Date 10]10 > [Due Date 10]10, 102)), "")
My outlined solution was also build pre-RANK functions.
It takes a little bit of time to set up if you are automating the ranges based off of the MIN and MAX, but overall it provides for a lot of flexibility and very low maintenance.
Haha. That is rather long. Good thing your column names aren't any longer, or you might hit that 4,000 character per cell limit.
Talking about it got me curious as to my exact setup. Since it is fully automated, I haven't had to touch it in a while.
Table Rank Table Range
0 f
1 f
2 f
3 f
4 f
5 f
6 f
7 f
8 f
9 f
10 f
I am actually using 11 different ranks.
0 is '= MIN(Score:Score)
10 is '= MAX(Score:Score)
1 - 9 are basically 10%, 20%, 30%, etc....
To get the percentages, I just used
=VALUE("." + [Table Rank]@row) * MAX(Score:Score)
Then my INDEX/MATCH in my rank column was as above with the appropriate column name changes. I manually entered the numbers of 0 - 10 in my rank column so I could go ahead and set up the conditional formatting for each rank to show shades of red, orange, and green depending on the rank.
Nicely done!
Thanks!
This thread is from 2019 and I haven't really found anything current on color scales in SS so I was wondering if there is an update.
Hi Smartsheet Community, I’m looking for some help setting up a way to track reviewer submissions across three different sheets. Here’s the setup: We have three sheets (one for faculty, one for students, and one for scholarships). Each sheet lists applicants, and in each row we assign three Selection Committee (SC) members…
Hi, I'm having an issue where the graphs in the dashboard are auto scaling to the numbers in the table. I was hoping to get a scale of 100 for each of my graphs on my dashboard but there's seem to be no way to update this. Would anyone know how to update this?
Hi, I'm having issues trying to get my automation to function. I have tried it numerous ways and it still doesn't work. Basically, if anything but New York is selected, I want a link email to go out with an additional form I need to fill out. Here is what my workflow looks like: I just cannot figure out what I'm missing…
