Health column?
Hello,
I'm 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
-
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<TODAY() for in the past.
Let me know if this helps :)
Good luck
Debbie
-
IT WORKED! Thank you :)
-
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"))
-
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!
-
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!
-
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!
-
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))))
-
Apologies, I have a lot of questions today :). I'm 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!
-
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 debbie.sawyer@smarterbusinessprocesses.com - might be easier!
-
Sure! I sent a zoom meeting invitation to you just now :) Thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!
