Formula

Eddie Endurium LLC

I have a series deliverables reference from another sheet (sheet 1). In this sheet (sheet 2) I list those deliverables under an objective and I want to rate the entire objective based on the highest risk in my Symbols (Red being the highest risk, then Yellow, Green, Gray) what would be the formula.

Same thing for the objective checkbox, if one check box is selected the objective is a checkbox.

AravindGP

Hi @Eddie Endurium LLC

If I understand you correctly, you want Objective 1 (the parent row) to show Red if even one of its deliverables (children) is Red. If none are Red, then look for Yellow and make it Yellow if you find one, and so on.

You can add this formula to your parent row for the Symbol column in Sheet 2

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray")))

For Objective column (which is a checkbox), you can use this formula in Sheet 2

=IF(COUNTIF(CHILDREN(), 1)>0, 1, 0)

Eddie Endurium LLC

Thank you Aravind! Much appreciated

AravindGP

Hi @Eddie Endurium LLC

If I understand you correctly, you want Objective 1 (the parent row) to show Red if even one of its deliverables (children) is Red. If none are Red, then look for Yellow and make it Yellow if you find one, and so on.

You can add this formula to your parent row for the Symbol column in Sheet 2

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray")))

For Objective column (which is a checkbox), you can use this formula in Sheet 2

=IF(COUNTIF(CHILDREN(), 1)>0, 1, 0)

Eddie Endurium LLC

Thank you Aravind! Much appreciated