sorry guys, I had mistyped a cell number before dragging the formula down through the other cells, this is working now. I can't use excel Jeani to paste an example in but I can just copy it, Attached is a copy of the worksheet so far.
Is there a way to extend on the formula as you can see in the worksheet sometimes the F20 code needs to be found but on other rows its an F07, F05 or F04 code i'm looking for.
How can i extend to formula to decide which one needs to be shown?
<TABLE style="WIDTH: 500pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=667 border=0 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 416pt; mso-width-source: userset; mso-width-alt: 20297" width=555><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2137244 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" width=112 height=17>Auto Generated</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=555>Option Codes</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B2)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C4500,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: #ffff99" height=34 x:str="" x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B3)))'> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,NH2,QC4,QE2,R38,TR5</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: #ffff99" height=34 x:str="" x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B4)))'> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,QC4,QE2,R38,TR5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B5)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C4500,C63,C93,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2,QC4,QE2,R38,TR5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:str="" x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B6)))'> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C3300,C93,EM8,F07,GS3,GT1,KU4,L30,MW5,N52</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: #ffff99" height=34 x:str="" x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B7)))'> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,QC4,R38,TR5</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: #ffff99" height=34 x:str="" x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B8)))'> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,QC4,R38,TR5</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: #ffff99" height=34 x:str="" x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B9)))'> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,QC4,R38,TR5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B10)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C4500,C63,C93,EM8,ES5,F20,FZ9,GS3,GT1,KU4,L30,M67,MW5,S28</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B11)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C4500,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B12)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C4500,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B13)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C4500,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B14)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C4500,C63,C93,EM8,ES5,F20,GT1,KU4,MW5,NH2,QC4,QE2,R38,TR5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B15)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C4500,C63,C93,EM8,ES5,F20,GT1,KU4,MW5,NH2,QC4,QE2,R38,TR5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B16)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C4500,EM8,ES5,F20,GT1,KU4,MW5,NH2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17 x:fmla='=REPT("F20",ISNUMBER(FIND("F20",B17)))'>F20</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 416pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=555>2633,AP1,C63,C93,EM8,F20,GD3,GS2,KH3,KU4,M45,MW5,MZ9,N56</TD></TR></TBODY></TABLE>