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
[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td=bgcolor:#002060]Master Data Element[/td][td=bgcolor:#002060]

Value​

[/td][td=bgcolor:#002060]

Project​

[/td][td=bgcolor:#002060]

Account​

[/td][td=bgcolor:#002060]

Operating Unit​

[/td][td=bgcolor:#002060]

Tree​

[/td][td=bgcolor:#002060]

Node​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]**Account[/td][td]

21501602​

[/td][td=bgcolor:#CCFFFF]

​

[/td][td=bgcolor:#CCFFFF]

21501602​

[/td][td=bgcolor:#CCFFFF]

​

[/td][td]**ZCMB_ACCOUNTS[/td][td]**ACCT_NO_OU, GENERAL_AND_ADMIN[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]**Account[/td][td]

21501603​

[/td][td=bgcolor:#CCFFFF]

​

[/td][td=bgcolor:#CCFFFF]

21501603​

[/td][td=bgcolor:#CCFFFF]

​

[/td][td]**ZCMB_ACCOUNTS[/td][td]**ACCT_NO_OU[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]**Account[/td][td]

60099008​

[/td][td=bgcolor:#CCFFFF]

​

[/td][td=bgcolor:#CCFFFF]

60099008​

[/td][td=bgcolor:#CCFFFF]

​

[/td][td]**ZCMB_ACCOUNTS[/td][td]**ACT_NO_DEPT[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]**Account[/td][td]

21002416​

[/td][td=bgcolor:#CCFFFF]

​

[/td][td=bgcolor:#CCFFFF]

21002416​

[/td][td=bgcolor:#CCFFFF]

​

[/td][td]**[/td][td]**[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Master Data[/td][/tr][/table]

Excel 2013
[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td=bgcolor:#002060]

Notes​

[/td][td=bgcolor:#002060]

Action​

[/td][td=bgcolor:#002060]

Permanent?​

[/td][td=bgcolor:#002060]

Date/Time Changed​

[/td][td=bgcolor:#002060]

Date/Time Restored​

[/td][td=bgcolor:#002060]Service Now Resolution[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]**Removed Account[/td][td=bgcolor:#FFFF00]**Removed[/td][td=bgcolor:#FFC000]

No​

[/td][td]

**4/5/16 3:00 PM​

[/td][td]

**4/7/16 7:45 AM​

[/td][td]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.[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]**Removed Account[/td][td=bgcolor:#FFFF00]**Removed[/td][td=bgcolor:#FFC000]

No​

[/td][td]

**4/5/16 3:00 PM​

[/td][td]

**4/7/16 7:45 AM​

[/td][td]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.[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]**Created new Account[/td][td=bgcolor:#FFFF00]**Created[/td][td=bgcolor:#FFC000]

Yes​

[/td][td]

**4/5/16 5:16 PM​

[/td][td]

**N/A​

[/td][td]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.[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]**Removed control flag[/td][td=bgcolor:#FFFF00]**Removed[/td][td=bgcolor:#FFC000]

No​

[/td][td]

**4/6/16 11:20 AM​

[/td][td]

**4/6/16 12:31 PM​

[/td][td]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.[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Master Data[/td][/tr][/table]

 

[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.