Is It Possible / Formula Request

@Felicia Nabors

How exactly are you wanting the Health symbols to fall? As it can be done.

I would like the colors to be same as the current Health Column. Here is the current legend

Gray - Not Started and/or not due in the next 90 days
Yellow - In Progress and due in the next 90 days
Red - Not Started/In Progress and due in the next 30 days
Green - Complete

Here is my thinking

Initiative - only green if all objectives status' are complete.
Objective - only green if all key results status' are complete

Other colors would be based upon due dates outlined above, but I would also like to base it upon the majority color of the underlying child. For example if the objective has 5 KR and 3 are yellow but 2 are red and the objective is in the next 90 days the objective should be yellow, but if all 5 KR's are red and the objective is due in the next 90 days, the objective should be red. (I think that makes sense).

Also, thanks for your help.

@Felicia Nabors

Ok. Lets Try This.. I will put os1, os2, os3 and so on because i dont know how many objective status rows you have.

and kr1,kr2 and so on for key results

=IF(Or(Status@Row = "Complete",And([Item Type]@row="Initiative",[os1]@row = "Complete",[os2]@row = "Complete",[os3]@row = "Complete"),And([Item Type]@row = "Objective",[kr1]@row = "Complete",[kr2]@row = "Complete",[kr3]@row = "Complete")),"Green",IF(And(Status@Row= "Not Started", [End Date/Due Date]@row >Today(90)),"Grey",IF(And(Or(Status@Row = "In Progress",Status@Row = "Not Started"),[End Date/Due Date]@row <=Today(30)),"Red",IF(And(Status@Row = "In Progress",[End Date/Due Date]@row <=Today(90)),"Yellow"))))

Just Replace the OS and KR rows with yours

Outside of seeing if it works for you. The problem I can see running into is when you don't have an entry in any of your Objectives or Key Results. So we would have to account for the blanks as well. I will do that once i am sure this portion of the formula works for you until that point

I think it might work. I have to rework it to include the Helper Reports column because it has the Objective and Key Results numbers in it. I will update this post by tomorrow morning and let you know if it worked for me. Thank you for working this out and pointing me in the right direction.

Hi. After working on the formula I determined that I might be overcomplicating what I am trying to do. So, below is my new formula, not sure it will work long term, I am still testing.

=IF(AND([SP Alignment (Pillar)]@row = "F1", [Item Type]@row = "Initiative", OR([Health]1 = "Gray", [Health]2 = "Gray", [Health]3 = "Gray")), "Gray", "")

Now, I am trying to figure out is it possible to write a formula for Smartsheet to calculate the correct symbol based upon the health status color and number of items. For example if I have a total of 5 items and the Health Status of 4 is red, and 1 status gray, could I write a formula that would determine that my Objective Status is red as the majority of the items are in a critical stage?

Thanks again for your help.

hard to truly think right now. How ever if you create a reference sheet that assigns a numeric value to each color. You can then Write a sum function with index match integrated that calculates the totals and assign a color based on the value of said totals.

Thanks. I will try that.

Have a great weekend.

@Felicia Nabors Let me know how it works out for you.