Another Formula Question

Good Day all,

I am having another issue with a nested "IF" formula. I have 5 columns that are drop down selection. They each have 3 choices. "Approve", "Approve - with changes" and "Not Approve". I am trying to get a status column to return "Approved" IF all 5 columns state "Approve or Approved - with changes" and if any say "not approved" then return the status "Not Approved". Can someone please help me? I have tried everything.

Alternately, we could use the R, G, Y balls for this and that would be fine, but the formula is killing me.

THank you kindly,

Find more posts tagged with

Accepted answers

TVang

The formula has been revised to use generic column labels. You can substitute the actual labels to finalize the formula.

= IF( OR( Column11@row = "", Column12@row = "", Column13@row = "", Column14@row = "", Column15@row = ""), "No Status", IF( FIND("Not", Column11@row + Column12@row + Column13@row + Column14@row + Column15@row + "") = 0 , "Approved", "Not Approved"))

The approach remains unchanged:

Check to see if there are blanks in any of the 5 cells, and if so, then return "No Status".
Else, combine the contents of all 5 cells and see if "Not" is in the string and return "Approved" if it isn't found,;
Else, return "Not Approved".

All comments

Ashlee

@Andrée Starå

Here is a screenshot of my columns. It is a long sheet. There is 1 status column. I want all the "Approver" columns to return a value of "Approved" if they are "Approved or APproved with changes", if it is blank I want nothing, and if any of them are "Not approved" I want it to return a value of Not Approved? Can this be done?

Screenshot (86).png

Ashlee

Alex Argumedo

That one still came up as unparseable. I have just a few hours to finish this.. Is there any way that we can figure this out together?

Alex Argumedo

try:

=IFERROR(IF(AND(OR([Approver (Brendan Fowler)]@row = "Approved", [Approver (Brendan Fowler)]@row = "Approved, with changes"), OR([Approver (Steve Nelson)]@row = "Approved", [Approver (Steve Nelson)]@row = "Approved with changes"), OR([Approver - Content Division (Matthew)]@row = "Approved", [Approver - Content Division (Matthew)]@row = "Approved with changes"), OR([Legal Department - Approval]@row = "Approved", [Legal Department - Approval]@row = "Approved with changes"), OR([Approver: Johnathan]@row = "Approved", [Approver: Johnathan]@row = "Approved with changes")), "Approved", "Not Approved"), "")

Ashlee

Alex Argumedo

This is what I had typed in

=IFERROR(IF(AND(OR([Approver (Brendan Fowler)]@row = "Approved", [Approver (Brendan Fowler)]@row = "Approved, with changes"), OR([Approver: Steve Nelson]@row = "Approved", [Approver: Steve Nelson]@row = "Approved, with changes"), OR([Approver: Matthew]@row = "Approved", [Approver: Matthew]@row = "Approved, with changes"), OR([Legal Department - Approval]@row = "Approved", [Legal Department - Approval]@row = "Approved, with changes"), OR([Approver: Johnathan]@row = "Approved", [Approver: Johnathan]@row = "Approved, with changes"), "Approved", "Not Approved"), ""))

Now, instead of getting "Unparseable" I get "Incorrect Argument Set"??

Alex Argumedo

remove this comma "Approved, with changes" << it keeps coming back, that comma breaks the formula and it is about 5 times in it.

the comma is between the quotations.

Replace all "Approved, with changes" with "Approved with changes"

Ashlee

Toufong Vang

This is the formula I tried in regards to your post.. It came back with a blank when the cells are blank, which I appreciate. However, when anyone clicks "Approve the status doesnt change?

=IF(COUNTIF([Approver (Brendan Fowler)]@row:[Approver: Johnathan]@row, @cell <> "") < 5, "", IF(FIND("Not", JOIN([Approver (Brendan Fowler)]@row:[Approver: Johnathan]@row)) = 0, "Approved", "Not Approved"))

TVang

The formula has been revised to use generic column labels. You can substitute the actual labels to finalize the formula.

The approach remains unchanged:

Check to see if there are blanks in any of the 5 cells, and if so, then return "No Status".
Else, combine the contents of all 5 cells and see if "Not" is in the string and return "Approved" if it isn't found,;
Else, return "Not Approved".

Ashlee

Toufong Vang

IT WORKED!! Thank you so much!! Alex Argumedo I cannot thank you enough for your help.

Alex Argumedo

I'm glad it worked, and I hope it did it on time :)

Regards

Andrée Starå

@Ashlee

Glad you got it working!

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.