Need help to unravel nested if statement in formula. (fomula to VBA code)

[Richard U]

I have this formula

=IF(OR((IF(‘Sheet 1’!AJ6=0;""; ‘Sheet 1'!AJ6))="";(IF(‘Sheet 1'!AJ6=0;""; ‘Sheet 1'!AJ6))=" ");"~";IF(LEFT(‘Sheet 1'!AM6;3)="940";"RSW INPUT";IF(LEFT(‘Sheet 1'!AM6;3)="401";"REU INPUT";IF(LEFT(‘Sheet 1'!AM6;3)="200";"ANZ INPUT";IF(RIGHT($M6;2)="MX";"#N/A";IF(‘Sheet 1'!AN6="COMPANY NAME";"RNA INPUT";VLOOKUP(E6&" "&(IF‘Sheet 1'!AJ6=0;"";'Portal Data'!AJ6));Sarep!$B$4:$C$291;2;"FALSE")))))))


Which I need to translate into some maintainable VBA code, but I can't quite figure out what this is doing. I'm a good coder, but the nested excel formulas confuse me.


Could someone please explain what this formula is doing? I can write the code myself once I understand the logic, but I can't seem to unravel this myself

 

[cerfani]

You have a few syntax errors... you are using semi colons instead of commas?? You are missing a parentheses on an If function.

If you want to clean it up, you should remove the extra parentheses you have around IF functions. You are doing this...

(IF(...))

Stuff like that is making it harder to read.

 

[Richard U]

It's not my formula

 

[craigexcel]

To the contrary, in the context of this forum, it's defintely your formula. As cerfani suggests, I would try to clean up the formula as much as possible, so it's easier to read, and then repost. You're more likely to get feedback as a result.

 

[cerfani]

maybe this will help you... i fixed the syntax error... i separated all the nested functions to look more like vba code... you have the boolean test on first line then the next line is the true result and after that the false result which leads to more nested IFs

 

[cerfani]

The IFs in that OR function were kinda messy...

just fyi: the value "" is different that the value " " ... a space is a unique character... this is an empty string value "" ... this is not empty " "

 

[thisoldman]

There misplaced parentheses in the IF following the VLOOKUP and unneeded quotation marks. I think this is how that subformula should be entered:
VLOOKUP(E6 & " " & IF(‘Sheet 1'!AJ6=0; ""; 'Portal Data'!AJ6); Sarep!$B$4:$C$291; 2; FALSE)

The OR() at the beginning doesn't make sense to me:
OR((IF(‘Sheet 1’!AJ6=0;""; ‘Sheet 1'!AJ6))=""; (IF(‘Sheet 1'!AJ6=0; ""; ‘Sheet 1'!AJ6))=" ")

I think this is meant to test if 'Sheet 1'AJ6 is a zero, a blank, an empty string, or a space. If I am correct, we can replace it with
OR('Sheet 1'AJ6=0; 'Sheet 1'AJ6=""; 'Sheet 1'AJ6=" ")

Formatted to give some structure:

=IF(OR('Sheet 1'AJ6=0; 'Sheet 1'AJ6=""; 'Sheet 1'AJ6=" ");
    "~";
    IF(LEFT(‘Sheet 1'!AM6;3)="940";
      "RSW INPUT";
      IF(LEFT(‘Sheet 1'!AM6;3)="401";
        "REU INPUT";
        IF(LEFT(‘Sheet 1'!AM6;3)="200";
          "ANZ INPUT";
          IF(RIGHT($M6;2)="MX";
            "#N/A";
            IF(‘Sheet 1'!AN6="COMPANY NAME";
              "RNA INPUT";
              VLOOKUP(E6 & " " & IF(‘Sheet 1'!AJ6=0; ""; 'Portal Data'!AJ6); Sarep!$B$4:$C$291; 2; FALSE)))))))

That should help.

 

[Richard U]

second try thanks for the input.

=IF(OR(IF('Portal Data'!AJ6=0,"",'Portal Data'!AJ6)="",IF('Portal Data'!AJ6=0,"",'Portal Data'!AJ6)=" "),VLOOKUP(AH6,Sarep!$G$4:$H$500,2,"FALSE"),IF(LEFT('Portal Data'!AM6,3)="940","ACO",IF(RIGHT($M6,2)="MX","#N/A",IF(OR(LEFT('Portal Data'!AM6,3)="401",LEFT('Portal Data'!AM6,3)="200",'Portal Data'!AN6="Company Name"),"SFC100",IF(J6="INPUT","RNA",VLOOKUP(E6&" "& IF('Portal Data'!AJ6=0,"",'Portal Data'!AJ6),Sarep!$B$4:$D$500,3,"FALSE"))))))

 

[Richard U]

Wow, thanks to all of you! THANK YOU SO MUCH!

 

[cerfani]

Regarding the OR function, it can be optimized...

OR(IF(‘Sheet 1’!AJ6=0,"", ‘Sheet 1'!AJ6)="",IF(‘Sheet 1'!AJ6=0,"", ‘Sheet 1'!AJ6)=" ")

can instead be...

‘Sheet 1’!AJ6=0

you notice it will result in true, if the IF results in "" which would occur if ‘Sheet 1’!AJ6=0 was true... so you can skip the extra steps and just check... ‘Sheet 1’!AJ6=0