Need help with the formula to update status based on changes to a field

Omotola Ashafa

Hi, I need help with a formula to automatically update status based on changes to multiple fields for example- I have =IF([Assigned To]@row <> "", "In Progress") to change status to In Progress when an input(name) is entered in the "assigned to" field

Now, I want the status to change to "In Review" when a name is entered in the "Peer reviewer" column and there is already a name in the "Assigned to" column.

I also need the status to change to "In Virtual Review" when a peer review complete is checked, and there's a name in the "Peer review" column as well as a name in the "Assigned to" column.

Sam M.

Hi Omotola,

I did some testing and this formula might work for you:

 

=IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review", IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review", IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")))

 

Formula in sections based on what is asked:

"=IF([Assigned To]@row <> "", "In Progress") to change status to In Progress when an input(name) is entered in the "assigned to" field"

IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")

 

"Now, I want the status to change to "In Review" when a name is entered in the "Peer reviewer" column and there is already a name in the "Assigned to" column."

IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review")

 

"I also need the status to change to "In Virtual Review" when a peer review complete is checked, and there's a name in the "Peer review" column as well as a name in the "Assigned to" column."

IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review")

Hope it helps,

Sam

Sam M.

Hi @Omotola Ashafa,

I did some testing and this formula might work for you:

 

=IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review", IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review", IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")))

 

Formula in sections based on what is asked:

"=IF([Assigned To]@row <> "", "In Progress") to change status to In Progress when an input(name) is entered in the "assigned to" field"

IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")

 

"Now, I want the status to change to "In Review" when a name is entered in the "Peer reviewer" column and there is already a name in the "Assigned to" column. "

IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review")

 

"I also need the status to change to "In Virtual Review" when a peer review complete is checked, and there's a name in the "Peer review" column as well as a name in the "Assigned to" column."

IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review")

Hope it helps,

Sam

Antonio Figueroa

You should use an nested IF AND statement see below

=IF(AND([Column7]@row > 0, [Column8]@row > 0, [Column9]@row > 0), "In Virtual Review", IF(AND([Column7]@row > 0, [Column8]@row > 0), "In Review", IF([Column7]@row > 0, "In Progress", "")))

Sam M.

Hi Omotola,

I did some testing and this formula might work for you:

 

=IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review", IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review", IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")))

 

Formula in sections based on what is asked:

"=IF([Assigned To]@row <> "", "In Progress") to change status to In Progress when an input(name) is entered in the "assigned to" field"

IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")

 

"Now, I want the status to change to "In Review" when a name is entered in the "Peer reviewer" column and there is already a name in the "Assigned to" column."

IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review")

 

"I also need the status to change to "In Virtual Review" when a peer review complete is checked, and there's a name in the "Peer review" column as well as a name in the "Assigned to" column."

IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review")

Hope it helps,

Sam

Omotola Ashafa

Thank you @Sam M. , it worked. Thank you for the explanation and for breaking it down.