Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there anyway to shorted this formula, using a macro
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(GX80 3=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(HK 832=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(HB 832=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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there anyway to shorted this formula, using a macro
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 noirnor wrote: 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(GX80 3=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(HK 832=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(HB 832=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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there anyway to shorted this formula, using a macro
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(GX80 3=HB902,HC902,IF (GX803=HB903,HC903,IF(GX803=HB904,HC904,IF(GX803=H B905,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. In article , noirnor wrote: 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(GX80 3=HB902,HC902,IF(GX803=HB903 ,HC903,IF(GX803=HB904,HC904,IF(GX803=HB905,HC905,I F(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(HK 832=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(HB 832=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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there anyway to shorted this formula, using a macro
Thank You so much. I have learned something today. :)
------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible Macro or formula | Excel Discussion (Misc queries) | |||
Macro formula help | Excel Worksheet Functions | |||
Formula or Macro | Excel Discussion (Misc queries) | |||
formula or macro. | Excel Discussion (Misc queries) | |||
formula or macro | Excel Worksheet Functions |