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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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"}),"")
 
Upvote 0
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 :)
 
Upvote 0
How about an even shorter

=IF(COUNTA(B4,B9)=2,"PL"&LEFT(SUBSTITUTE(B4,"mm x ",),5)&"D"&SUBSTITUTE(LEFT(B9,1),"P","S"),"") :)
 
Upvote 0
just copy & paste. You can create a validation list in B4 for '50mm x 125mm Skirting Duct'.... & B9 for 'Black', 'White'......

Does this works?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,515
Messages
6,131,110
Members
449,620
Latest member
MatXm

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
Back
Top