# 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.

• ✭✭✭✭✭✭
edited 08/23/24

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!