Multiple IF situations

[Guest]

I'm stumped....What I and trying to do is set up multple IF situations from
drop down lists. As an example:
=IF(AND(B25="Hockey",D25="English",E25="Standard"),"123456789","Special")...now
I want to say the IF it different it will be this, but IF it's different
again it will be this...and so on. I have already 48 circumstances. If I
knew VB, I'm sure there is a more realistic way to do this.

Any assistance will be greatly appreciated.

 

[Fredrik Wahlgren]

I'm stumped....What I and trying to do is set up multple IF situations from
drop down lists. As an example:
=IF(AND(B25="Hockey",D25="English",E25="Standard"),"123456789","Special")...
now
I want to say the IF it different it will be this, but IF it's different
again it will be this...and so on. I have already 48 circumstances. If I
knew VB, I'm sure there is a more realistic way to do this.

Any assistance will be greatly appreciated.

It seems as if you want to do nested IF's. Unfortunately, you can't have
more than 7 If statements in a cell. This can be done easily in VBA. See the
links below. For more links google Excel nested if

http://www.cpearson.com/excel/nested.htm
http://spreadsheets.about.com/cs/excelfunction1/a/nestediffunct.htm

/ Fredrik

 

[Guest]

Thanks for the info, but, how can I do it in VBA.....your comment "this can
be done easily" intriqued me. Any suggestions would be appreciated. Another
question....I don't really know if I am looking at a "nesting" situation.
Each circumstance within the cell is determined by the drop down lists. So,
as an example, instead of "Hockey" in B25, it was "Soccer" it would give a
different result. It seems to me that I have an IF/OR situation.....but, I
still haven't found an answer.

 

[Fredrik Wahlgren]

Thanks for the info, but, how can I do it in VBA.....your comment "this can
be done easily" intriqued me. Any suggestions would be appreciated. Another
question....I don't really know if I am looking at a "nesting" situation.
Each circumstance within the cell is determined by the drop down lists. So,
as an example, instead of "Hockey" in B25, it was "Soccer" it would give a
different result. It seems to me that I have an IF/OR situation.....but, I
still haven't found an answer.

I missed the fact that you used these if statments in a drop down list. What
I meant is that it's easy and recommendable to replace complex logic in a
cell with a user defined function. I don't know if you can call such a
function from a drop down list but I guess it should be possible since you
can use IF. Do you understand how to create the nested functions. If you do,
you can post them to this newsgroup and ask for an equivalent implementation
under VBA.

/Fredrik

 

[Bob Phillips]

Sub Test

With ACtivesheet
B25 = .Range("B25").Value
D25 = .Range("D25").Value
E25 = .Range("E25").Value
If B25="Hockey" And D25="English" And E25="Standard" Then
.Range ("F25").Value = "123456789"
ElseIf B25="Soccer" And D25="English" And E25="Standard" Then
.Range ("F25").Value = "abcd"
ElseIf B25="Soccer" And D25="French" And E25="Standard" Then
.Range ("F25").Value = "xyz"
'etc...
End If
End With

End Sub

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Here is the nested situation example:
=IF(AND(B25="Soccer",D25="English",E25="Standard"),"123456789",(IF(AND(B25="Hockey",D25="English",E25="Standard"),234567890,(IF(AND(B25="Football",D25="English",E25="Standard"),345678901,(IF(AND(B25="Baseball",D25="English",E25="Standard"),456789012,(IF(AND(B25="Basketball",D25="English",E25="Standard"),567890123,(IF(AND(B25="Racing",D25="English",E25="Standard"),678901234,(IF(AND(B25="Wrestling",D25="English",E25="Standard"),789012345,"Special")))))))))))))

Now.....This has to apply to more variables in the named cells, as well as
has to be able to be allocated to 11 more rows....there is a total (at
present) of 596 different scenerios.....HELP please

 

[Fredrik Wahlgren]

Hmm. Do you know how to create a simple function using VBA? I'm currently
busy with other things.

/Fredrik

 

[Guest]

SOLVED....

Thanks to everyone, I have managed to figure it out using VBA....sample code:
Function upccaculator(games, languages, advertising)
Select Case games
Case "Soccer"
Select Case languages
Case "English"
Select Case advertising
Case "Standard"
upccode = "873794000217"
Case "Custom"
upccode = "Custom"
End Select
Case "French"
Select Case advertising
Case "Standard"
upccode = "873794000248"
Case "Custom"
upccode = "Custom"
End Select
Case "Spanish"
Select Case advertising
Case "Standard"
upccode = "873794000224"
Case "Custom"
upccode = "Custom"
End Select
End Select

......so forth..........................

It works

 

[Fredrik Wahlgren]

Excellent. I hope you had some fun while doing so. This is more readable and
far less error prone.

/Fredrik