Data Refresh Issue

[Blueridge227]

Version: Excel 2007

I have a Table with the first three columns populated by an external data Connection(JobID,EmpID,JobDesc)
<table border="0" cellpadding="0" cellspacing="0" width="263"><colgroup><col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;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:1.0pt solid black; border-right:none;border-bottom:1.0pt solid black;border-left:none; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="64">JobID</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:1.0pt solid black;border-right:none; border-bottom:1.0pt solid black;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">EmpID</td> <td class="xl63" style="width:53pt;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:1.0pt solid black;border-right:none; border-bottom:1.0pt solid black;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="71">JobDesc</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:1.0pt solid black;border-right:none; border-bottom:1.0pt solid black;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">Hours</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" 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;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">100</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;background:#D8D8D8;mso-pattern:#D8D8D8 none">35</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;background:#D8D8D8;mso-pattern:#D8D8D8 none">Rework</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;background:#D8D8D8;mso-pattern:#D8D8D8 none">2.5hrs</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" 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" height="20">650</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">15</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">Ship Part</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">1.5hrs</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" 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:none;border-right:none;border-bottom: 1.0pt solid black;border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">200</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:none;border-right:none;border-bottom:1.0pt solid black; border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none">10</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:none;border-right:none;border-bottom:1.0pt solid black; border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none">Weld</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:none;border-right:none;border-bottom:1.0pt solid black; border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none">5hrs</td> </tr> </tbody></table>
I populate the hours for each job manually after.
The problem is when I refresh the data and more jobs have been added. The hours do not remain in the proper row. They seem to stay in the same row.
<table border="0" cellpadding="0" cellspacing="0" width="263"><colgroup><col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;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:1.0pt solid black; border-right:none;border-bottom:1.0pt solid black;border-left:none; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="64">JobID</td> <td class="xl65" 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:1.0pt solid black;border-right:none; border-bottom:1.0pt solid black;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">EmpID</td> <td class="xl65" style="width:53pt;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:1.0pt solid black;border-right:none; border-bottom:1.0pt solid black;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="71">JobDesc</td> <td class="xl65" 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:1.0pt solid black;border-right:none; border-bottom:1.0pt solid black;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">Hours</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;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">100</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;background:#D8D8D8;mso-pattern:#D8D8D8 none">35</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;background:#D8D8D8;mso-pattern:#D8D8D8 none">Rework</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;background:#D8D8D8;mso-pattern:#D8D8D8 none">2.5hrs</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" height="20">250</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">16</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">new part</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">1.5hrs</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;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">650</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;background:#D8D8D8;mso-pattern:#D8D8D8 none">15</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;background:#D8D8D8;mso-pattern:#D8D8D8 none">Ship Part</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;background:#D8D8D8;mso-pattern:#D8D8D8 none">
</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:none" height="20">200</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:none">10</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:none">Weld</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:none">5hrs</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:none;border-right:none;border-bottom: 1.0pt solid black;border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">650</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:none;border-right:none;border-bottom:1.0pt solid black; border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none">22</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:none;border-right:none;border-bottom:1.0pt solid black; border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none">rework</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:none;border-right:none;border-bottom:1.0pt solid black; border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none">
</td> </tr> </tbody></table>
Is there anyway to lock the hours with the other data so that on a refresh it moves with it?

 

[GlennUK]

Surely such data should be stored in the source area ( the external data area )?

 

[Blueridge227]

I ask myself that question every day. The data connection is pulling information from a read only database. With no Write access I am forced to do it this way.

 

[GlennUK]

It's more complicated than you think ... you are trying to assign data alongside a data area that is dynamic, so of course it won't stay aligned. Do you have a unique identifier field in the data? If so, type your hours into a separate table with unique id as one column and hours as the other, and then use formulas next to the query output to fetch the matching appropriate data from that table.