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!
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…
