Can you use Ancestors along with IF(And to check a box?

Hello,

I need some help with a formula, if it is even possible.

Scenario:

Overall Project: checkbox column Level = 1 or 2

Exec Report Activity: checkbox column Level = 3, 4, 5, etc.

Roadmap Report: checkbox column

Is it possible to have a formula search for a checked box in the Overall Project column (Overall Project Check also indicates that it is a parent row) and then look at the children rows in the Exec Report Activity column for any checked boxes, resulting in a check in a new column called Reporting?

I only want a check in the Reporting column if the parent row has the Overall Project column checked and a child

row (and child of child rows) has the Exec Report Activity column checked.

I have been trying variations of this formula without success.

=INDEX(ANCESTORS([Overall Project]:[Overall Project], 1, IF(AND([Exec Report Activity]@row, 1), 1)

The below formula works but only for the children at level 3, it does not work for children of children (level 4 and up).

=IF(AND(PARENT([Overall Project]@row) = 1, [Exec Report Activity]@row = 1), 1)

In the screenshot example, I would like a formula that will also check the boxes that are in red outline

Any assistance you can offer is appreciated.

Thank you!

Best Answer

  • Abraham Sanchez
    Abraham Sanchez ✭✭✭
    Answer ✓

    I have this Idea, I hope it helps.

    =IF(AND(COUNTIFS(ANCESTORS([Overall Project]@row), 1, ANCESTORS([Level]@row), 2) > 0, [Exec Report Activity]@row = 1), 1, 0)

    What it does is that it looks at all lthe ancerstors and count how manya at level 2 has are checked in the Overall Project, and if the current row Exec Report Active is check, if both are then if output 1 else a 0.

    With this formula it shouldn't matter how deep in the level the task is, as it will just look at all ancentors and check the ones at level 2 (there should be only one anyway) However if you remove the level check it can check in all ancentors not only the ones on level 2

Answers

  • Abraham Sanchez
    Abraham Sanchez ✭✭✭
    Answer ✓

    I have this Idea, I hope it helps.

    =IF(AND(COUNTIFS(ANCESTORS([Overall Project]@row), 1, ANCESTORS([Level]@row), 2) > 0, [Exec Report Activity]@row = 1), 1, 0)

    What it does is that it looks at all lthe ancerstors and count how manya at level 2 has are checked in the Overall Project, and if the current row Exec Report Active is check, if both are then if output 1 else a 0.

    With this formula it shouldn't matter how deep in the level the task is, as it will just look at all ancentors and check the ones at level 2 (there should be only one anyway) However if you remove the level check it can check in all ancentors not only the ones on level 2

  • Tina
    Tina ✭✭✭✭

    Hi Abraham, You are amazing! This formula does exactly what we needed it to do. Thank you so much for all of your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!