Vlookup Problem when file name changes

[zaska]

Hi,

=VLOOKUP(A2,MTO_07072011.DAT!$A:$C,3,0)

In the above formula File name "MTO_07072011.DAT " changes daily so how do i refer to dynamic file name in the above formula

The next day file name would change to "MTO_08072011.DAT"

Also i want to replace #N/A error with 0

<table border="0" cellpadding="0" cellspacing="0" width="563"><col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="width:48pt" span="5" width="64"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:56pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="74">SYMBOL</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">SERIES</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">OPEN</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">HIGH</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">LOW</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">CLOSE</td> <td style="width:71pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="95">TOTTRDQTY</td> <td style="width:56pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="74">Vlookup</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">20MICRONS</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">20110707</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">48</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">48.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">46.75</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">48.05</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">5426</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">3054</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">3IINFOTECH</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">20110707</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">46.45</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">46.8</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">45.75</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">46.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">594172</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">203687</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">3MINDIA</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">20110707</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4175</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4200</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4155</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4174.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">637</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">615</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">A2ZMES</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">20110707</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">260.9</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">263.75</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">256.6</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">258</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">178517</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">60345</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">AANJANEYA</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">20110707</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">371.75</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">372.6</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">362.05</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">364.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">270256</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">AARTIDRUGS</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">20110707</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">135</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">139</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">132.65</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">136.55</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">5950</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none" align="center">#N/A</td> </tr> </tbody></table>

Lookup table in another file name "MTO_07072011.DAT"

<table border="0" cellpadding="0" cellspacing="0" width="550"><col style="mso-width-source:userset;mso-width-alt:4681;width:96pt" width="128"> <col style="mso-width-source:userset;mso-width-alt:4461;width:92pt" width="122"> <col style="mso-width-source:userset;mso-width-alt:10971;width:225pt" width="300"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:96pt; font-size:11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#9BBB59;mso-pattern:#9BBB59 none" height="20" width="128">Name of Security</td> <td class="xl64" style="border-left:none;width:92pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#9BBB59;mso-pattern:#9BBB59 none" width="122">Quantity Traded</td> <td class="xl65" style="border-left:none;width:225pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#9BBB59;mso-pattern:#9BBB59 none" width="300">% of Deliverable Quantity to Traded Quantity</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none" height="20">20MICRONS</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none" align="right">3054</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">3IINFOTECH</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">203687</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none" height="20">3MINDIA</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none" align="right">615</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">A2ZMES</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">60345</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none" height="20">AANJANEYA</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none" align="right">120070</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">AARTIDRUGS</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">3359</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none" height="20">AARTIIND</td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none">EQ</td> <td class="xl71" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#EBF1DE;mso-pattern:#EBF1DE none" align="right">27325</td> </tr> </tbody></table>
Thank you

 

[Andrew Poulsom]

Is that really your formula? I would expect the file name to be in square brackets. The easiest way to solve your problem is to Edit|Links and change the Source. To avoid #N/A use the ISERROR function.

 

[zaska]

Sir,

Yes it is my formula. Sir i am using this formula in VBA code so it will be difficult for editing the links every day.

I open the file using


"E:\Macros\Input\MTO_" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmyyyy") & ".DAT"

I would like to know whether it is possible to refer the same way in the above formula

Thank you

 

[Andrew Poulsom]

You can use INDIRECT to build up the external reference, but the source workbook must be open. Why isn't there a worksheet reference in your formula?

 

[zaska]

Sir,

I don't know much about references. I simply tried Vlookup formula referring the source file with mouse.

Now the formula is =VLOOKUP(A2,'[MTO_07072011.DAT]'!$A:$C,3,0)

Can you kindly tell me how to use Index formula for referring the MTO file. Yes this file is already open with VBA Code.


Thank you for the response

 

[Andrew Poulsom]

There is still no sheet reference, but try:

=VLOOKUP(A2,INDIRECT("'[MTO_"&TEXT(G2,"ddmmyyyy")&".DAT]'!$A:$C"),3,0)

 

[zaska]

Sir,

