Put table data into a form and save form as separate file

[MattsDad]

I need a way to take commission data that we maintain in a table in Excel 2003 and populate a target worksheet, (a grid for budget vs. actual), that will then be saved outside of the workbook so that it can be emailed to employees to substantiate their commission payments.

I need it to:

• Start with the first entry on the commission table and cycle through all names until the last record is completed
• Update data fields on target worksheet for all records labeled as "yes" in the export column
  • Maybe use "offset" function to populate fields on the target workbook based on current record in the list???
• Save the target workbook as a separate .xls file in the same directory as the commission table
• Break links to source data so the new file will be a stand-alone file that can be emailed to employees
• Repeat until entire list has been completed

This seems to me to be a simple concept but I can't figure out how to convert it to an actual process. Any help you can give will be greatly appreciated.

Thanks for taking the time to read this far. Here's some sample data that might make my project a bit more clear (I hope).


<table width="915" border="0" cellpadding="0" cellspacing="0"><col style="width: 34pt;" width="45"> <col style="width: 53pt;" width="71"> <col style="width: 53pt;" width="70"> <col style="width: 51pt;" width="68"> <col style="width: 56pt;" width="75"> <col style="width: 59pt;" width="78"> <col style="width: 68pt;" width="90" span="2"> <col style="width: 64pt;" width="85"> <col style="width: 63pt;" width="84"> <col style="width: 67pt;" width="89"> <col style="width: 53pt;" width="70"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 34pt;" width="45" height="17"> <table width="695" border="0" cellpadding="0" cellspacing="0"><col style="width: 34pt;" width="45"> <col style="width: 53pt;" width="71"> <col style="width: 53pt;" width="70"> <col style="width: 26pt;" width="34"> <col style="width: 38pt;" width="51"> <col style="width: 46pt;" width="61" span="2"> <col style="width: 54pt;" width="72"> <col style="width: 48pt;" width="64"> <col style="width: 67pt;" width="89"> <col style="width: 58pt;" width="77"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 34pt;" width="45" height="17"> <table width="763" border="0" cellpadding="0" cellspacing="0"><col style="width: 44pt;" width="59"> <col style="width: 53pt;" width="71"> <col style="width: 62pt;" width="82"> <col style="width: 32pt;" width="42"> <col style="width: 53pt;" width="71"> <col style="width: 52pt;" width="69"> <col style="width: 50pt;" width="67"> <col style="width: 54pt;" width="72"> <col style="width: 48pt;" width="64"> <col style="width: 67pt;" width="89"> <col style="width: 58pt;" width="77"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 44pt;" width="59" height="17">Export</td> <td style="width: 53pt;" width="71">First</td> <td style="width: 62pt;" width="82">Last</td> <td style="width: 32pt;" width="42">Rate</td> <td class="xl25" style="width: 53pt;" width="71"> Target </td> <td class="xl25" style="width: 52pt;" width="69"> Actual </td> <td class="xl25" style="width: 50pt;" width="67"> Budget </td> <td class="xl25" style="width: 54pt;" width="72"> Diff </td> <td class="xl24" style="width: 48pt;" width="64">% to Plan</td> <td class="xl24" style="width: 67pt;" width="89">EarnRate</td> <td class="xl25" style="width: 58pt;" width="77"> Earn$ </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Yes</td> <td>John</td> <td>Public</td> <td class="xl24" align="right">10%</td> <td class="xl25"> 1,000 </td> <td class="xl25"> 500,000 </td> <td class="xl25"> 490,000 </td> <td class="xl25"> 10,000 </td> <td class="xl24" align="right">102%</td> <td class="xl24" align="right">100%</td> <td class="xl25"> 1,000 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Yes</td> <td>Jane</td> <td>Eyre</td> <td class="xl24" align="right">23%</td> <td class="xl25"> 2,300 </td> <td class="xl25"> 30,000 </td> <td class="xl25"> 33,000 </td> <td class="xl25"> (3,000)</td> <td class="xl24" align="right">91%</td> <td class="xl24" align="right">80%</td> <td class="xl25"> 1,840 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Yes</td> <td>Jane</td> <td>Eyre</td> <td class="xl24" align="right">12%</td> <td class="xl25"> 1,200 </td> <td class="xl25"> 50,000 </td> <td class="xl25"> 49,000 </td> <td class="xl25"> 1,000 </td> <td class="xl24" align="right">102%</td> <td class="xl24" align="right">100%</td> <td class="xl25"> 1,200 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">No</td> <td>Dora</td> <td>Theexplora</td> <td class="xl24" align="right">20%</td> <td class="xl25"> 2,000 </td> <td class="xl25"> 75,000 </td> <td class="xl25"> 70,000 </td> <td class="xl25"> 5,000 </td> <td class="xl24" align="right">107%</td> <td class="xl24" align="right">120%</td> <td class="xl25"> 2,400 </td> </tr> </tbody></table></td> <td style="width: 53pt;" width="71">
</td> <td style="width: 53pt;" width="70">
</td> <td style="width: 26pt;" width="34">
</td> <td class="xl25" style="width: 38pt;" width="51">
</td> <td class="xl25" style="width: 46pt;" width="61">
</td> <td class="xl25" style="width: 46pt;" width="61">
</td> <td class="xl25" style="width: 54pt;" width="72">
</td> <td class="xl24" style="width: 48pt;" width="64">
</td> <td class="xl24" style="width: 67pt;" width="89">
</td> <td class="xl25" style="width: 58pt;" width="77">
</td> </tr> </tbody></table></td> <td style="width: 53pt;" width="71">
</td> <td style="width: 53pt;" width="70">
</td> <td style="width: 51pt;" width="68">
</td> <td style="width: 56pt;" width="75">
</td> <td class="xl24" style="width: 59pt;" width="78">
</td> <td class="xl24" style="width: 68pt;" width="90">
</td> <td class="xl24" style="width: 68pt;" width="90">
</td> <td class="xl24" style="width: 64pt;" width="85">
</td> <td class="xl25" style="width: 63pt;" width="84">
</td> <td class="xl25" style="width: 67pt;" width="89">
</td> <td class="xl24" style="width: 53pt;" width="70">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl25" align="right">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl25" align="right">
</td> <td class="xl25" align="right">
</td> <td class="xl24">
</td> </tr> </tbody></table>Thanks,
Joe