Multi Nested IF/AND Formula

Christina C

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.

Shubham

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

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

Christina C

@Shubham - Many thanks! It worked! I was lost in this one. Appreciate your assistance.

Christina C

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!

Christina C

@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!