Help with Formulas

d.elwell

Hello!

I’ve been using Smartsheets for a while now, but am new to building them. I am having trouble working with formulas to identify current status of my tasks. I’ve searched the community and have tried out suggestions based on previous responses, but I’m still stuck.

I’d be grateful for help sorting out the following:

1. What formula should I use to enable status health balls for each individual line (lines 5-7) to auto populate based on current status and overall due date? 
2. What formula should I use to enable the parent line (line 4) status health ball to auto populate based on the overall status of the individual lines within the group?

Ultimately, I'd like the status of each parent item to populate a report that I've created.

Thanks very much!

Paul Newcome

You would use nested IF statements for both.

 

For the child rows, it would look something like this...

 

=IF(Criteria set 1 is true, "Red", IF(Criteria set 2 is true, "Yellow", IF(Criteria set 3 is true, "Green")))

 

For the parent rows it would look something like this...

 

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green")))

.

The specifics would be based on your exact criteria.