Formula Help

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Can anyone tell me what might be wrong with this formula? The second half is
not working...or maybe if there is an easier VB way to do this!

=IF(OR(F7=0,AND(E7="vg_2x1_1",E7="vg_2x1_2",E7="vg_2x1_3")),$P$9,IF(OR(F7=0,AND(E7="vg_2x2_1",E7="vg_2x2_2",E7="vg_2x2_3")),$P$13,"nope"))

Any assistance will be greatly appreciated. Thanks in advance for your time...
 
one thing

this

AND(E7="vg_2x1_1",E7="vg_2x1_2",E7="vg_2x1_3")

must always be FALSE since you're trying to AND the same cell with different
values. use OR to return a TRUE if E7 is equal to any one of those values
 
try this fix

=IF(AND(F7=0,OR(E7="vg_2x1_1",E7="vg_2x1_2",E7="vg_2x1_3")),$P$9,IF(AND(F7=0,OR(E7="vg_2x2_1",E7="vg_2x2_2",E7="vg_2x2_3")),$P$13,"nope"))
 
That worked perfect....now, I have added this to another portion of a formula
and the last part (very last "If") of this again does not seem to work? Could
it be to long?

=IF(AND(F9=0,OR(E9="vg_2x1_1",E9="vg_2x1_2",E9="vg_2x1_3")),$P$9,IF(AND(F9=0,OR(E9="vg_2x2_1",E9="vg_2x2_2",E9="vg_2x2_3")),$P$13,IF(AND(F9=35,D9=2),$K$9,IF(AND(F9=45,D9=2),$K$10,IF(AND(F9=60,D9=2),$K$11,IF(AND(F9=35,D9=3),$K$16,IF(AND(F9=45,D9=3),$K$17,IF(AND(F9=60,D9=3),$K$18,"nope")))))))
 
If you are using 2003 you may only use 7 nested IF statements...

If this post helps click Yes
 
7 IF's used to be the max, but your formula is pig ugly (apologies to pig
fans everywhere )! lol

You could create a UDF - A User Defined Function, that could be quite elegent.

something akin to this maybe

Option Explicit
Public Function MyUdf(Esource As Range, Fsource As Range)
Dim res As String
If Range("F9") <> 1 Then
MyUdf = "nope"
Exit Function
End If

Select Case Esource.Value
Case "vg_2x1_1", E9 = "vg_2x1_2", E9 = "vg_2x1_3"
res = Range("P13")
Case "vg_2x2_1", E9 = "vg_2x2_2", E9 = "vg_2x2_3"
res = Range("K16")
End Select

MyUdf = res

End Functio
 
That's what I thought....that's a bummer cause I just needed one
more....thanks for the clarification...will have to try another method.
 
Yes...Thanks Patrick...that was going to be my next method...I will play
around with what you provided and see what I can come up with.

Thanks again for your help with this!
 
Back
Top