Options
Natalia Kataoka ✭✭✭✭✭
12/16/19 in Formulas and Functions
I am attempting to write a formula based on 2 separate criteria, though I feel like I need some type of "BUT" statement and could use some help.
I want a picklist column to read YES or NO depending on the following:
IF Ins Status = Current OR N/A AND Agreement EXE is checked THEN Approved for Work = YES, BUT, IF Agreement Type = Truck AND Ins Status = Current AND Agreement EXE is checked AND if AB5 Compliant is checked THEN Approved for Work = YES, otherwise NO.
In a nutshell, there is a third requirement if the type = truck, that does not apply to any other type.
I have the first portion working correctly, I am just unsure how to add the second portion. It currently reads:
=IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", "NO")
Basically, I want a separate criteria if the Agreement Type = Truck, but every other type would be based upon a different criteria. In the attached screenshot, I would want TESCON to read NO under Approved for Work.
Tags:
- Formulas
- functionality
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
12/17/19
Options
This would be a good example of when to use a nested IF formula.
=IF(this is true, then do this, otherwise do this)
You would drop the next IF statement into the "otherwise do this" portion.
=IF(this is true, do this, IF(something else is true, do this, otherwise do something else))
0 · Share on FacebookShare on Twitter
Natalia Kataoka ✭✭✭✭✭
12/17/19
Options
I have been attempting to nest the IFs, but the formula is still not working. I have tried rearranging, but no luck. This is what I have come up with so far, and while there is no error, it is not changing the field that I want it to.
=IF(AND([Agreement Type]@row = "TRUCK", [Agreement EXE]@row = 1, [Ins Status]@row = "Current", [AB5 Compliant?]@row = 1), "YES", IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", "NO"))
I also tried the below, with and without listing all the other Agreement Types.
=IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A", [Agreement Type]@row = "MSA", [Agreement Type]@row = "BPO", [Agreement Type]@row = "MPO"), [Agreement EXE]@row = 1), "YES", IF(AND([Agreement Type]@row = "TRUCK", [Agreement EXE]@row = 1, [Ins Status]@row = "Current", [AB5 Compliant?]@row = 1), "YES","NO"))
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
12/17/19
Options
Based on your original post where you stated:
I want a picklist column to read YES or NO depending on the following:
IF Ins Status =CurrentORN/AANDAgreement EXEis checked THENApproved for Work= YES, BUT, IFAgreement Type= Truck ANDIns Status= Current ANDAgreement EXEis checked AND ifAB5 Compliantis checked THENApproved for Work= YES, otherwise NO.
Give this a try...
=IF(OR(AND(OR([INS Status]@row = "Current", [INS Status]@row = "N/A"), [Agreement EXE]@row = 1), AND([Agreement Type]@row = "Truck", [INS Status]@row = "Current", [Agreement EXE]@row = 1, [AB5 Compliant?]@row = 1)), "YES", "NO")
0 · Share on FacebookShare on Twitter
Natalia Kataoka ✭✭✭✭✭
12/17/19
Options
Hm, that one is still not working correctly, but no error.
Do I need to specify any of the other Agreement Types?
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
12/17/19
Options
The above formula will cover the below based on how I read your original post (the quoted portion in my previous comment)...
YES will be generated by the following:
- [INS Status] = "Current" or "N/A"
- and [Agreement EXE] is checked
or
- [Agreement Type] = "Truck"
- and [INS Status] = "Current"
- and [Agreement EXE] is checked
- and [AB5 Compliant?] is checked
If a row does not meet one of those sets of requirements, it will generate a NO. If you have other combinations that could generate a YES then they would need specified.
0 · Share on FacebookShare on Twitter
Natalia Kataoka ✭✭✭✭✭
12/17/19
Options
Ah, ok got it. I do think I'll need to specify some other combinations. I will play with that, thanks for your help!
0 · Share on FacebookShare on Twitter
Natalia Kataoka ✭✭✭✭✭
12/17/19
Options
Figured it out!
=IF(OR(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), OR([Agreement Type]@row = "BPO", [Agreement Type]@row = "MBPO", [Agreement Type]@row = "MSA"), [Agreement EXE]@row = 1), AND([Agreement Type]@row = "TRUCK", [Ins Status]@row = "Current", [Agreement EXE]@row = 1, [AB5 Compliant?]@row = 1)), "YES", "NO")
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
12/17/19
Options
Excellent. Happy to help! 👍️
0 · Share on FacebookShare on Twitter
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 59.4K Get Help
- 90 Global Discussions
- 94 Industry Talk
- 400 Announcements
- 12 Community Corner Newsletter
- 71 Brandfolder
- 121 Just for fun
- 46 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 251 Events
- 13 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
