Change Status Based on Previous Row

kelceyg

I am trying to create a column formula that will change the status of a row to "Ready for Work" when status in the row above it changes to "Complete"

I tried to follow the information that was provided this thread, but I keep getting #UNPARSEABLE:

https://community.smartsheet.com/discussion/128307/change-status-based-on-previous-row

I already have an auto-number column (Job # - font is just in white based on conditional formatting), I added a Text/Number column (Status Row) for the Match formula, and I was trying to use a checkbox column (Status Change) to check if the previous row's status changes to "Complete." I wanted that to trigger the "Change a cell value…" automation to change the Status to "Ready for Work."

Can anyone please assist on the correct column formula to use to accomplish the above?

Thanks!

jmyzk_cloudsmart_jp

Hi @kelceyg

We use the MATCH function to cope with the case when rows are moved. For example, in the image below, the shaded rows are moved. If you are not moving the rows, you can remove the MATCH function column and use the Auto Number column.

The first solution uses a formula to show the status value based on the previous row's value.

[Status by Formula] =IF(Row@row > 1, IF(INDEX(Status:Status, Row@row - 1) = "Complete", "Ready for Work", Status@row), Status@row)

1. IF(Row@row > 1, ...): This checks if the current row is not the first row. The formula only applies to rows greater than 1, as there's no "previous row" for the first row.
2. INDEX(Status:Status, Row@row - 1): The INDEX function retrieves the value from the "Status" column in the row above the current row (Row@row - 1). This allows you to check the status of the previous row.
3. INDEX(Status:Status, Row@row - 1) = "Complete": This part of the formula checks if the previous row's status is "Complete." If true, it proceeds to update the current row's status.
4. "Ready for Work": If the previous row's status is "Complete," the formula sets the current row's status to "Ready for Work."
5. Status@row (else condition): If the previous row's status is not "Complete" or if the current row is the first row, it retains the existing status (Status@row).

https://app.smartsheet.com/b/publish?EQBCT=bc09294908af48438acea6835a275d51

The second solution uses workflows and a helper column to change the Status value with automation.

[Previous Row Status Changed to Complete] =IF(Row@row > 1, INDEX([Status Change]:[Status Change], Row@row - 1))

This formula checks if the previous row's status is "Complete" and marks the checkbox if true.

The workflows are as follows.

No 2. Automation: A workflow in which the "Previous Row Status Changed to Complete" column detects a status change and updates the current row's status to "Ready for Work."

• Trigger: When a checkbox is marked as checked in the "Status Change" column (indicating the previous row's status has changed to "Complete").
• Action: Change the "Status" column in the current row to "Ready for Work."