Formula that brings a parent row into a report based on certain date criteria
I'm trying to bring parent rows into a report based on if the child row End Date is within the next 30 days. I've created a helper column but I'm struggling with the formula. My sheet has an Activities column, Start and End Dates etc and Im trying to create a formula that checks the helper column box of any row that has a date in the next 30 days but also checks any parent to that row.
Any help is much appreciated.
Answers
-
Hi @Pe
(Link to the published demo sheet)
ANC =COUNT(ANCESTORS())
End Date in Next 30 Days =IF(ANC@row > 0, IF(ISDATE([End Date]@row), IF([End Date]@row < TODAY(30), 1)))
Flag Parent =IF(COUNTIF(CHILDREN([End Date in Next 30 Days]@row), 1) > 0, 1) -
Thats great, thank you. Is there a way to lose the Flag parent and ANC helper columns and put all the formulas into one in the End Date in Next 30 days please?
-
Hi @Pete R
Here is the formula.
[End Date in Next 30 Days] =IF(ANC@row > 0, IF(ISDATE([End Date]@row), IF([End Date]@row < TODAY(30), 1)), IF(COUNTIF(CHILDREN([End Date in Next 30 Days]@row), 1) > 0, 1))
By the way, I mixed up the Smartsheet link with the link for the other question in the previous comment. Here is the correct link.
(Link to the published demo sheet)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 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!