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,
Best Answer
-
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".
Answers
-
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")
-
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".
-
This helped a lot, is there a way to add in that when the cells are blank there is no status?
-
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"),"")
-
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"))
-
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
-
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?
-
I removed the "Not Approved" from the end and it worked, but now how will I get the not approved to appear?
-
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"), "")
-
It came up as "Unparseable"? I do not know what the problem is. You seem to be my only help though
-
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"), "")
-
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
-
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"
-
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:
- Combines all 5 responses into one string;
- Searches the string for "Not";
- If it is not found ("0"), then returns "Approved";
- Otherwise returns "Not Approved".
The assumption is that all 5 columns being evaluated are next to each other.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 434 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi all, I'm struggling to get a formula to work that can return the number of projects that start within the next 4 weeks. In the main sheet we have a project type column and a project start date colu…
- Hi all! I know how to do this manually but am hoping someone has a shortcut! I have a sheet with a very long dropdown list of college majors for employees who have signed up for a particular program. …
- New program proposals are submitted via a form and collected on Sheet 1. When approved by a team leader, the row is copied to Sheet 2. I want to capture the date the copied occurred, which I thought w…
