Is there anyway to shorted this formula, using a macro

[noirnor]

Ok, I have a pull down list setup, in which is linked to a cell. From
that cell is 3 different items that get created from it. I have create
If statements to do the comparison's. But the If statement can only be
7 It's long. And I have a few selection list's that are 30 to 40 long,
and one that is 200 long.

Basically it this is the selection and the other categories that pop
up do to what the select.





Selected-Selection P/N Desc Sku Old P/N
EVO W4000 W40 W4000- 1 w40


The equation looks like this.

=IF(GX803=HB900,HC900,IF(GX803=HB901,HC901,IF(GX803=HB902,HC902,IF(GX803=HB903,HC903,IF(GX803=HB904,HC904,IF(GX803=HB905,HC905,IF(GX803=HB906,HC906,0)))))))

I have about 30 cells long strung back to back with these, due only
being able to have 7 if statements in one equation.

And it has turned out to be a night mare.

I have had to create 9 equations like this, then connect it to another
equation like

=IF(HO832=0,IF(HN832=0,IF(HM832=0,IF(HL832=0,IF(HK832=0,IF(HJ832=0,IF(HI832=0,IF(HH832=0,HG832,HH832),HI832),HJ832),HK832),HL832),HM832),HN832),HO832)

that is attached to another...

=IF(HF832=0,IF(HE832=0,IF(HD832=0,IF(HC832=0,IF(HB832=0,IF(HA832=0,IF(GZ832=0,IF(GY832=0,GX832,GY832),GZ832),HA832),HB832),HC832),HD832),HE832),HF832)

This equations the way I have it set up will allow me to have a list of
567, but I have to create the equation, 81 times just for P/N, 81 times
just for desc... so on.

Is there a easier, and more resourceful friendly way?

Noirnor

 

[John Wilson]

noirnor,

A lookup table would probably be the best way to go.
Take a look here for some examples:
http://www.cpearson.com/excel/lookups.htm#

Post back with more specifics on what you're trying to
do.

John

 

[J.E. McGimpsey]

Usually when you get up to 4-5 nested ifs, there's an easier way of
doing the job. In this case, replace the 30 instances of

=IF(GX803=HB900,HC900,IF(GX803=HB901,HC901,IF(GX803=HB902,HC902,IF
(GX803=HB903,HC903,IF(GX803=HB904,HC904,IF(GX803=HB905,HC905,IF(GX
803=HB906,HC906,0)))))))

with

=IF(ISNA(MATCH(GX803, HB900:HB1100, FALSE)),0,VLOOKUP(GX803,
HB900:HC1100, 2, FALSE))

which attempts to match the value in GX803 in the range
HB900:HB1100. If the value is found, return the corresponding value
in HC900:HC1100, otherwise (i.e., MATCH() returns "#N/A"), return 0.

 

[noirnor]

Thank You so much. I have learned something today.