thank you for the formula but i got # REF Error Both the formula file and source file are open.

 

[Andrew Poulsom]

Please post the exact formula you are using. What's in G2?

 

[zaska]

Sir,


In G2 there is Total traded Quantity . ( I didn't understand why you used G2 in your formula ) What i want to do is to Lookup Value in A2 from Lookuptable in "MTO_.....flile" and get the Delivery Quantity data into formula file.

Formula File

<table border="0" cellpadding="0" cellspacing="0" width="566"><colgroup><col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="width:48pt" span="5" width="64"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:56pt;font-size: 11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="74">SYMBOL</td> <td class="xl63" style="width:48pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">SERIES</td> <td class="xl63" style="width:48pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">OPEN</td> <td class="xl63" style="width:48pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">HIGH</td> <td class="xl63" style="width:48pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">LOW</td> <td class="xl63" style="width:48pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">CLOSE</td> <td class="xl63" style="width:71pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="95">TOTTRDQTY</td> <td class="xl63" style="width:58pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="77">Column1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">20MICRONS</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">20110707</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">48</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">48.2</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">46.75</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">48.05</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">5426</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">#REF!</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">3IINFOTECH</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">20110707</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">46.45</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">46.8</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">45.75</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">46.3</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">594172</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none">#REF!</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">3MINDIA</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">20110707</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">4175</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">4200</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">4155</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">4174.95</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">637</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">#REF!</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">A2ZMES</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">20110707</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">260.9</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">263.75</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">256.6</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">258</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">178517</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none">#REF!</td> </tr> </tbody></table>
Lookup Table

<table border="0" cellpadding="0" cellspacing="0" width="242"><colgroup><col style="mso-width-source:userset;mso-width-alt:4461;width:92pt" width="122"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:92pt; font-size:11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="122">NameofSecurity</td> <td class="xl64" style="border-left:none;width:46pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="61">QuantityTraded</td> <td class="xl65" style="border-left:none;width:44pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="59">%ofDeliverableQuantitytoTradedQuantity</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">20MICRONS</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">3054</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">3IINFOTECH</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">203687</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">3MINDIA</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">615</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">A2ZMES</td> <td class="xl67" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">EQ</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">60345</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">AANJANEYA</td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">EQ</td> <td class="xl71" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">120070</td> </tr> </tbody></table>
I used the formula " =VLOOKUP(A2,MTO_07072011.DAT!$A:$C,3,0) " which fetched the result. I didn't understand why there was no reference in square bracket.

Result sheet after using my own formula

<table border="0" cellpadding="0" cellspacing="0" width="591"><col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="width:48pt" span="5" width="64"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:77pt; font-size:11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="102">SYMBOL</td> <td class="xl66" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">SERIES</td> <td class="xl66" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">OPEN</td> <td class="xl66" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">HIGH</td> <td class="xl66" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">LOW</td> <td class="xl66" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">CLOSE</td> <td class="xl66" style="border-left:none;width:71pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="95">TOTTRDQTY</td> <td class="xl67" style="border-left:none;width:56pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="74">Delivery</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">20MICRONS</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">20110707</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">48</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">48.2</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">46.75</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">48.05</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">5426</td> <td class="xl71" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">3054</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">3IINFOTECH</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">20110707</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">46.45</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">46.8</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">45.75</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">46.3</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">594172</td> <td class="xl71" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">203687</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl72" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">3MINDIA</td> <td class="xl73" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">20110707</td> <td class="xl74" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4175</td> <td class="xl74" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4200</td> <td class="xl74" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4155</td> <td class="xl74" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4174.95</td> <td class="xl74" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">637</td> <td class="xl75" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">615</td> </tr> </tbody></table>
Thank you

 

[Andrew Poulsom]

Did you copy that formula from a cell and paste it into your reply? Is the source file an Excel workbook with a DAT extension? I have never seen a link formula without square barckets around the file name and a sheet reference. I would expect the formula to be like this:

=VLOOKUP(A2,[MTO_07072011.DAT]Sheet1!$A:$C,3,0)