Status formula pulling from 2 columns and using multiple conditions

Brooks

Hello,

I am trying to write a formula that pulls from two columns: Progress and Done?

Progress is a drop down column with these options: Not started, In progress, Scheduled, Declined.

Done? is a checkbox.

When Done? is checked, then the formula should return Complete.

When Done? is unchecked and Progress is Not started, then the formula should return Not started.

When Done? is unchecked and Progress is In progress, then the formula should return In progress.

When Done? is unchecked and Progress is Scheduled, then the formula should return In progress.

When Done? is unchecked and Progress is Declined, then the formula should return Complete.

This is what I have so far. It works when Progress is "Scheduled" but not the others.

=IF(Progress@row = "Scheduled", IF([Done?]@row = 1, "Complete", IF(Progress@row = "In progress", "In progress", IF(Progress@row = "Scheduled", "In progress", IF(Progress@row = "Declined", "Complete", "Not started")))))

Thank you for your help!

Find more posts tagged with

Formulas

Accepted answers

All comments

Genevieve P.

Hi @Brooks

You'll want to use an OR function in there instead of moving from one IF to another. This way it checks the two possible instructions (checked box or "Declined") to then show "Complete":

=IF(OR(Progress@row = "Declined", [Done?]@row = 1), "Complete",

Then since we've already written the Checkbox instruction, the formula will only move on if the checkbox is not checked, so we don't have to write this again.

What I would do next is write the one logic statement that's different than the value:

IF(Progress@row = "Scheduled", "In progress",

Otherwise, the other two statements are to return the same value as in your "Progress" column, so you can simply reference that cell without an IF statement!

Progress@row

Full Formula:

=IF(OR(Progress@row = "Declined", [Done?]@row = 1), "Complete", IF(Progress@row = "Scheduled", "In progress", Progress@row))

Let me know if this makes sense and works for you 🙂

Genevieve

Brooks

Hi @Genevieve P. !

Yes, I completely understand. I totally forgot about the OR function. Thanks so much! 😊

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.