Multiple IF situations
 

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.

Multiple IF situations
 

"K.W.Martens" wrote in message
...
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/exc...tediffunct.htm

/ Fredrik

Multiple IF situations
 

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" wrote:


"K.W.Martens" wrote in message
...
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/exc...tediffunct.htm

/ Fredrik

Multiple IF situations
 

"K.W.Martens" wrote in message
...
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

Multiple IF situations
 

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)


"K.W.Martens" wrote in message
...
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" wrote:


"K.W.Martens" wrote in message
...
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/exc...tediffunct.htm

/ Fredrik

Multiple IF situations
 

Here is the nested situation example:
=IF(AND(B25="Soccer",D25="English",E25="Standard") ,"123456789",(IF(AND(B25="Hockey",D25="English",E2 5="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="Standa rd"),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" wrote:


"K.W.Martens" wrote in message
...
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

Multiple IF situations
 

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

/Fredrik


"K.W.Martens" wrote in message
...
Here is the nested situation example:

=IF(AND(B25="Soccer",D25="English",E25="Standard") ,"123456789",(IF(AND(B25="
Hockey",D25="English",E25="Standard"),234567890,(I F(AND(B25="Football",D25="
English",E25="Standard"),345678901,(IF(AND(B25="Ba seball",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" wrote:


"K.W.Martens" wrote in message
...
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

Multiple IF situations
 

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



"K.W.Martens" wrote:

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.

Multiple IF situations
 

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

/Fredrik

"K.W.Martens" wrote in message
...
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



"K.W.Martens" wrote:

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.