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

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.

Find more posts tagged with

Formulas

Accepted answers

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

All comments

Sam M.

Hi @Omotola Ashafa,

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

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:

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.