Combining an IF formula to returning different values based on row level

Natalie Gorman

Hello lovely Smartsheet community.

I have a status column using symbols.

The status is set using a formula, but I currently have two different formulas depending on the level of the row.

If the row is a second level I have the following formula:

=IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF([Task Not Required this Year]2 = true, "Blue", IF(Progress3 = "Quarter", "Yellow", IF(Progress2 = "Half", "Yellow", IF(Progress2 = "Three Quarter", "Yellow", IF(Progress2 = "Full", "Green", "")))))))

If the row is anything below second level, then I have this formula:

=IF([Task Not Required this Year]81 = true, "Blue", IF(Progress81 = "", "", IF(Progress81 = "Quarter", "Yellow", IF(Progress81 = "Half", "Yellow", IF(Progress81 = "Three Quarter", "Yellow", IF(Progress81 = "Full", "Green", "Red"))))))

Both work fine independently but I am struggling to combine the formula so that I just have one, allowing easier insertion of rows without the need to consider which formula should be used.

Help would be most welcome.

Many thanks

Natalie

Andrée Starå

Hi @Natalie Gorman

I hope you're well and safe!

Try adding something like this.

I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more. (you can also include it in your formula, depending on what levels you need to check for)

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Natalie Gorman

Thank you @Andrée Starå I will give that a go.

I have another formula question. I currently have the following:

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

I want to add in an or at the start which is saying, if red or blue, then red. The rest of the formula remains the same.

I have tried multiple different ways and I can't seem to get the formula right.

Many thanks

Natalie

Natalie Gorman

https://community.smartsheet.com/discussion/comment/289417#Comment_289417

Hi @Andrée Starå

I have put in the helper column as suggested but still struggling to combine the two.

I get the following to work on the first row

=IF(AND(Helper@row = 2, (COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()))), "Red", IF(AND(Helper@row = 2, (COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()))), "Blue", IF(AND(Helper@row = 2, (COUNTIFS(CHILDREN(), "Yellow") = COUNT(CHILDREN()))), "Yellow", IF(AND(Helper@row = 2, (COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()))), "Green", IF(AND(Helper@row <> 2, IF([Task Not Required this Year]@row = true, "Blue", IF(AND(Helper@row <> 2, IF(Progress@row = "", "", IF(AND(Helper@row <> 2, IF(Progress@row = "Quarter", "Yellow", IF(AND(Helper@row <> 2, IF(Progress@row = "Half", "Yellow", IF(AND(Helper@row <> 2, IF(Progress@row = "Three Quarter", "Yellow", IF(AND(Helper@row <> 2, IF(Progress@row = "Full", "Green", "Red"))))))))))))))))))))))

However, when when I drag the formula down I get one of two errors on all rows including the one that was working:

#blocked or #incorrect argument set. The latter appears to occur on rows where the helper column is not returning a value as there are no children.

I'm sure I am missing something obvious but I am unable to see what it is.

Help would be much appreciated.

Kind regards,

Natalie