If nesting... in a true/false - FOUND IT! Remove the 0 in the nesting
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,))
Answers
-
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))
-
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)))
-
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.
-
No problem. I appreciate that you took the time to look that closely. Thank you, really!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!