If nesting... in a true/false - FOUND IT! Remove the 0 in the nesting

Options
Amber Perez
Amber Perez ✭✭
edited 07/14/20 in Formulas and Functions

I can't figure out why combing these statements isn't working.


This works: =IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Lead or Supervisor", [Approvals Needed]@row), CONTAINS("Manager", [Approvals Needed]@row), [Lead or Supervisor Approval]@row = "Approved", [Manager Approval]@row = "Approved"), 1, 0)


and this works: =IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Manager", [Approvals Needed]@row), [Manager Approval]@row = "Approved"), 1, 0)


But this does not work, I get #UNPARSEABLE

=IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Lead or Supervisor", [Approvals Needed]@row), CONTAINS("Manager", [Approvals Needed]@row), [Lead or Supervisor Approval]@row = "Approved", [Manager Approval]@row = "Approved"), 1, 0, IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Manager", [Approvals Needed]@row), [Manager Approval]@row = "Approved"), 1, 0,))

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You also have an extra comma there at the end between the last zero and when you closed off the formula.

    You can also shorten this a touch like so...

    =IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Manager", [Approvals Needed]@row), [Manager Approval]@row = "Approved"), IF(CONTAINS("Lead or Supervisor", [Approvals Needed]@row), IF([Lead or Supervisor Approval]@row = "Approved", 1), 1))

  • Amber Perez
    Options

    Thanks Paul. The problem with the shortened version is that if I only have a Lead or Supervisor and Director, no Manager, it doesn't work. I also sometimes have a Manager, with no Lead or Supervisor, and still need the Director. This is what I have now that seems to be working:

    =IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Lead or Supervisor", [Approvals Needed]@row), CONTAINS("Manager", [Approvals Needed]@row), [Lead or Supervisor Approval]@row = "Approved", [Manager Approval]@row = "Approved"), 1, IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Manager", [Approvals Needed]@row), [Manager Approval]@row = "Approved"), 1, IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Lead or Supervisor", [Approvals Needed]@row), [Lead or Supervisor Approval]@row = "Approved"), 1, 0)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Sorry about that. I was working off of the formula in the original post where you had Manager and Director specified for both of the IF/ANDs. I didn't realize you would need to account for Director with no Manager.


    The shortened version above accounts for

    Director and Manager

    Director and Manager and Lead/Supervisor

    as you have in the original post. Sorry about that.

  • Amber Perez
    Options

    No problem. I appreciate that you took the time to look that closely. Thank you, really!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!