Need Help w/ Complicated Nested IF, AND, and SUBSTITUTE Statements

I need help with a nested IF AND statement that also uses the SUBSTITUTE formula.

I’m trying to automate project ID creation. Each project is tied to a client and can also (optionally) be tied to a different sub-entity.

Depending on who is paying, I either want to use the Client name or the Sub-entity name in the project ID. Additionally, I want to use the abbreviation for each as long as one exists. If an abbreviation doesn’t exist, I want to use the name but remove " " and “,” using the substitute function.

So the scenarios are:

Scenario #1: Payer=“Client” AND Client Abbreviation is Blank, Client Name with " " and “,” removed with SUBSTITUTE function.
Scenario #2: Payer=“Client” AND Client Abbreviation is Not Blank, Client Abbreviation.
Scenario #3: Payer=“Sub-Entity” AND Sub-Entity Abbreviation is Blank, Sub-Entity Name with " " and “,” removed with SUBSTITUTE function.
Scenario #4: Payer=“Sub-Entity” AND Sub-Entity Abbreviation is Not Blank, Sub-Entity Abbreviation.

Any help would be greatly appreciated!

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.