Help needed to Create Formulae

[abdulsalam.abdullah]

Hello!,


I have the following in sheet1

Range D4 and Range E4 have validation list.

Range D4 picks the value from a list which is formatted as text and can
have *,1, 2, 3, 100, 101 thru 400.

Range D4 can have *, G, N

I have a formula in say G4 based on the value in D4 and E4.

I want to apply the following conditions.


If D4 = "*" And E4 = "*" Then
Formula 1
If D4 = "*" And E4 = 1 Or E4 = 2 Or E4 = 3 Then
Formula 2
If D4 = "*" And E4 <> 1 Or E4 <> 2 Or E4 <> 3 Or E4 <> "*" Then
Formula 3
If D4 <> "*" And E4 = "*" Then
Formula 4
If D4 <> "*" And E4 = 1 Or E4 = 2 Or E4 = 3 Then
Formula 5
If D4 <> "*" And E4 <> 1 Or E4 <> 2 Or E4 <> 3 Or E4 <> "*" Then
Formula 6

When I used if .. Elseif ..Endif my formula do not work!

Ho can I make this using VBA?

Is ther any conflict in my conditions there?

Thanks

 

[Ardus Petus]

If d4 = "*" Then
If b4 = "*" Then
Formula 1
ElseIf b4 = 1 Or b4 = 2 Or b4 = 3 Then
Formula 2
Else
Formula 3
End If
Else
If b4 = "*" Then
Formula 4
ElseIf b4 = 1 Or b4 = 2 Or b4 = 3 Then
Formula 5
Else
Formula 6
End If

End If

Cordialement,

 

[Glen Mettler]

Try using Case

Case D4 = "*" And E4 = "*"
Formula 1
Case D4 = "*" And E4 = 1 Or E4 = 2 Or E4 = 3
Formula 2
Case D4 = "*" And E4 <> 1 Or E4 <> 2 Or E4 <> 3 Or E4 <> "*"
Formula 3
Case D4 <> "*" And E4 = "*"
Formula 4
Case D4 <> "*" And E4 = 1 Or E4 = 2 Or E4 = 3
Formula 5
Case D4 <> "*" And E4 <> 1 Or E4 <> 2 Or E4 <> 3 Or E4 <> "*"
Formula 6
End Case

This will also work if you put it in G4 and fill down

=IF(AND($D4="*",$E4="*"),"Formula1",IF(AND($D4="*",$E4<4,$E4<>"*"),"Formula2",IF(AND($D4="*",$E4>4,),"Formula3",IF(AND($D4="*",$E4>1),"Formula4",IF(AND($D4="G",$E4<=3),"Formula5",IF(AND($D4<>"*",$E4<>"*",$E4>3),"Formula6"))))))

Glen