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."

kelceyg

@jmyzk_cloudsmart_jp I thought I had the solution figured out, but it appears not.

I should start this out with all rows on the sheet have been copied over from various helper sheets.

The process:

1. We have a general intake sheet where a "Project Subtype" is selected from a dropdown list. This first intake sheet is also were an auto-number column is utilized. (Subtype example: Ad)
2. Based on the project subtype selection, the row is copied to a helper sheet (Ad Intake), and this is where a formula is utilized to determine how many times this project row is copied over to the third helper sheet.
3. On the third helper sheet (Ad Helper), INDEX/Match formulas are used on each copied row to pull in reoccurring tasks related to creating an Ad. A helper column is used that takes the data from the auto-number column (from the first intake sheet) and adds a "." and sequential numbers. (Example IMP001.1, IMP001.2, etc.)
4. Finally, all of the rows from the Ad Helper sheet are copied over to the final Project Tracker sheet. This sheet will have a parent row with the project name and assigned ID from the auto-number column, and the tasks listed underneath.

It is the final Project Tracker sheet that I need to have the ability to mark a task as "Ready for Work" when the previous task is marked as complete. So when IMP001.1 is "Complete", IMP001.1 changes to "Ready for Work".

In my solution, I have Row column with the MATCH column formula: =MATCH([Job ID]@row, [Job ID]:[Job ID], 0)

Then I have a Status Change column with this column formula: =IF(Row@row > 1, IF(INDEX(Status:Status, Row@row - 1) = "Complete", "Ready for Work", Status@row), Status@row)

Then I have a Prev Row Complete checkbox column with this column formula: =IF([Status Change]@row = "Ready for Work", 1, 0)

Lastly, I have an automation set up that says: "When rows are added changed: When Prev Row Complete changes to Checked, Change cell value: Change cell value in Status to Ready for Work"

The problem I am running into is that once the Status of one row changes to "Complete," every single row underneath changes to Ready for Work, not just the immediate row under. Additionally, I don't want the Status of the parent rows of each separate project to change.

Any thoughts on how I can make this work?

Screenshots of my final project tracking sheet below:

Georgie

Hi @kelceyg,

Thanks for providing a full explanation of all the formulas and automation you’re using. It looks like all rows are changing to “Ready for Work” because your formulas and automation are creating a loop - that is, your flow of work goes like this:

• First, the Status Change column changes to “Ready for Work” when previous row’s Status changes to “Complete”.
• Then, the Prev Row Complete column has a formula that checks the box when the Status Change column is equal to “Ready For Work”.
• Then, the automation updates the Status column to “Ready for Work” when Prev Row Complete is checked.

So that all rows don’t change to Ready for Work, you’ll need to change the phrasing either in the Status column or the Status Change column. My suggestion would be to edit the formula in the Status Change column so that it returns “Ready” when the previous row is marked as “Complete”, like so:

• =IF(Row@row > 1, IF(INDEX(Status:Status, Row@row - 1) = "Complete", "Ready", Status@row), Status@row)

Does that work for you?

Georgie