How to flag the next 4 rows with no children

Options

Hey all, I'm stuck on a formula for a project task sheet. In the below picture, I have the Children column, which shockingly calculates the number of children that row has.

Basically, if it has no children, it is a task. If it has children it is a parent/summary row. I want to flag the next 4 tasks (0 children) after the last completed row (which is the just the row number of the last row to be marked as complete). This is to be able to populate a "next steps" report. I have the flag/checkbox column, but I can't come up with a fully working formula. Currently, I am flagging the next 5 rows (numerically) after a completed row, but only if they have 0 Children and it is not a Milestone (duration = 0). So if there are parent or Milestone rows in the next 5 rows it may only flag 2 or 3 rows. This does what I want, but I'd rather have the next 4 rows with 0 children all the time and ignore the parent/summary rows. Current formula is: =IF(AND(Duration@row <> 0, Children@row = 0, NOT(ISBLANK([Last Completed Row]@row)), [Row Position]@row > [Last Completed Row]@row, [Row Position]@row <= [Last Completed Row]@row + 5), 1, 0)



Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!