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
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!