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

olivialandau

Hello,

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

@olivialandau

How is the status marked complete?

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.

Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/