Please assist with this formula. Thanks.

All data is in a single sheet.

I need all rows in a column titled "Status" to be populated with "APPROVED" when the following conditions are met: "Yes" in column titled "BU Approval", "Green" or "Yellow" in column titled "B", "Accepted" or "N/A" in column titled "C", date in column titled "D" is today or in future, Box in column titled "E" is checked.

Here's the formula in column "Status" with "unparseable" error

=IFERROR ((AND [A] @row="Yes", [B] @row="Green" "Yellow", [C] @row="Accepted" "N/A", [D] @row>=TODAY (), [E] @row=1, "APPROVED"), "")

Find more posts tagged with

Accepted answers

Nick Korna

Hi @AnnS,

You need some OR statements in there for the multiple options:

=IF(AND([BU Approval]@row = "Yes", OR(B@row = "Green", B@row = "Yellow"), OR(C@row = "Accepted", C@row = "N/A"), D@row >= TODAY(), E@row = 1), "APPROVED", "")

Example output:

Hope this helps - if there any issues just post and we can work them out!

All comments

Darren Mullen

@AnnS it looks like you have a space between the column and the @row. It should be for example [A]@row.

AnnS

thanks - i removed the space before @row as suggested - still getting unparseable error

Carson Penticuff

I believe this covers all of the criteria.

=IFERROR(IF(AND([BU Approval]@row = "Yes", OR([B]@row = "Green", [B]@row = "Yellow"), OR([C]@row = "Accepted", [C]@row = "N/A"), [D]@row <= TODAY(), [E]@row = 1), "APPROVED", ""),"")

Nick Korna

Hi @AnnS,

You need some OR statements in there for the multiple options:

=IF(AND([BU Approval]@row = "Yes", OR(B@row = "Green", B@row = "Yellow"), OR(C@row = "Accepted", C@row = "N/A"), D@row >= TODAY(), E@row = 1), "APPROVED", "")

Example output:

Hope this helps - if there any issues just post and we can work them out!

AnnS

Formula from Nick worked. Thanks to all who responded!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.