Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Colouring Cells using a formula.
Good Day All:
I would like a cell to change color (background color) based on a range(See Below). Has anyone used such a formula?
My Sheet:https://app.smartsheet.com/b/publish?EQBCT=906e1a0d8f8342e4baead18e6d0a76e4
Overall Rating Score
4.6 - 5.0 (Green)
3.6 - 4.5 (Light Green)
2.6 - 3.5 (Yellow)
2.0 - 2.5 (Light Red)
<2 on Any Objective / Criteria (Red)
Thanks
Comments
-
Robert S. Employee
Hello,
This can be accomplished using conditional formatting rather than a formula. Here's a help center article with more information on conditional formatting (https://help.smartsheet.com/articles/516359).
-
Kal-El ✭
Conditional Formatting will not work with my current layout
-
J. Craig Williams ✭✭✭✭✭✭
Since you have revoked the publishing of your example sheet, I can not imagine why Conditional Formatting won't work.
Craig
-
Kal-El ✭
Hi Craig:
I decided to use a formula because I only want Row 1 cells to have color. However, I am having problems adding colors to my formula.
Published Link to New Sheet:https://app.smartsheet.com/b/publish?EQBCT=587e3b977fe34f409ca2991506151082
My Formula: =IF([Total Points]1 >= 4.6, "Excellent", IF([Total Points]1 >= 3.6, "Good", IF([Total Points]1 >= 2.6, "Acceptable", "Unacceptable")))
Desired Results:
Excellent = Green
Good = Light Green
Acceptable: Yellow
Unacceptable = Red
-
Kal-El ✭
Craig:
I made some changes and wanted to add background color to Row 13 in addition to Row 1.
I appreciate your help.
Thanks
-
J. Craig Williams ✭✭✭✭✭✭
Kal-El,
You can't set a background color (or any formatting) via a formula. You can only do so using Conditional Formatting.
However, all is not lost.
1.添加一个新列,名字like "Use Conditional Formatting" (or something shorter). A Checkbox column would work.
2. Check row 1 and 13.
3. Set up a Conditional Formatting rule for each of the 4 types.
You'll need to add an ADD condition (using the down-arrow icon after initial setup).
Final result for Excellent should look something like the image below.
(Note that there is no "greater than or equal to" in the conditional criteria list)
I hope that helps.
Craig
Categories
=IF([Date as Text Type]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", DATE(VALUE(LEFT([Date as Text Type]@row, 4)), VALUE(MID([Date as Text Type]@row, 6, 2)), VALUE(MID([Date as Text Type]@row, 9, 2))), \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107082,"type":"question","name":"Automatic Update of Dropdown List?","excerpt":"I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet? I can create a helper sheet if that would help. Any ideas? I don't believe I have the premium Smartsheets plan. Thank you! Lori","snippet":"I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet?…","categoryID":322,"dateInserted":"2023-06-29T14:16:13+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T16:05:09+00:00","insertUserID":162337,"insertUser":{"userID":162337,"name":"maineL","title":"Admin. & Course Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/maineL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!plNXqQdBs_o!2kvQ_7y42f4!PTHvB33mICN","dateLastActive":"2023-06-29T16:17:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":162337,"lastUser":{"userID":162337,"name":"maineL","title":"Admin. & Course Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/maineL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!plNXqQdBs_o!2kvQ_7y42f4!PTHvB33mICN","dateLastActive":"2023-06-29T16:17:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":32,"score":null,"hot":3376107682,"url":"https:\/\/community.smartsheet.com\/discussion\/107082\/automatic-update-of-dropdown-list","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107082\/automatic-update-of-dropdown-list","format":"Rich","lastPost":{"discussionID":107082,"commentID":383131,"name":"Re: Automatic Update of Dropdown List?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383131#Comment_383131","dateInserted":"2023-06-29T16:05:09+00:00","insertUserID":162337,"insertUser":{"userID":162337,"name":"maineL","title":"Admin. & Course Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/maineL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!plNXqQdBs_o!2kvQ_7y42f4!PTHvB33mICN","dateLastActive":"2023-06-29T16:17:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T14:52:00+00:00","dateAnswered":"2023-06-29T14:50:26+00:00","acceptedAnswers":[{"commentID":383100,"body":"