Multiple if / and / istext
Hello Everyone
I am finding a way to fit a new argument
this is my current formula:
=IF(ISBLANK([Logistics Validation]1), "New", IF([Logistics Validation]1 = "invalid", "Closed", IF(AND(ISTEXT([Logistics Personnel - Assigned]1), [Logistics Validation]1 = "Valid"), "In-Progress")))
need to add this arguments,
- logistics validation = "valid"+ logistics personnel assigned (istext) + logistics provider ( istext) = "Assigned"
- logistics validation = "valid"+ logistics personnel assigned (istext) + logistics provider ( istext) +3pl responses (istext) ="re-open"
- logistics validation = "valid"+ logistics personnel assigned (istext) + logistics provider ( istext) +3pl responses (istext) + corrective action(istext)+resolution(istext) = "closed"
i am currently stuck on my formula above and can no longer proceed to add the last 3 arguments.
thank you for your assistance
Answers
-
Hi Archie,
You're stuck because your 3rd IF in your formula is already "logistics validation = "valid"+ logistics personnel assigned (istext)". If Yes, you display "In Progress". If not means either one of them is not a text or logistics validation is not valid.
Thus you cannot go on with your arguments as they are already check in this statement.
What you need is to add them in the YES part of this IF statement like:
=IF(ISBLANK([Logistics Validation]@row), "New", IF([Logistics Validation]@row = "invalid", "Closed",IF(AND(ISTEXT([Logistics Personnel - Assigned]@row), [Logistics Validation]@row = "Valid"), IF(ISTEXT([Logistics Provider]@row), IF(ISTEXT([3PL Responses]@row),IF(AND(ISTEXT([Corrective Action]@row), ISTEXT([Resolution]@row)), "Closed"),"Re-Open"),"Assigned"),In-Progress")))
So here you check every possibilities to the last one, and apply Closed if they are all true, then you go backwards if they aren't closing IF statements each time.
Hope it helped.
-
mistake comment.
-
thanks David.
Here what i did.
=IF(AND(ISTEXT([Logistics Personnel - Assigned]1), ISTEXT([Logistics Provider]1), ISTEXT([3PL Responses]1), ISTEXT([Corrective Action]1), ISTEXT(Resolution1), [Logistics Validation]1 = "Valid"), "Closed", IF(AND(ISTEXT([Logistics Personnel - Assigned]1), ISTEXT([Logistics Provider]1), ISTEXT([3PL Responses]1), [Logistics Validation]1 = "Valid"), "Re-Open", IF(AND(ISTEXT([Logistics Personnel - Assigned]1), ISTEXT([Logistics Provider]1), [Logistics Validation]1 = "Valid"), "Assigned to 3PL", IF(AND(ISTEXT([Logistics Personnel - Assigned]1), [Logistics Validation]1 = "Valid"), "In-Progress", IF(ISBLANK([Logistics Validation]1), "New", IF([Logistics Validation]1 = "Invalid", "Closed"))))))
think we had the same
thank you
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!