Pulling Data From One Sheet To Another Automatically Using Values

[tjbailey10]

<font size="2">Worksheet: YTD Summary UsedRange: $A$1:$AE$38 Range: $O$4:$U$11</font><br /><br /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2"> </font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">O</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">P</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Q</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">R</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">S</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">T</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">U</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">4</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO4_705"></a><font size="2">Apr</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP4_705"></a><font size="2">May</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ4_705"></a><font size="2">Jun</font></td><td width="100px" style="background-color:#ffffff;"><a name="cR4_705"></a><font size="2">Jul</font></td><td width="100px" style="background-color:#ffffff;"><a name="cS4_705"></a><font size="2">Aug</font></td><td width="100px" style="background-color:#ffffff;"><a name="cT4_705"></a><font size="2">Sep</font></td><td width="100px" style="background-color:#ffffff;"><a name="cU4_705"></a><font size="2">Oct</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">5</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO5_705"></a><font size="2"><a title="=Delivery!D5" href="#fO5_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP5_705"></a><font size="2"><a title="=Delivery!H5" href="#fP5_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ5_705"></a><font size="2"><a title="=Delivery!L5" href="#fQ5_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cU5_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">6</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO6_705"></a><font size="2"><a title="=Delivery!D9" href="#fO6_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP6_705"></a><font size="2"><a title="=Delivery!H9" href="#fP6_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ6_705"></a><font size="2"><a title="=Delivery!L9" href="#fQ6_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cU6_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">7</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO7_705"></a><font size="2"><a title="=Delivery!D13" href="#fO7_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP7_705"></a><font size="2"><a title="=Delivery!H13" href="#fP7_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ7_705"></a><font size="2"><a title="=Delivery!L13" href="#fQ7_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cU7_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">8</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO8_705"></a><font size="2"><a title="=Delivery!D141" href="#fO8_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP8_705"></a><font size="2"><a title="=Delivery!H141" href="#fP8_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ8_705"></a><font size="2"><a title="=Delivery!L141" href="#fQ8_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT8_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cU8_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">9</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO9_705"></a><font size="2"><a title="=Delivery!D70" href="#fO9_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP9_705"></a><font size="2"><a title="=Delivery!H70" href="#fP9_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ9_705"></a><font size="2"><a title="=Delivery!L70" href="#fQ9_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT9_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cU9_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">10</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO10_705"></a><font size="2"><a title="=Delivery!D74" href="#fO10_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP10_705"></a><font size="2"><a title="=Delivery!H74" href="#fP10_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ10_705"></a><font size="2"><a title="=Delivery!L74" href="#fQ10_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT10_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cU10_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">11</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO11_705"></a><font size="2"><a title="=Delivery!D21" href="#fO11_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP11_705"></a><font size="2"><a title="=Delivery!H21" href="#fP11_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ11_705"></a><font size="2"><a title="=Delivery!L21" href="#fQ11_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT11_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cU11_705"></a><font size="2"></font></td></table></div><p /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Address</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Value</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Formula</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fO5_705"></a><font size="2"><a href="#cO5_705">O5</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!D5</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fP5_705"></a><font size="2"><a href="#cP5_705">P5</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!H5</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ5_705"></a><font size="2"><a href="#cQ5_705">Q5</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!L5</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fO6_705"></a><font size="2"><a href="#cO6_705">O6</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!D9</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fP6_705"></a><font size="2"><a href="#cP6_705">P6</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!H9</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ6_705"></a><font size="2"><a href="#cQ6_705">Q6</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!L9</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fO7_705"></a><font size="2"><a href="#cO7_705">O7</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!D13</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fP7_705"></a><font size="2"><a href="#cP7_705">P7</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!H13</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ7_705"></a><font size="2"><a href="#cQ7_705">Q7</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!L13</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fO8_705"></a><font size="2"><a href="#cO8_705">O8</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!D141</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fP8_705"></a><font size="2"><a href="#cP8_705">P8</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!H141</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ8_705"></a><font size="2"><a href="#cQ8_705">Q8</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!L141</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fO9_705"></a><font size="2"><a href="#cO9_705">O9</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!D70</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fP9_705"></a><font size="2"><a href="#cP9_705">P9</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!H70</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ9_705"></a><font size="2"><a href="#cQ9_705">Q9</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!L70</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fO10_705"></a><font size="2"><a href="#cO10_705">O10</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!D74</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fP10_705"></a><font size="2"><a href="#cP10_705">P10</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!H74</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ10_705"></a><font size="2"><a href="#cQ10_705">Q10</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!L74</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fO11_705"></a><font size="2"><a href="#cO11_705">O11</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!D21</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fP11_705"></a><font size="2"><a href="#cP11_705">P11</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!H21</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ11_705"></a><font size="2"><a href="#cQ11_705">Q11</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=Delivery!L21</font></td></tr></table></div>

In the above example you can see that the data references the "Delivery" tab. I'm looking to get Excel to automatically pull the data from the Delivery tab without having to use a reference (IE it would input the value 100% instead of =Delivery!L9) to complicate the matter the worksheet does not list the data in sequential order.
For Example: The data for Vendor 1 would be pulled from H5, H9, H13. The data for Vendor 2 would be pulled from L5, L9, L13, and so forth.
To even further complicate the matter another set of data would need to be pulled in a similar fashion except from a completely different workbook. (Again data not in a sequential order but in a pattern).

Is there a script that could automate this process?

 

[tjbailey10]

I forgot to mention I'm running Excel 2007 on a Windows 7 machine.

 

[tjbailey10]

bump