change status based on previous row

Alywilk

Is there a way to have a formula change the status column when the row above it changes. For example when the status in Row 1, changes to "Complete" the status in row 2 will change to "Ready to Start" I know I can't put a formula in the drop down so I imagine some sort of helper column would be needed. or could this be done through automations instead?

Alywilk

I did work out to create a helper column that the box will check in the 2nd row if Status1(in row 1) changes to "complete" using the formula:

=IF(Status1 = "Complete", 1, 0)

and then set up an automation for when the box gets checked to update the Status cell to "ready to start" however this formula is not able to be copied down the column so it does require that I manually enter it for each row to be dependent on its correct predecessor (just the row above) So I am still interested in if there is an easier/different way to do this.

Paul Newcome

To get a column formula to work, you first need an auto-number column (called "Auto" in this example) and then a text/number column (called "Row" in this example) containing the following column formula:

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

Then the formula in the checkbox column would be:

=IF(IFERROR(INDEX(Status:Status, Row@row - 1), ".") = "Complete", 1)