Multi Nested IF/AND Formula
Hello,
I really thought I could start out simple with this one and then build on it but I can't even get the first piece without an #UNPARSEABLE error.
Here's a snip of the smartsheet columns I am comparing and the need is to return what Template to use. There's 10 different "Practice Group or Division" options but the logic focuses on only 2 Divisions and whether there is a value in "California License" or it's blank.
Here is the logic I have been asked to craft a formula from to return what Template to use:
So I started with line one in a formula and build from there:
=IF(AND([Practice Group or Division]@row = "Asset Management"), (ISBLANK([California License]@row), "MMAEAST-ASSET-RET", " ")
I get an #UNPARSEABLE error right from the jump, very discouraging to even continue.
Any help or direction is greatly appreciated. Thank you.
Best Answer
-
Hi Christina C,
Please use the following formula for the first condition condition -
=IF(AND([Practice Group or Division]@row = "Asset Management", ISBLANK([California License]@row)), "MMAEAST-ASSET-RET", "")
And for the all conditions use following formula-
=IF(AND([Practice Group or Division]@row = "Asset Management", ISBLANK([California License]@row)), "MMAEAST-ASSET-RET", IF(AND([Practice Group or Division]@row = "Asset Management", NOT(ISBLANK([California License]@row))), "MMAEAST-ASSET-RET-CA", IF(AND([Practice Group or Division]@row <> "Retirement Services", NOT(ISBLANK([California License]@row))), "MMAEAST-CA", IF(AND([Practice Group or Division]@row = "Retirement Services", ISBLANK([California License]@row)), "MMAEAST-RET", IF(AND([Practice Group or Division]@row = "Retirement Services", NOT(ISBLANK([California License]@row))), "MMAEAST-RET-CA", IF(AND([Practice Group or Division]@row <> "Asset Management", ISBLANK([California License]@row)), "MMAEAST-STANDARD", IF(AND([Practice Group or Division]@row <> "Retirement Services", ISBLANK([California License]@row)), "MMAEAST-STANDARD", IF(AND([Practice Group or Division]@row <> "Asset Management", NOT(ISBLANK([California License]@row))), "MMAEAST-CA", ""))))))))
Thank You!
Shubham Umale
Associate - Smartsheet, Ignatiuz Software
Answers
-
Hi Christina C,
Please use the following formula for the first condition condition -
=IF(AND([Practice Group or Division]@row = "Asset Management", ISBLANK([California License]@row)), "MMAEAST-ASSET-RET", "")
And for the all conditions use following formula-
=IF(AND([Practice Group or Division]@row = "Asset Management", ISBLANK([California License]@row)), "MMAEAST-ASSET-RET", IF(AND([Practice Group or Division]@row = "Asset Management", NOT(ISBLANK([California License]@row))), "MMAEAST-ASSET-RET-CA", IF(AND([Practice Group or Division]@row <> "Retirement Services", NOT(ISBLANK([California License]@row))), "MMAEAST-CA", IF(AND([Practice Group or Division]@row = "Retirement Services", ISBLANK([California License]@row)), "MMAEAST-RET", IF(AND([Practice Group or Division]@row = "Retirement Services", NOT(ISBLANK([California License]@row))), "MMAEAST-RET-CA", IF(AND([Practice Group or Division]@row <> "Asset Management", ISBLANK([California License]@row)), "MMAEAST-STANDARD", IF(AND([Practice Group or Division]@row <> "Retirement Services", ISBLANK([California License]@row)), "MMAEAST-STANDARD", IF(AND([Practice Group or Division]@row <> "Asset Management", NOT(ISBLANK([California License]@row))), "MMAEAST-CA", ""))))))))
Thank You!
Shubham Umale
Associate - Smartsheet, Ignatiuz Software
-
@Shubham - Many thanks! It worked! I was lost in this one. Appreciate your assistance.
-
Hello @Shubham ! You helped with this formula last month and it has working wonderfully! I was asked to include one more condition that checks a value in another column "Union Bug" and if the value is "Yes" it returns the value "Union Bug". If the value is "No" then it follows the formula you provided. I thought I could just insert an IF at the start of the formula with an additional parenthesis at the end but I am getting an "Unparsable" error.
Here is the edited formula where I included the new logic in the beginning, which then continues into your formula if the initial IF statement is False.
=IF([Union Bug]@row = “Yes”, “Union Bug”, IF(AND([Practice Group or
Division]@row = "Asset Management", ISBLANK([California License]@row)),
"MMAEAST-ASSET-RET", IF(AND([Practice Group or
Division]@row = "Asset Management", NOT(ISBLANK([California License]@row))),
"MMAEAST-ASSET-RET-CA", IF(AND([Practice Group or
Division]@row <> "Retirement Services", NOT(ISBLANK([California License]@row))),
"MMAEAST-CA", IF(AND([Practice Group or
Division]@row = "Retirement Services", ISBLANK([California License]@row)),
"MMAEAST-RET", IF(AND([Practice Group or
Division]@row = "Retirement Services", NOT(ISBLANK([California License]@row))),
"MMAEAST-RET-CA", IF(AND([Practice Group or
Division]@row <> "Asset Management", ISBLANK([California License]@row)),
"MMAEAST-STANDARD", IF(AND([Practice Group or
Division]@row <> "Retirement Services", ISBLANK([California License]@row)),
"MMAEAST-STANDARD", IF(AND([Practice Group or
Division]@row <> "Asset Management", NOT(ISBLANK([California License]@row))),
"MMAEAST-CA", "")))))))))
I really thought this would work, what did I do wrong? If you can help I would greatly appreciate it. Thank you!
-
@Shubham - Please ignore my previous message. It looks like it now works. I may have missed a comma or had an extra space somewhere. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!