[SOLVED] 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.

"K.W.Martens" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

"K.W.Martens" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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

>
> "K.W.Martens" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

/Fredrik


"K.W.Martens" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> >
> > "K.W.Martens" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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.

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

/Fredrik

"K.W.Martens" <[email protected]> wrote in message
news:[email protected]...
> 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.