How to flag the next 4 rows with no children

Dan West_TX

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)

Paul Newcome

How are you marking when something is complete?

Dan West_TX

Sorry Paul, I didn't realize that someone had replied. I thought I would get a notice or something, I almost gave up. :)

=IFERROR(IF(INDEX([% Complete]:[% Complete], MATCH([Subheading Row]@row, [Row Position]:[Row Position])) < 0.99, MAX(COLLECT([Row Position]:[Row Position], Children:Children, 0, Status:Status, "Complete")), ""), "")

Dan West_TX

I should have given a bit more context on that formula.

Subheading formula:

=MAX(COLLECT([Row Position]:[Row Position], [Row Position]:[Row Position], @cell < [Row Position]@row, Level:Level, @cell = 1))

Row Position Formula:

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

Paul Newcome

I'm not sure I follow. Are you able to show your % Complete column so I can see how your formula is populating it in context to your previous screenshot?

Dan West_TX

This is a project sheet, there is no formula in the % complete column. The parent rows in Smartsheet automatically calculate the % complete based on the child rows and duration. The Heading (level 0) is the project name, the Subheading (Level 1) is the title for that section (Level formula is just Count(Ancestors()). Basically, if Level 1 is not complete (>=99%) check for the next 5 tasks that are not marked as complete.