Conditional Formatting Help

Laura
Laura ✭✭✭✭✭✭
edited 12/09/19 inFormulas and Functions

See Screenshot Example Please

Goal: to compare cells in 2 rows across columns.

All numbers in both these rows are based off formulas.

I want the cell in row "needed machine hrs" (column 1) to turn RED,

IFthe number is greater than the number in the cell directly below it, in row "Current Machine/Labor Hrs" (column 1).

Is this possible?

Thanks in advance for any help or suggestions

comparisonsmartsheet.jpg

Tags:
· Share on Facebook Share on Twitter

Comments

  • Matt Hagler
    Matt Hagler ✭

    Can you rearrange the data so that the hours are in columns? If so, you can create an additional column with a formula like "=if([Needed Machine Hours]1>[Current Mechanical/Labor Hrs Incl Eff]1,"overloaded"," ") and then set up a conditional format to highlight the "Needed Machine Hours" column if the new column includes "overloaded". Hope that helps!

    · Share on Facebook Share on Twitter
  • Laura
    Laura ✭✭✭✭✭✭

    That was my first thought as well but its not my sheet to rearrange, unfortunately.

    Any other suggestions, without rearranging the sheet?

    · Share on Facebook Share on Twitter
  • Matt Hagler
    Matt Hagler ✭
    edited 04/12/18

    I can't think of a way to get it to work with only the two rows that you show. Maybe a row (Row 1 for this example) could be added in which a formula could be placed to evaluate the values in the column like the following:

    =IF([NEEDED MACHINE HRS]>[CURRENT MECH...], "YES", IF([NEEDED MACHINE HRS]=[CURRENT MECH...], "AT CAPACITY", ""))

    You could then add conditional formats for each column to highlight that cell in Row 1 like the following:

    "IfColumn 1is equal to 'YES' then apply this format to theColumn 1column."

    You would have to clone this format and adjust it for each column and if you wanted to use the "at capacity" to highlight the cell a different color, more formats would have to be set up to do that.

    Capture.JPG

    · Share on Facebook Share on Twitter
  • Matt Hagler
    Matt Hagler ✭

    You could even set up a notification that would trip when the "Overloaded?" column is not blank by using the example below.

    Capture1.JPG

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Hi @Tamar<\/a> <\/p>

I think this is happening becuase you are editing a range that exists in the original location. Instead of editing the range in the new location, be sure to delete the entire range and then click in the place where it was in the formula. Then choose another range. <\/p>

I hope that helps.<\/p>

Matt<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":107113,"type":"question","name":"How to return a BLANK if we have a BLANK cell using a COUNTIFS formula","excerpt":"This formula is currently working, and it is returning how many items are reviewed for each \"Project Lead\". The first part of the formula is basically saying that we would count them, as long as we have a \"Reviewer\" name added in the \"Reviewer\" column. =IFERROR(COUNTIFS(Reviewer:Reviewer, <>\"//m.santa-greenland.com/community/discussion/comment/\", [Project Lead]:[Project…","snippet":"This formula is currently working, and it is returning how many items are reviewed for each \"Project Lead\". The first part of the formula is basically saying that we would count…","categoryID":322,"dateInserted":"2023-06-29T22:39:31+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T02:51:05+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T03:08:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":157974,"lastUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T03:08:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3376173036,"url":"https:\/\/community.smartsheet.com\/discussion\/107113\/how-to-return-a-blank-if-we-have-a-blank-cell-using-a-countifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107113\/how-to-return-a-blank-if-we-have-a-blank-cell-using-a-countifs-formula","format":"Rich","lastPost":{"discussionID":107113,"commentID":383252,"name":"Re: How to return a BLANK if we have a BLANK cell using a COUNTIFS formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383252#Comment_383252","dateInserted":"2023-06-30T02:51:05+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T03:08:32+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-30T02:51:53+00:00","dateAnswered":"2023-06-29T22:55:01+00:00","acceptedAnswers":[{"commentID":383233,"body":"

@Filippo<\/a>, the most inelegant but dead simple thing to do is this:<\/p>

=IF(IFERROR(COUNTIFS(Reviewer:Reviewer, <>\"//m.santa-greenland.com/community/discussion/comment/\", [Project Lead]:[Project Lead], [Project Lead]@row), \"//m.santa-greenland.com/community/discussion/comment/\")<\/strong>=0, \"//m.santa-greenland.com/community/discussion/comment/\", IFERROR(COUNTIFS(Reviewer:Reviewer, <>\"//m.santa-greenland.com/community/discussion/comment/\", [Project Lead]:[Project Lead], [Project Lead]@row), \"//m.santa-greenland.com/community/discussion/comment/\")<\/strong>)<\/p>

Simplified, it's just this:<\/p>

=IF(<your formula> = 0, \"//m.santa-greenland.com/community/discussion/comment/\", <your formula>)<\/p>"},{"commentID":383252,"body":"

Very clever Lucas! Simple IF statement logic. It works perfectly, thanks!!!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":107107,"type":"question","name":"Formula that counts how many rows have a date in the past","excerpt":"Hi! I need help with creating a formula that will count how many rows in my column called End Date have dates in the past. I've tried the following and it gives an \"unparseable\" error: =COUNTIFS([End Date]:[End Date], \"<=\" & TODAY()) Thanks in advance for your help!","snippet":"Hi! I need help with creating a formula that will count how many rows in my column called End Date have dates in the past. I've tried the following and it gives an \"unparseable\"…","categoryID":322,"dateInserted":"2023-06-29T20:39:23+00:00","dateUpdated":"2023-06-29T20:39:58+00:00","dateLastComment":"2023-06-29T20:52:03+00:00","insertUserID":162796,"insertUser":{"userID":162796,"name":"Beth M","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20M","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n7ZG0TBD44T33.png","dateLastActive":"2023-06-29T21:23:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162796,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-30T02:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3376143686,"url":"https:\/\/community.smartsheet.com\/discussion\/107107\/formula-that-counts-how-many-rows-have-a-date-in-the-past","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107107\/formula-that-counts-how-many-rows-have-a-date-in-the-past","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107107,"commentID":383217,"name":"Re: Formula that counts how many rows have a date in the past","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383217#Comment_383217","dateInserted":"2023-06-29T20:52:03+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-30T02:47:38+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-29T20:54:25+00:00","dateAnswered":"2023-06-29T20:52:03+00:00","acceptedAnswers":[{"commentID":383217,"body":"

You are very close, try this.<\/p>

=COUNTIFS([End Date]:[End Date], <= TODAY())<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions