Can you change a status in a dependency row once their predecessor is complete?

Lucinda Bugbee

I have a project gnatt schedule that has a status column dropdown of "Not Started", "In Progress" "Complete" and "On Hold". When the status changes to "Complete" can the dependency row be changed to "In Progress"?

Once the Econolite Collection changes to "Complete" I want the status of the Rack Elevation Facility Visio Diagram to change to "In Progress" and send an automation.

SueinSpain

You might be able to do this through automation and "Change a Cell Value" but I am not sure how you would identify what row is the trigger and what row to amend.

The alternative would be a formula if you can access the dependency.

A really good question and if no one can find the answer for you please raise it as a Product Idea as it would be a great feature.

I use automation so if Status = Completed the % complete is set to 100 or if % Complete is 100% then set status = Completed as I find my users tend to update one or the other column but rarely both :(

Good luck as I'm definitely following this one :)

jmyzk_cloudsmart_jp

Hi @Lucinda Bugbee

Based on @SueinSpain 's idea, I created a formula-based solution that works even when multiple predecessors are used with formats like FS, FF, SS, or SF.

The goal is:
When all predecessor tasks are marked as Complete, update the current task's status to 'In Progress' automatically (or trigger an automation).

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

Concept

Many users use dependency types like FS, FF, SF, and SS. This logic:

• Strips out the text part (F, S) and extracts the task numbers.
• Looks up the status of those tasks using INDEX() + VALUE().
• Checks conditions to trigger an update based on status.

Formulas

// Remove FS/FF/SF/SS from Predecessor

[Pred Numbers] = SUBSTITUTE(SUBSTITUTE(Predecessor@row, "F", ""), "S", "")


// Extract individual Predecessor indexes

[P1] = IFERROR(LEFT([Pred Numbers]@row, FIND(", ", [Pred Numbers]@row) - 1), [Pred Numbers]@row)

[P2] = IFERROR(MID([Pred Numbers]@row, FIND(DL#, SUBSTITUTE([Pred Numbers]@row, ", ", DL#, 1)) + 1, FIND(DL#, SUBSTITUTE([Pred Numbers]@row + ", ", ", ", DL#, 2)) - FIND(DL#, SUBSTITUTE([Pred Numbers]@row, ", ", DL#, 1)) - 1), "")

[P3] = IFERROR(MID([Pred Numbers]@row, FIND(DL#, SUBSTITUTE([Pred Numbers]@row, ", ", DL#, 2)) + 1, FIND(DL#, SUBSTITUTE([Pred Numbers]@row + ", ", ", ", DL#, 3)) - FIND(DL#, SUBSTITUTE([Pred Numbers]@row, ", ", DL#, 2)) - 1), "")

DL# stands for the Sheet Summary Field, DL, with an unusual character, in this example, the pipe character, "|".

In the P2 to P3 formula, I used the SUBSTITUTE and FIND functions to split the [Pred Numbers] cell values.

[P2] = IFERROR(MID([Pred Numbers]@row , FIND(DL#, SUBSTITUTE([Pred Numbers]@row , ", ", DL#, 1)) + 1, FIND(DL#, SUBSTITUTE([Pred Numbers]@row + ", ", ", ", DL#, 2)) - FIND(DL#, SUBSTITUTE([Pred Numbers]@row , ", ", DL#, 1)) - 1), "")

// Lookup Status of each Predecessor

[Status P1] = IF(ISBLANK([P1]@row), "", INDEX(Status:Status, VALUE([P1]@row)))

[Status P2] = IF(ISBLANK([P2]@row), "", INDEX(Status:Status, VALUE([P2]@row)))

[Status P3] = IF(ISBLANK([P3]@row), "", INDEX(Status:Status, VALUE([P3]@row)))

// Checkbox logic: "Ready to start" if all predecessors are Complete or Blank (but not all blank)

[Update to In Progress] = AND(

    OR([Status P1]@row = "Complete", ISBLANK([Status P1]@row)),

    OR([Status P2]@row = "Complete", ISBLANK([Status P2]@row)),

    OR([Status P3]@row = "Complete", ISBLANK([Status P3]@row)),

    OR(ISTEXT([Status P1]@row), ISTEXT([Status P2]@row), ISTEXT([Status P3]@row))

)

Notes

• This solution ignores dependency types (e.g., SS, FF, SF) beyond removing the letters.
• In case of SS, the logic may not suit your case, because SS means “Start when predecessor starts”, not “complete when predecessor completes”. That nuance is skipped to avoid overcomplicating this formula-based logic.

I hope this helps someone trying to implement conditional status updates on dependency completion.

Workflow Image

Demo Image

SueinSpain

@jmyzk_cloudsmart_jp Wow just WOW - very clever solution very clearly explained