Another Formula Question

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

Uploaded Jun 14, 2021235.86 KB

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"))

Toufong Vang

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".

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!