Hi all.
Given a required price (cell F8 currently showing 4025 as below), I need to find within the table at the bottom (Range is E11:E21) the value that is closest to the required price (one that is larger, one that is smaller). This gives boundary values that are then represented as M and N in cells F6 and F7 (Currently showing 4055 and 3975 WHICH I'VE PUT IN MANUALLY BUT WANT TO MAKE AUTOMATED)
<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=290 border=0 x:str><COLGROUP><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6619" width=181><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 136pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" width=181 height=22>M</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffffcc" width=109 x:num="4055.25">$4,055</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" height=22>N </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc" x:num="3975.25">$3,975</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" height=22>Required Price</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99" x:num="4065">$4,025</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 136pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=181 border=0 x:str><COLGROUP><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6619" width=181><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 136pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" width=181 height=18 x:num="4295.25">$4,295</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="4215.25">$4,215</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="4135.25">$4,135</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="4055.25">$4,055</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3975.25">$3,975</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #99ccff" height=18 x:num="3895.25" x:fmla='=BDP("LMCADP 20090520 LME Comdty","px_eval")'>$3,895</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3815.25">$3,815</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3735.25">$3,735</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3655.25">$3,655</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3575.25">$3,575</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3495.25">$3,495</TD></TR></TBODY></TABLE>
Please help with the code for this in each of the 2 cells, I'm a newbie
Given a required price (cell F8 currently showing 4025 as below), I need to find within the table at the bottom (Range is E11:E21) the value that is closest to the required price (one that is larger, one that is smaller). This gives boundary values that are then represented as M and N in cells F6 and F7 (Currently showing 4055 and 3975 WHICH I'VE PUT IN MANUALLY BUT WANT TO MAKE AUTOMATED)
<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=290 border=0 x:str><COLGROUP><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6619" width=181><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 136pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" width=181 height=22>M</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffffcc" width=109 x:num="4055.25">$4,055</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" height=22>N </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc" x:num="3975.25">$3,975</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" height=22>Required Price</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99" x:num="4065">$4,025</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 136pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=181 border=0 x:str><COLGROUP><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6619" width=181><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 136pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" width=181 height=18 x:num="4295.25">$4,295</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="4215.25">$4,215</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="4135.25">$4,135</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="4055.25">$4,055</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3975.25">$3,975</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #99ccff" height=18 x:num="3895.25" x:fmla='=BDP("LMCADP 20090520 LME Comdty","px_eval")'>$3,895</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3815.25">$3,815</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3735.25">$3,735</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3655.25">$3,655</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3575.25">$3,575</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3495.25">$3,495</TD></TR></TBODY></TABLE>
Please help with the code for this in each of the 2 cells, I'm a newbie