nesting logical functions

  • Thread starter Thread starter Plum
  • Start date Start date
P

Plum

How does one get around the 7 argument limit? I am
attempting to create a model number configurator in Excel
2003, which will provide a Letter in one cell, if a user
chooses a description from a drop-down list in another
cell. With lists of 7 entries or less, I am successfull
using =IF(B3=C8,"A",IF(B3=C9,"B"... Where column B3 is
the drop down list with the description,C8 is the
original list form which the DD is based, and "A","B"...
is a choosen text. PROVIDED I do not go beyond 7
arguments. Is there some way to break the description
list into 2 or more nested arguments so that I can make
8,9,n arguments work?
 
How does one get around the 7 argument limit? I am
attempting to create a model number configurator in Excel
2003, which will provide a Letter in one cell, if a user
chooses a description from a drop-down list in another
cell. With lists of 7 entries or less, I am successfull
using =IF(B3=C8,"A",IF(B3=C9,"B"... Where column B3 is
the drop down list with the description,C8 is the
original list form which the DD is based, and "A","B"...
is a choosen text. PROVIDED I do not go beyond 7
arguments. Is there some way to break the description
list into 2 or more nested arguments so that I can make
8,9,n arguments work?
Click to expand...

I would use a different logic/algorithm.

For example, if your chosen text is in D8:Dn, then

=INDEX(D8:Dn,MATCH(B3,C8:Cn,0))

or something similar might do the trick.


--ron
 
It's probably best to use something like the Vlookup function.

Since you're already utilizing the data list that populates the drop down
menu, just add an adjoining column containing your "chosen text".

For example:
C8 = data8
C9 = data9
C10 = data10
....etc.

Now add:
D8 = A
D9 = B
D10 = C
....etc.

So you end up with a data list of perhaps
C8:D20

You now *replace* your "If" formula with Vlookup:

=Vlookup(B3,$C$8:$D$20,2,0)
And you can copy down as needed.

You could of course place your data list in any "out of the way" portion of
your sheet, or even on another sheet.
If you do use another sheet for the list, just reference it in the formula,
such as:
=VLOOKUP(B3,Sheet2!$C$8:$D$20,2,0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


How does one get around the 7 argument limit? I am
attempting to create a model number configurator in Excel
2003, which will provide a Letter in one cell, if a user
chooses a description from a drop-down list in another
cell. With lists of 7 entries or less, I am successfull
using =IF(B3=C8,"A",IF(B3=C9,"B"... Where column B3 is
the drop down list with the description,C8 is the
original list form which the DD is based, and "A","B"...
is a choosen text. PROVIDED I do not go beyond 7
arguments. Is there some way to break the description
list into 2 or more nested arguments so that I can make
8,9,n arguments work?
 
-----Original Message-----


I would use a different logic/algorithm.

For example, if your chosen text is in D8:Dn, then

=INDEX(D8:Dn,MATCH(B3,C8:Cn,0))

or something similar might do the trick.


--ron
.
Click to expand...
Incredible! I've spent the better part of my evening
trying to go it alone (yes, enter "stubborn"),and all I
had to do was ask for help. Thanks for the help.
 
Incredible! I've spent the better part of my evening
trying to go it alone (yes, enter "stubborn"),and all I
had to do was ask for help. Thanks for the help.
Click to expand...

You're welcome. But I've found that being "stubborn" sometimes leads me to new
insights :-).

There are frequently many ways to skin a cat in Excel.
--ron
 
Back
Top