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

Amber Perez

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,))

Paul Newcome

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

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

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

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