Multiple if / and / istext

Archie Villa

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,

1. logistics validation = "valid"+ logistics personnel assigned (istext) + logistics provider ( istext) = "Assigned"
2. logistics validation = "valid"+ logistics personnel assigned (istext) + logistics provider ( istext) +3pl responses (istext) ="re-open"
3. 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

David Joyeuse

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.

David Joyeuse

mistake comment.

Archie Villa

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