Another Formula Question

Ashlee

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,

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

Alex Argumedo

The IF conditional , OR and the AND can be used in the following way: (this is an example with 3 columns, but you only have to repeat for the rest):

=IF(AND(OR([Column17]@row = "Approve", [Column17]@row = "Approve with changes"), OR([Column21]@row = "Approve", [Column21]@row = "Approve with changes"), OR([Column20]@row = "Approve", [Column20]@row = "Approve with changes")), "Approved", "Not Approved")

Toufong Vang

Hi, Ashlee

Try this...

= IF( FIND("Not", JOIN(Column11@row:Column15@row))=0,"Approved", "Not Approved")

Join the cells into one long text/string and then search for "Not".

Ashlee

Alex Argumedo

This helped a lot, is there a way to add in that when the cells are blank there is no status?

Alex Argumedo

yes, just add IFERROR( at the front:

=IFERROR(IF(AND(OR([Column17]@row = "Approve", [Column17]@row = "Approve with changes"), OR([Column21]@row = "Approve", [Column21]@row = "Approve with changes"), OR([Column20]@row = "Approve", [Column20]@row = "Approve with changes")), "Approved", "Not Approved"),"")

Ashlee

Alex Argumedo 

This is what I have in the box.. I know it is a lot I am sorry. This is returning the status "Incorrect Argument Set"

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

Alex Argumedo

I think you are missing the IF error option at the end:

=IFERROR(IF(AND(OR([Column17]@row = "Approve", [Column17]@row = "Approve with changes"), OR([Column21]@row = "Approve", [Column21]@row = "Approve with changes"), OR([Column20]@row = "Approve", [Column20]@row = "Approve with changes")), "Approved", "Not Approved"), "")

This is my same formula but with the IFERROR added, just add the other 2 columns.

Notice the las part of the formula is not the same it has and added part

Ashlee

Alex Argumedo 

That worked sort of. However, the status still comes up when all the cells are blank? I want to drag the formula down or even create a column formula in which it will only populate if there are approvals/rejections coming through? Is that even possible?

Ashlee

Alex Argumedo 

I removed the "Not Approved" from the end and it worked, but now how will I get the not approved to appear?

Alex Argumedo

Try this:

=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

It came up as "Unparseable"? I do not know what the problem is. You seem to be my only help though

Alex Argumedo

Ther is a typo in your formula, I just fixed, try this:

=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 

It still came up as unparseable. I feel so confused as to why this keeps happening. I am doing a sheet for a client of mine and on a deadline, so I am obviously rushing... But I have never had a formula take me this long to figure out

Alex Argumedo

Don't panic, there is more typos in your formula, and the typos are commas, therefore breaking the whole thing, removed another misplaced comma:

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

I changed "Approved, with changes" to "Approved with changes"

Toufong Vang

The revised formula checks to see if there are any blank cells, "No Status".

=IF(COUNTIF(Column11@row:Column15@row, @cell<>"")<5,"No Status", IF( FIND("Not", JOIN(Column11@row:Column15@row))=0,"Approved", "Not Approved"))

Explanation

COUNTIF(Column11@row:Column15@row, @cell<>"")

The COUNTIF() expression returns the number of responses (non-blank cells). If it is not "5", then the value returned by the formula is "No Status".

IF( FIND("Not", JOIN(Column11@row:Column15@row))=0,"Approved", "Not Approved")

The second part of the formula:

1. Combines all 5 responses into one string;
2. Searches the string for "Not";
3. If it is not found ("0"), then returns "Approved";
4. Otherwise returns "Not Approved".

The assumption is that all 5 columns being evaluated are next to each other.

Andrée Starå

Hi @Ashlee

I hope you're well and safe!

Have you double-checked your column names because they don't seem to be consistent in the formula?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!