I have a column formula applied to a Status Column, however it is not applying to one of the rows.

Katherine Law

The column formula is:

=IF(Progress@row = "Complete", "Blue", IF(Progress@row = "Upcoming", "Green", IF(Progress@row = "Ongoing", "Green", IF(Progress@row = "Commenced", "Green", IF(Progress@row = "On Hold", "Yellow", IF(Progress@row = "Discontinued", "Red", IF(Progress@row = ".", "", IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", IF(COUNTIF(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red")))))))))))

The issue lies somewhere in the latter part of the formula where it is counting the colour of the dot in the child rows and using that to determine the colour of the dot in the parent row.

In summary, it is setup so that…

• If all child dots are blue, then the parent is blue
• If there are any yellow, then the parent is yellow
• If there are any green, then the parent is green
• If all child dots are red, then the parent is red

I am having issues with a group where there is one blue and one red child (see row 15 in the image below). The parent row (#215) is showing up blank. Do you have any idea as to why this would be the case?

Thanks!

jmyzk_cloudsmart_jp

Hi @Katherine Law

As I can not see the value of the parent row's [Progress] column, it is hard to guess the cause, but if the parent row has a value, I think the IF statement based on that value superseded the IF Statement based on the children's values.

So, I added an IF statement to determine whether the row is a parent row and applied IF statements based on the children's value, not on the row's Progress value.

=IF(COUNT(CHILDREN()) > 0, 
  IF(COUNT(CHILDREN(RYGB@row)) = COUNTIF(CHILDREN(RYGB@row), "Blue"), "Blue", 
  IF(HAS(CHILDREN(RYGB@row), "Yellow"), "Yellow", 
  IF(HAS(CHILDREN(RYGB@row), "Green"), "Green", 
  IF(COUNT(CHILDREN(RYGB@row)) = COUNTIF(CHILDREN(RYGB@row), "Red"), "Red")))
), 
  IF(Progress@row = "Complete", "Blue", 
  IF(Progress@row = "Upcoming", "Green", 
  IF(Progress@row = "Ongoing", "Green", 
  IF(Progress@row = "Commenced", "Green", 
  IF(Progress@row = "On Hold", "Yellow", 
  IF(Progress@row = "Discontinued", "Red", 
  IF(Progress@row = ".", "")))))))
)

The four IFs are for parent rows, and the seven IFs are for the children rows.

Smartsheet