Health column?
Hello,
我attempting to create a function in my "Health" column that will return Red, Yellow, or Green based on:
1) Status (dropdown input by sheet user)
2) Dates in the date columns to the right, broken down by step
We are attempting to track communications through various review processes. Some require legal review, and some do not. How do I make the health column reflect:
Green: Status Column is Not Started + Draft Due date is more than 7 days from today
Green: Status Column is Draft in Progress + Draft Due date is not more than 7 days from today
Green: Status Column is Fx Review in Progress + Fx Review Due date is in the future
Green: Status Column is Legal Review in Progress + Legal Review Due is in the future
Green: Status Column is Awaiting Distribution OR Distribution Complete + Distribution Date is in the future
Green: Status Column is Distribution Complete
Yellow: Status Column is Not Started + Draft Due date is 7 or fewer days from today
Red: Status Column is Not Started + Draft Due date is in the past
Red: Status Column is NOT Distribution Complete + Distribution Date is in the past
Yellow: Status Column is Draft in Progress + Draft Due date is in the past
Yellow: Status Column is Fx Review in Progress + Fx Review Due date is in the past
Yellow: Status Column is Legal Review in Progress + Legal Review Due is in the past
Yellow: Status Column is Awaiting Distribution + Distribution Date is in the past
Thank you,
Bridget
Best Answer
-
IT WORKED! Thank you :)
Answers
-
Debbie Sawyer ✭✭✭✭✭✭Hi Bridget you will need to use logic similar to this:
=IF(OR(AND(Red Cond1,Red Cond1),AND(Red Cond2,Red Cond2)),"Red",IF(OR(AND(Yellow Cond 1,Yellow Cond1),AND(Yellow Cond2,Yellow Cond2),AND(Yellow Cond3,Yellow Cond3),AND(Yellow Cond4,Yellow Cond4),AND(Yellow Cond5,Yellow Cond5)),"Yellow","Green"))
where
Red Cond1 = Red: Status Column is Not Started + Draft Due date is in the past
Red Cond2 = Red: Status Column is NOT Distribution Complete + Distribution Date is in the past
Yellow Cond1 = Yellow: Status Column is Not Started + Draft Due date is 7 or fewer days from today
Yellow Cond2 = Yellow: Status Column is Draft in Progress + Draft Due date is in the past
Yellow Cond3 = Yellow: Status Column is Fx Review in Progress + Fx Review Due date is in the past
Yellow Cond4 = Yellow: Status Column is Legal Review in Progress + Legal Review Due is in the past
Yellow Cond5 = Yellow: Status Column is Awaiting Distribution + Distribution Date is in the past
Use [status]@row="Not Started" etc
Use [Draft Due date]@row
Let me know if this helps :)
Good luck
Debbie
-
IT WORKED! Thank you :)
-
Debbie Sawyer ✭✭✭✭✭✭
Well done Bridget!
I hope you were ok with me giving you the logic instead of me writing it all out for you. Sometimes it is better for you to build the finished formula as you are more likely to understand what is going on and so will be able to write similarly complicated ones next time :)
Have a great weekend. Glad this helped.
-
I have a follow-up questions on this topic :).
I want to add a criteria that if status column = "distribution complete", the cell returns "blue". Where in the formula should I add that?
Thank you!
=IF(OR(AND(Status@row = "not started", [Distribution Date]@row < TODAY()), AND(Status@row <> "Distribution Complete", [Distribution Date]@row < TODAY())), "Red", IF(OR(AND(Status@row = "not started", [Draft Due]@row = TODAY(7)), AND(Status@row = "Draft in Progress", [Draft Due]@row < TODAY()), AND(Status@row = "Fx Review in Progress", [Review Due]@row < TODAY()), AND(Status@row = "Legal Review in Progress", [Legal Review Due]@row < TODAY()), AND(Status@row = "Awaiting Distribution", [Distribution Date]@row < TODAY())), "Yellow", "Green"))
-
Debbie Sawyer ✭✭✭✭✭✭
Hi Bridget
I have popped it in the end between the "Yellow" and "Green" - i.e. before the final "otherwise" statement.
You need to ensure that the RAG balls selected in the column properties support a blue ball! :D
=IF(OR(AND(Status@row = "not started", [Distribution Date]@row < TODAY()), AND(Status@row <> "Distribution Complete", [Distribution Date]@row < TODAY())), "Red", IF(OR(AND(Status@row = "not started", [Draft Due]@row = TODAY(7)), AND(Status@row = "Draft in Progress", [Draft Due]@row < TODAY()), AND(Status@row = "Fx Review in Progress", [Review Due]@row < TODAY()), AND(Status@row = "Legal Review in Progress", [Legal Review Due]@row < TODAY()), AND(Status@row = "Awaiting Distribution", [Distribution Date]@row < TODAY())), "Yellow", IF(Status@row="Distribution Complete","Blue","Green")))
Let me know if this works for you!
Kind regards
Debbie
-
PERFECT! Thank you :). I had tried to do that, but think I didn't nest it correctly... I appreciate your help!
-
Debbie, you're so helpful - could you please help me with one other Smartsheet formula question? I'm attempting to have an "expected % complete" column, and have it working correctly using this formula
=IF([Start Date]@row < TODAY(), (TODAY() - [Start Date]@row) / ([Finish Date]@row - [Start Date]@row), 0)
However, I want the max return value to be 100% (I.e., not 145% if the finish date is in the past). How do I calculate that? I'm at a loss...
Thank you!
-
Debbie Sawyer ✭✭✭✭✭✭
You're welcome
-
Hi Debbie, I see you're "you're welcome" message, but wasn't sure if you see my other question? I think they were posted the same minute :) Thank you!
-
Debbie Sawyer ✭✭✭✭✭✭
Hi Bridget, sorry like you said, our messages crossed, then I was in a client call! :D
Give me a mo and I'll test something out in a sheet - back in a few minutes
-
Thanks so much!
-
Debbie Sawyer ✭✭✭✭✭✭
Here we go - I think this works...
=IF([Start Date]@row > TODAY(), 0, IF([Finish Date]@row < TODAY(), 1, ((TODAY() - [Start Date]@row) / ([Finish Date]@row - [Start Date]@row))))
-
道歉,我今天有很多问题:)。我also trying to make my "% Complete" column have the parent row the sum of the children rows - and not be able to be edited. So, the parent row would say "build website" and the child rows would have the steps to complete that task (e.g., hire developer, draft content, create logo). I want the task owners to update the "% Complete" column for the child rows, and I want the parent row in the % Complete column to summarize the % complete of the children rows - perhaps incorporating the length of each of the children tasks (I.e., if 1 task is 10 days and the other tasks are each 5 days, the first task represents 50% of the task). Is that possible? I have seen this before, but think I must be using the wrong type of column or something to enable this automatically? Thank you!
-
Debbie Sawyer ✭✭✭✭✭✭
Bridget the Parent Row should automatically be summarising the children, is your sheet not doing it?
You can lock parent rows if you want to, there is a lock row option on the row menu.
If you have a moment, I can zoom and show you?
My email address is[email protected]- might be easier!
-
Sure! I sent a zoom meeting invitation to you just now :) Thank you!
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+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-08-26T01:04:51+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-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
