Too Many Nested IF Statements. What are My Options?

[madkinson]

We have an Excel spreadsheet where we log in actions taken on certain service tickets. This so-called “Master Data” log is a required SOX control, so accuracy is very important. I wrote a series of nested IF statements using the following logic.

At the top of the hierarchy are the elements:

• Account
• Business Unit
• Department
• Operating Unit
• Other
• Product
• Project
• Rule
• Service Date
• Tree

Under each element, there are 2 levels. The next level is “Action”. Each Element could have one of the following as a condition:

• Action
• Activated
• Added
• Changed
• Created
• Duplicated
• Modified
• Removed

Under each of the Actions would be a Yes/No (is the change permanent?)

The results produce a line of text something like this:

Removed Account 21501602 from the ACCT_NO_OU, GENERAL_AND_ADMIN node of the ZCMB_ACCOUNTS tree.
Removed 04/05/16 03:00 PM and restored 04/07/16 07:45 AM.

where the values are pulled from specified cells in the worksheet below as follows (I had to cut some columns so it would fit, code selected is in BOLD):
=IF('Master Data'!C1072="Account",
IF('Master Data'!T1072="Activated",
('Master Data'!S1072&" "&'Master Data'!J1072&".
Activated "&TEXT('Master Data'!V1072,"mm/dd/yy hh:mm AM/PM")&"."),
IF('Master Data'!T1072="Added",
('Master Data'!S1072&" "&'Master Data'!J1072&" to the "&'Master Data'!Q1072&" node of the "&'Master Data'!P1072&" tree.
Added "&TEXT('Master Data'!V1072,"mm/dd/yy hh:mm AM/PM")&"."),
IF('Master Data'!T1072="Created",
('Master Data'!S1072&" "&'Master Data'!J1072&".
Created "&TEXT('Master Data'!V1072,"mm/dd/yy hh:mm AM/PM")&"."),
IF('Master Data'!T1072="Removed",
IF('Master Data'!S1072="Permanently removed account",
('Master Data'!S1072&" "&'Master Data'!J1072&" from the "&'Master Data'!Q1072&" node of the "&'Master Data'!P1072&" tree.
Removed "&TEXT('Master Data'!V1072,"mm/dd/yy hh:mm AM/PM")&".")),
('Master Data'!S1072&" "&'Master Data'!J1072&" from the "&'Master Data'!Q1072&" node of the "&'Master Data'!P1072&" tree.
Removed "&TEXT('Master Data'!V1072,"mm/dd/yy hh:mm AM/PM")&" and restored "&TEXT('Master Data'!W1072,"mm/dd/yy hh:mm AM/PM")&"."),
('Master Data'!S1072&" "&'Master Data'!J1072&" on the "&'Master Data'!Q1072&" node of the "&'Master Data'!P1072&" tree.
Changed "&TEXT('Master Data'!V1072,"mm/dd/yy hh:mm AM/PM")&" and restored "&TEXT('Master Data'!W1072,"mm/dd/yy hh:mm AM/PM")&".")))),

The IF statements are unwieldy and I’ve reached the maximum number of nested statements. I would like to use the Select CASE Statement functionality, but I don’t understand it well enough to get a working statement.

Thanking you in advance,
Mark

Excel 2013

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Master Data Element	Value​	Project​	Account​	Operating Unit​	Tree​	Node​
2​	**Account	21501602​	​	21501602​	​	**ZCMB_ACCOUNTS	**ACCT_NO_OU, GENERAL_AND_ADMIN
3​	**Account	21501603​	​	21501603​	​	**ZCMB_ACCOUNTS	**ACCT_NO_OU
4​	**Account	60099008​	​	60099008​	​	**ZCMB_ACCOUNTS	**ACT_NO_DEPT
5​	**Account	21002416​	​	21002416​	​	**	**

Sheet: Master Data

Excel 2013

Row\Col	H​	I​	J​	K​	L​	M​
1​	Notes​	Action​	Permanent?​	Date/Time Changed​	Date/Time Restored​	Service Now Resolution
2​	**Removed Account	**Removed	No​	**4/5/16 3:00 PM​	**4/7/16 7:45 AM​	Removed rule ACTOU0577 from Ledgers For a Unit for BU 00577. Removed 01/04/16 11:20 AM and restored 01/04/16 11:35 AM.
3​	**Removed Account	**Removed	No​	**4/5/16 3:00 PM​	**4/7/16 7:45 AM​	Removed rule ACTOU00340 from Ledgers For a Unit for BU 00340. Removed 01/04/16 11:20 AM and restored 01/04/16 11:35 AM.
4​	**Created new Account	**Created	Yes​	**4/5/16 5:16 PM​	**N/A​	Removed rule ACTOU0177 from Ledgers For a Unit for BU 00177. Removed 01/04/16 11:20 AM and restored 01/04/16 11:35 AM.
5​	**Removed control flag	**Removed	No​	**4/6/16 11:20 AM​	**4/6/16 12:31 PM​	Removed rule ACTOU0088 from Ledgers For a Unit for BU 00088. Removed 01/04/16 11:20 AM and restored 01/04/16 11:35 AM.

Sheet: Master Data

 

[Joe4]

I would definitely go with creating your own function in VBA. They are much easier to read and maintain than unwieldy formulas.

See if this gives you a good start on CASE statements: MS Excel: How to use the CASE Statement (VBA)

 

[madkinson]

I would definitely go with creating your own function in VBA. They are much easier to read and maintain than unwieldy formulas.

See if this gives you a good start on CASE statements: MS Excel: How to use the CASE Statement (VBA)

This is what I have so far:

Sub Master_Data_Text()
    Application.ScreenUpdating = False
    With ActiveSheet

    Select Case Element
        Case "Account"
            Select Case Action
                Case "Activated"
                    Select Case Permanent
                        Case "Yes"
                            .Range("Y1") = ' the text I will create later
                        Case Else
                            .Range("Y1") = ' alternate text
                    End Select
                Case "Added"
                        Case "Yes"
                            .Range("Y1") = ' the text I will create later
                        Case Else
                            .Range("Y1") = ' alternate text
                    End Select
                Case "Changed"
                        Case "Yes"
                            .Range("Y1") = ' the text I will create later
                        Case Else
                            .Range("Y1") = ' alternate text
                    End Select
                Case Else
                
                End Select
            End Select
        Case "Business Unit"
            
        ' repeat as Above for each Element and Action
    End Select
End With
    Application.ScreenUpdating = True
End Sub

Am I on the right track?

 

[Joe4]

Looks like that should work.