How to write a formula that checks the status of predecessor rows?


I'm looking to create a formula that will automatically update a row's status column to 'Ready to Begin' so long as the status of all of that row's predecessors are marked as complete. So for example:

In this case, I'd like lines 7 and 8 have their Status column automatically update to "Ready to Begin" because their predecessor (line 6) has been marked as "Complete".

Can someone tell me if there's a formula function that works this way? Thank you!


  • Leibel S
    Leibel S ✭✭✭✭✭✭


    How is the status marked complete?

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭

    Hi @olivialandau

    I was in the same situation earlier and tried the below approach and it is working well for me and You can also try this by using the helper columns. Create the below columns and apply the formula

    First create a Row ID column which indicate the current row Number - =COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> ""))

    Create another Helper column - =Predecessors@row

    Create PredecessorsHelp = =VALUE([Helper]@row)

    Create Predecessor Status - =IF(INDEX(Status:Status, MATCH(PredecessorsHelp@row, RowID:RowID)) = "Complete", "Ready to Begin", "")

    I hope this works for you.


    Amit WadhwaniSmartsheet CoE, Ignatiuz Software, Exton, PA

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA

    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!