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!