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
-
Hi Paul, This formula is exactly what we needed. Thank you so much for your help!
Answers
-
Try:
=IF(AND(Ancestor[Overall Project]@row = 1, Children([exec report activity] = 1), 1, 0)
-
Hi Danielle, It didn't work. I tried a couple of variations and still no luck. Thank you
-
Try this...
=IF(AND(PARENT([Overall Project]@row) = 1, [Exec Report Activity]@row = 1), 1)
-
Hi Paul, This formula is exactly what we needed. Thank you so much for your help!
-
Happy to help. 👍️
-
Hi Paul - for this same formula - checkboxes are not being checked for the projects that have checkboxes at levels other than the level directly below the parent. Is there a version of this formula that incorporates ancestors that will search checkboxes at all levels below the parent?
-
@Andrea Mayer Try replacing PARENT with ANCESTORS.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!