Solved: How to nest a Massive IF statement?

[Simon Lloyd]

Hi all i have a nested worksheet formula as below in box 2 (took some time to devise it!!!), the formula works fine but i need to add the line in box 1 to the statement, but i have read that there is a limit of 7 IF's to a statement anyone know a clever way around it?
Box1:
[VBA]=IF(AND(N5=4,K2="Line Off"),"",IF(AND(F5=4,K2="Line Off"),"",IF(AND(B5=4,K2="Line Off"),"",IF(AND(R5=4,K2="Line Off"),"",IF(AND(V5=4,K2="Line Off"),""[/VBA]
Box2:
[VBA]=IF(AND(N5=4,K2="No Ops"),"",IF(AND(F5=4,K2="No Ops"),"",IF(AND(B5=4,K2="No Ops"),"",IF(AND(R5=4,K2="No Ops"),"",IF(AND(V5=4,K2="No Ops"),"",IF(K2="Line Off",B33,IF(K2="No Ops",B33)))))))[/VBA]

Hope you can help!

Regards,
Simon

[mvidas]

Hi Simon,

Your first one looks like it didn't all paste in. As for your second one:

=IF(K2="No Ops",IF(OR(N5=4,F5=4,B5=4,R5=4,V5=4),"",B33),IF(K2="Line Off",B33,"meets no conditions"))

Assuming I understood your logic correctly..
Matt

[Simon Lloyd]

Thanks Matt, the first one didnt paste complete because it was only partial of what i was trying to achieve but couldnt get it to work.........you didn't answer my question but gave me a really nice lesson so this is what i managed after your post!

[VBA]
=IF(G2="No Ops",IF(OR(N5=3,J5=3,B5=3,R5=3,V5=3),"",B32),IF(G2="Line Off",IF(OR(N5=3,J5=3,B5=3,R5=3,V5=3),"",B32),IF(G2="Line Off",B32,"")))
[/VBA]Thanks,

Simon

[Norie]

Simon

Couldn't you use another OR to check G2?

[mvidas]

Also, as for this section of it:
If(G2="Line Off",If(Or(N5=3,J5=3,B5=3,R5=3,V5=3),"",B32),If(G2="Line Off",B32,""))

Wouldn't it be impossible for the bolded B32 there to ever play a part? You're saying "If g2 is 'line off' then ____ else if g2 is 'line off' ..."

Combining Norie's suggestion with this, I suggest:
=IF(OR(G2="No Ops",G2="Line Off"),IF(OR(N5=3,J5=3,B5=3,R5=3,V5=3),"",B32),"")

Or even just:
=IF(and(OR(G2="No Ops",G2="Line Off"),OR(N5=3,J5=3,B5=3,R5=3,V5=3)),"",B32)

[Simon Lloyd]

Gentlemen.....i havent finished the workbook by far but i have attached it so you can see what i am trying to achieve, the worksheet has the protection on but no password..............have a click around before removing the protection and you will see the names move around depending on the states of certain cells.

Regards,
Simon

[Shazam]

I'm guessing is this what you're looking for?


Input formula in cell E30

=IF(OR(AND(C$2="No Ops",$N$5=1,$J$5=1,$F$5=1,$R$5=1,$V$5=1),AND(C$2="Line Off",$N$5=1,$J$5=1,$F$5=1,$R$5=1,$V$5=1)),B30,"")


Hope it helps.

[Simon Lloyd]

Thanks Shazam for your answer but it's not quite what i'm after, if you take a look at the workbook i've uploaded you will see that there are names in various places, on using a dropdown in some cells the names are removed take a look at B2, click it and choose No Ops, you will see the name I Ferris moves from A4 to E30 (Line Ops), now if you enter his index number in either N5, J5, F5, R5, V5 you will see his name move from Line Ops to the required line, this will also be true if you selected Line Off in B2, it would mean he is available for use on other lines. I need this kind of functionality for all the names however, the names highlighted in yellow would only ever move if the 2 lines they work between both show Line Off or No Ops.

As i said i'm quite far from finishing what i envisage the crew sheet doing and there are many more lines to create sheets for, the point of it is that it has to be easy to use and point and click functionality as much as possible, i never use worksheet functions i usually use VBA so this is a first for me as there isn't a single line of code in the project....Yet!!!

Regards,
Simon