Need help creating a formula that automatically changes the "status" based on several fields

Hi! I need help with a formula to automatically update the "status" column based on changes to multiple fields.

The status column has three options: Not started, In progress, and Complete.

-- If columns "Reviewer Assigned" and "Approver Assigned" are not blank, I want the status to automatically change to "In progress."

-- If column "Send Email?" is "Yes", I want the status to automatically change to "Complete."

-- Otherwise, the stsus would be "Not Started" (ie. no reviewer or approver).

Here is what I have so far. I know there are errors though:

=IF(NOT(ISBLANK([Reviewer Assigned]@row)),NOT(ISBLANK([Approver Assigned]@row)),"In progress",IF([Send Email?]@row="Yes","Complete","Not started"))))

Thank you for your help!

Find more posts tagged with

Formulas

Accepted answers

Heather Duff

Hi @Brooks ,

You're very close! Try this:

=if(and([reviewer assigned]@row<>0,[approver assigned]@row<>0),"In Progress",if([send email?]@row="Yes","Complete","Not Started"))

Let me know if it works.

Best,

Heather

All comments

Heather Duff

Hi @Brooks ,

You're very close! Try this:

=if(and([reviewer assigned]@row<>0,[approver assigned]@row<>0),"In Progress",if([send email?]@row="Yes","Complete","Not Started"))

Let me know if it works.

Best,

Heather

Brooks

Thanks @Heather D!

Heather Duff

Happy to help!

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.