12learnexcel
New Member
- Joined
- Aug 3, 2009
- Messages
- 7
Hi experts,
I searched the whole forum for this type of query and found no solution (or I may not be using the correct keywords).
Kindly help me create a formula wherein whenever I add a sales entry from a certain client in sheet 2 (sales worksheet), the respective agent's name will automatically be reflected under the "agent" column. Source data is located in sheet 1 (Masterlist).
Thank you in advance.
<table x:str="" style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="445" width="380"><col style="width: 48pt;" width="64"> <col style="width: 70pt;" width="93"> <col style="width: 56pt;" width="75"> <col style="width: 272pt;" width="363"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" height="20" width="64">Masterlist (Sheet 1)</td> <td class="xl65" style="border-left: medium none; width: 70pt;" width="93">
</td> <td class="xl65" style="border-left: medium none; width: 56pt;" width="75">
</td> <td class="xl65" style="border-left: medium none; width: 272pt;" width="363">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Client No</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Client</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Agent</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Succeeding Columns contain Other Info about the Client</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Cathy</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Joel</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jack</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Mary</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jill</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Joe</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jack</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Peter</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jill</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jack</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Sales Worksheet (Sheet 2)</td> <td class="xl65" style="border-left: medium none;">
</td> <td class="xl65" style="border-left: medium none;">
</td> <td class="xl65" style="border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Date</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Client</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Agent</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Sales</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40023" align="center" height="20">29-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">98</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40023" align="center" height="20">29-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Mary</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">120</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40023" align="center" height="20">29-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Peter</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40024" align="center" height="20">30-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Mary</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">75</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40024" align="center" height="20">30-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Cathy</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">35</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40025" align="center" height="20">31-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Peter</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">80</td> </tr> </tbody></table>
I searched the whole forum for this type of query and found no solution (or I may not be using the correct keywords).
Kindly help me create a formula wherein whenever I add a sales entry from a certain client in sheet 2 (sales worksheet), the respective agent's name will automatically be reflected under the "agent" column. Source data is located in sheet 1 (Masterlist).
Thank you in advance.
<table x:str="" style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="445" width="380"><col style="width: 48pt;" width="64"> <col style="width: 70pt;" width="93"> <col style="width: 56pt;" width="75"> <col style="width: 272pt;" width="363"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" height="20" width="64">Masterlist (Sheet 1)</td> <td class="xl65" style="border-left: medium none; width: 70pt;" width="93">
</td> <td class="xl65" style="border-left: medium none; width: 56pt;" width="75">
</td> <td class="xl65" style="border-left: medium none; width: 272pt;" width="363">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Client No</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Client</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Agent</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Succeeding Columns contain Other Info about the Client</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Cathy</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Joel</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jack</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Mary</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jill</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Joe</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jack</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Peter</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jill</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jack</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Sales Worksheet (Sheet 2)</td> <td class="xl65" style="border-left: medium none;">
</td> <td class="xl65" style="border-left: medium none;">
</td> <td class="xl65" style="border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Date</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Client</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Agent</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Sales</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40023" align="center" height="20">29-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">98</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40023" align="center" height="20">29-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Mary</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">120</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40023" align="center" height="20">29-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Peter</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40024" align="center" height="20">30-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Mary</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">75</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40024" align="center" height="20">30-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Cathy</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">35</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40025" align="center" height="20">31-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Peter</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">80</td> </tr> </tbody></table>