Smartsheet Formula to Click Active Checkbox in Next Row when Complete is Checked in the Row above

Mil05006

Title says it all but I'm putting together a workflow using hierarchy. We have certain tasks that have 6-7 phases in them before they are complete, and other tasks that only have 1 thing to do when they are complete, and I'd like to build an automated formula to check an "active" checkbox if something is a child row and the child row above it is checked complete, OR to check "active" if the row doesn't have any children. I'd use this "check the active box" trigger to send an email alert to the person it's assigned to that their part is now active.

Thoughts on how to make this work? Below is a screenshot of what I've built out so far. I also have a parent row check box as well, but am completely stumped on how to get this trigger to work since it's dependent on the row above it getting checked first.

What I want is that if I check complete in Phase 1: Initial Scoping, the Active box under Phase 2: Mapping and Classification is checked (and then so on- so when I click complete in Phase 2, Phase 3 goes active, etc.).

Paul Newcome

Yeah. Sorry about that. I got a little ahead of myself and forgot to finish out the INDEX.

=IFERROR(INDEX(COLLECT(Complete:Complete, Row:Row, @cell = Row@row - 1), 1), "")

Paul Newcome

Insert an auto-number column (called "Auto" in this example) and a text/number column (called "Row" in this example). In the [Row] column use this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then in your [Active] column you can use something like this:

=IFERROR(INDEX(COLLECT(Complete:Complete, Row:Row, @cell = Row@row - 1), "")

Mil05006

Hello Paul,

Thank you! Did what you said and it's pulling an incorrect argument set. It keeps adding a ) at the end, and I see that but just have no idea where the heck to put it to get it to work. Putting it after complete:complete, row:row, or anywhere else pulls the same error.

Paul Newcome

Yeah. Sorry about that. I got a little ahead of myself and forgot to finish out the INDEX.

=IFERROR(INDEX(COLLECT(Complete:Complete, Row:Row, @cell = Row@row - 1), 1), "")

Mil05006

THANK YOU THAT WORKS! Really appreciate it.

Paul Newcome

Happy to help. 👍️