formula is too long

stilgar

Board Regular
Joined
Feb 28, 2011
Messages
51
I need to somehow get these 4 formula's into one cell but they are too long

=IF(AND(B4="35mm x 125mm Skirting Duct",B9="BLACK"),"PL35125DB",IF(AND(B4="35mm x 125mm Skirting Duct",B9="WHITE"),"PL35125DW",IF(AND(B4="35mm x 125mm Skirting Duct",B9="OPAL GREY"),"PL35125DO",IF(AND(B4="35mm x 125mm Skirting Duct",B9="NATURAL ANODISED"),"PL35125DN",IF(AND(B4="35mm x 125mm Skirting Duct",B9="POWDERCOATED"),"PL35125DS","")))))

=IF(AND(B4="35mm x 150mm Skirting Duct",B9="BLACK"),"PL35150DB",IF(AND(B4="35mm x 150mm Skirting Duct",B9="WHITE"),"PL35150DW",IF(AND(B4="35mm x 150mm Skirting Duct",B9="OPAL GREY"),"PL35150DO",IF(AND(B4="35mm x 150mm Skirting Duct",B9="NATURAL ANODISED"),"PL35150DN",IF(AND(B4="35mm x 150mm Skirting Duct",B9="POWDERCOATED"),"PL35150DS","")))))

=IF(AND(B4="50mm x 150mm Skirting Duct",B9="BLACK"),"PL50150DB",IF(AND(B4="50mm x 150mm Skirting Duct",B9="WHITE"),"PL50150DW",IF(AND(B4="50mm x 150mm Skirting Duct",B9="OPAL GREY"),"PL50150DO",IF(AND(B4="50mm x 150mm Skirting Duct",B9="NATURAL ANODISED"),"PL50150DN",IF(AND(B4="50mm x 150mm Skirting Duct",B9="POWDERCOATED"),"PL50150DS","")))))

=IF(AND(B4="50mm x 200mm Skirting Duct",B9="BLACK"),"PL50200DB",IF(AND(B4="50mm x 200mm Skirting Duct",B9="WHITE"),"PL50200DW",IF(AND(B4="50mm x 200mm Skirting Duct",B9="OPAL GREY"),"PL50200DO",IF(AND(B4="50mm x 200mm Skirting Duct",B9="NATURAL ANODISED"),"PL50200DN",IF(AND(B4="50mm x 200mm Skirting Duct",B9="POWDERCOATED"),"PL50200DS","")))))

Idea's?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello, Try this;

=IF(AND(OR(LEFT(B9)={"B","N","O","P","W"}),ISNUMBER(SEARCH("*mm*x*mm",B4))),"PL"&LEFT(B4,2)&MID(B4,8,3)&"D"&LOOKUP(LEFT(B9),{"A","B","N","O","P","W"},{"","B","N","O","S","W"}),"")
 

stilgar

Board Regular
Joined
Feb 28, 2011
Messages
51
not really sure how to use that formula. do i just copy it or do i have to enter in something?

Those formulas are specific to the part numbers aswell. i'm not sure if your one takes that in to account?

Still learning :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,958
Office Version
  1. 365
Platform
  1. Windows
How about an even shorter

=IF(COUNTA(B4,B9)=2,"PL"&LEFT(SUBSTITUTE(B4,"mm x ",),5)&"D"&SUBSTITUTE(LEFT(B9,1),"P","S"),"") :)
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

just copy & paste. You can create a validation list in B4 for '50mm x 125mm Skirting Duct'.... & B9 for 'Black', 'White'......

Does this works?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,958
Office Version
  1. 365
Platform
  1. Windows
not really sure how to use that formula. do i just copy it or do i have to enter in something?

Those formulas are specific to the part numbers aswell. i'm not sure if your one takes that in to account?

Still learning :)

Are you saying that B4 could contain descriptions other than "Skirting Duct"?

How many descriptions / part numbers are you looking at? You will probably be better off with a lookup table.
 

stilgar

Board Regular
Joined
Feb 28, 2011
Messages
51
yeah proberly. i've got it working though just need to pretty it up

cheers guys
 

Watch MrExcel Video

Forum statistics

Threads
1,122,819
Messages
5,598,289
Members
414,223
Latest member
Accountant2B

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top