IF AND....
I am trying to have a column show Green when the status Does not = Contacted - Need to Follow Up AND when Attempt # is less than or equal to 2 (Attempt # = 3 or 4 it should be yellow and 5 or more should be red). HELP :)
=IF(AND(Status@row <> "Contacted - Need to Follow up", "green", IF(AND([Attempt #]@row <= 2, "green", IF([Attempt #]@row = 3, "yellow", IF([Attempt #]@row = 4, "yellow", IF([Attempt #]@row > 4, "red")))))))
Answers
-
Carson Penticuff ✭✭✭✭✭✭This will also do some verification to ensure the symbols are not being populated for blank rows or any non-numerical values.
=IF(AND(Status@row <> "Contacted - Need to Follow up", Status@row <> "", ISNUMBER([Attempt #]@row)), IF([Attempt #]@row <= 2, "Green", IF([Attempt #]@row <= 4, "Yellow", IF([Attempt #]@row > 4, "Red"))))
-
THanks, i got red and yellow but no greens.
-
Carson Penticuff ✭✭✭✭✭✭
In your screenshot, none of the values in Attempt # are 2 or less. Are there rows elsewhere that are 2 or less?
-
I appologize if I said this wrong (good possibility as I have been working on this all day).
If status is not Contacted- Need to Follow Up, it want it to be green.
If it is Contacted - Need to Follow Up, if it is 2 or less than green. If it is 3 or 4 then yellow and if 5 or more red. What i need to see is who we have contacted and need to follow up with and code it accordingly. If we have scheduled a meeting, then green is great.
-
Carson Penticuff ✭✭✭✭✭✭
Yes, I definitely misunderstood. No worries, give this one a try:
=IF(Status@row <> "", IF(Status@row <> "Contacted - Need to Follow up", "Green", IF(ISNUMBER([Attempt #]@row), IF([Attempt #]@row <= 2, "Green", IF([Attempt #]@row <= 4, "Yellow", "Red")))))
Help Article Resources
Categories
Check out theFormula Handbook template!
<\/p>
You can use this formula. The formula will count rows where priority is high and status is not completed.<\/p>
<\/p>
=COUNTIFS(Priority:Priority, \"High\", Status:Status, <> \"Completed\")<\/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":112197,"type":"question","name":"Method to check whether a Assessment Date has passed upon multiple cells within a row.","excerpt":"Good afternoon, We are currently using a smart sheet to track assessment bookings for different standards. We enter the assessment dates within the relevant cells for a particular standard (normally 3 assessments per standard, however are named differently which has resulted in a large number of columns which is…","snippet":"Good afternoon, We are currently using a smart sheet to track assessment bookings for different standards. We enter the assessment dates within the relevant cells for a particular…","categoryID":322,"dateInserted":"2023-10-26T13:07:42+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T10:08:35+00:00","insertUserID":143323,"insertUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T10:10:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":143323,"lastUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T10:10:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":52,"score":null,"hot":3396731177,"url":"https:\/\/community.smartsheet.com\/discussion\/112197\/method-to-check-whether-a-assessment-date-has-passed-upon-multiple-cells-within-a-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112197\/method-to-check-whether-a-assessment-date-has-passed-upon-multiple-cells-within-a-row","format":"Rich","lastPost":{"discussionID":112197,"commentID":401973,"name":"Re: Method to check whether a Assessment Date has passed upon multiple cells within a row.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401973#Comment_401973","dateInserted":"2023-10-27T10:08:35+00:00","insertUserID":143323,"insertUser":{"userID":143323,"name":"Matthew Drake","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Drake","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T10:10:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T10:10:19+00:00","dateAnswered":"2023-10-26T14:43:35+00:00","acceptedAnswers":[{"commentID":401827,"body":"
