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

Exec Report Activity: checkbox column

Reporting: 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 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)

Any assistance you can offer is appreciated.

Thank you!

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!