HI
HELP!!!
I have four workbooks which contains data for agents conversion for example:
<table style="border-collapse: collapse; width: 238pt;" width="317" border="0" cellpadding="0" cellspacing="0"><col style="width: 190pt;" width="253"><col style="width: 48pt;" width="64"><tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 190pt;" width="253" height="17">Agent Name</td> <td class="xl65" style="width: 48pt;" width="64">%</td> </tr></tbody></table>
<table style="border-collapse: collapse; width: 238pt;" width="317" border="0" cellpadding="0" cellspacing="0"><col style="width: 190pt;" width="253"><col style="width: 48pt;" width="64"><tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 190pt;" width="253" height="17">agent a
</td> <td class="xl65" style="width: 48pt;" width="64">39.95
</td> </tr></tbody></table>
Each of the workbook contains one weeks worth of data.
I have a FINAL work but which consists of a months customer satisfaction surveys for example:
<table style="border-collapse: collapse; width: 254pt;" width="338" border="0" cellpadding="0" cellspacing="0"><col style="width: 92pt;" width="123"> <col style="width: 104pt;" width="138"> <col style="width: 58pt;" width="77"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 92pt;" width="123" height="17">Transaction Week</td> <td class="xl68" style="border-left: medium none; width: 104pt;" width="138">AgentName</td> <td class="xl75" style="border-left: medium none; width: 58pt;" width="77">CS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">26</td> <td class="xl70" style="border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">26</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">200</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">26</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">-100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b </td> <td class="xl73" style="border-left: medium none;">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agentc </td> <td class="xl73" style="border-left: medium none;">200</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b </td> <td class="xl73" style="border-left: medium none;">200</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">200</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">100</td> </tr> </tbody></table>
I have to analyse customer satisfaction against conversion which i have done by creating a pivot table. to gain the relevent information in the final workbook which i am going to pivot i used a vlookup to find the relevent conversion for each agent eliminating N/A. The formula i used was:
=IF(ISNA(VLOOKUP(C2,'26'!A:B,2,0)),"",VLOOKUP(C2,'26'!A:B,2,0))
Which left me with this in the final table.
<table style="border-collapse: collapse; width: 317pt;" width="423" border="0" cellpadding="0" cellspacing="0"><col style="width: 92pt;" width="123"> <col style="width: 104pt;" width="138"> <col style="width: 56pt;" width="75"> <col style="width: 65pt;" width="87"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 92pt;" width="123" height="17">Transaction Week</td> <td class="xl68" style="border-left: medium none; width: 104pt;" width="138">AgentName</td> <td class="xl75" style="border-left: medium none; width: 56pt;" width="75">cs</td> <td class="xl68" style="width: 65pt;" width="87">%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">26</td> <td class="xl70" style="border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">200</td> <td class="xl71" style="border-top: medium none;">60</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">26</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">100</td> <td class="xl71" style="border-top: medium none;">40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">26</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">-100</td> <td class="xl71" style="border-top: medium none;">20</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl70" style="border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">0</td> <td class="xl71" style="border-top: medium none;">60</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">200</td> <td class="xl71" style="border-top: medium none;">20</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">-200</td> <td class="xl71" style="border-top: medium none;">50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">100</td> <td class="xl71" style="border-top: medium none;">30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">0</td> <td class="xl71" style="border-top: medium none;">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">0</td> <td class="xl71" style="border-top: medium none;">50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">100</td> <td class="xl71" style="border-top: medium none;">80</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">200</td> <td class="xl71" style="border-top: medium none;">75</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">0</td> <td class="xl71" style="border-top: medium none;">16</td> </tr> </tbody></table>
This works great however because i have four work books based on each week of the month (26,27,28,29) I have to manually change the vlookup based on the transaction week in the final workbook.
As this is a monthly thing the number of customer satisfaction sample changes and agent data change i.e agents may have less agents as some may have left or vice versa. This would become time consuming to change the vlookup formulas so they pick up from the relevent workbook.
QUESTION!!!!
I if was to have a cell within each of the weekly workbook for which i could enter the transaction week into i.e 30,31,32,33
IS there a chance to create a formula which could do the following:
1)lookup the transaction week to identify which weekly workbook to use
2)look up agent name within that workbook to find conversion
3) to leave as blank if nothing is found instead of n/a (agent maybe sick for the week so no scores maybe available.
HELP!!!!!! lol
HELP!!!
I have four workbooks which contains data for agents conversion for example:
<table style="border-collapse: collapse; width: 238pt;" width="317" border="0" cellpadding="0" cellspacing="0"><col style="width: 190pt;" width="253"><col style="width: 48pt;" width="64"><tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 190pt;" width="253" height="17">Agent Name</td> <td class="xl65" style="width: 48pt;" width="64">%</td> </tr></tbody></table>
<table style="border-collapse: collapse; width: 238pt;" width="317" border="0" cellpadding="0" cellspacing="0"><col style="width: 190pt;" width="253"><col style="width: 48pt;" width="64"><tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 190pt;" width="253" height="17">agent a
</td> <td class="xl65" style="width: 48pt;" width="64">39.95
</td> </tr></tbody></table>
Each of the workbook contains one weeks worth of data.
I have a FINAL work but which consists of a months customer satisfaction surveys for example:
<table style="border-collapse: collapse; width: 254pt;" width="338" border="0" cellpadding="0" cellspacing="0"><col style="width: 92pt;" width="123"> <col style="width: 104pt;" width="138"> <col style="width: 58pt;" width="77"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 92pt;" width="123" height="17">Transaction Week</td> <td class="xl68" style="border-left: medium none; width: 104pt;" width="138">AgentName</td> <td class="xl75" style="border-left: medium none; width: 58pt;" width="77">CS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">26</td> <td class="xl70" style="border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">26</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">200</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">26</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">-100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b </td> <td class="xl73" style="border-left: medium none;">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agentc </td> <td class="xl73" style="border-left: medium none;">200</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b </td> <td class="xl73" style="border-left: medium none;">200</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">200</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">100</td> </tr> </tbody></table>
I have to analyse customer satisfaction against conversion which i have done by creating a pivot table. to gain the relevent information in the final workbook which i am going to pivot i used a vlookup to find the relevent conversion for each agent eliminating N/A. The formula i used was:
=IF(ISNA(VLOOKUP(C2,'26'!A:B,2,0)),"",VLOOKUP(C2,'26'!A:B,2,0))
Which left me with this in the final table.
<table style="border-collapse: collapse; width: 317pt;" width="423" border="0" cellpadding="0" cellspacing="0"><col style="width: 92pt;" width="123"> <col style="width: 104pt;" width="138"> <col style="width: 56pt;" width="75"> <col style="width: 65pt;" width="87"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 92pt;" width="123" height="17">Transaction Week</td> <td class="xl68" style="border-left: medium none; width: 104pt;" width="138">AgentName</td> <td class="xl75" style="border-left: medium none; width: 56pt;" width="75">cs</td> <td class="xl68" style="width: 65pt;" width="87">%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">26</td> <td class="xl70" style="border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">200</td> <td class="xl71" style="border-top: medium none;">60</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">26</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">100</td> <td class="xl71" style="border-top: medium none;">40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">26</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">-100</td> <td class="xl71" style="border-top: medium none;">20</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl70" style="border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">0</td> <td class="xl71" style="border-top: medium none;">60</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">200</td> <td class="xl71" style="border-top: medium none;">20</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">-200</td> <td class="xl71" style="border-top: medium none;">50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">100</td> <td class="xl71" style="border-top: medium none;">30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">0</td> <td class="xl71" style="border-top: medium none;">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">0</td> <td class="xl71" style="border-top: medium none;">50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent a</td> <td class="xl73" style="border-left: medium none;">100</td> <td class="xl71" style="border-top: medium none;">80</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent b</td> <td class="xl73" style="border-left: medium none;">200</td> <td class="xl71" style="border-top: medium none;">75</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">agent c</td> <td class="xl73" style="border-left: medium none;">0</td> <td class="xl71" style="border-top: medium none;">16</td> </tr> </tbody></table>
This works great however because i have four work books based on each week of the month (26,27,28,29) I have to manually change the vlookup based on the transaction week in the final workbook.
As this is a monthly thing the number of customer satisfaction sample changes and agent data change i.e agents may have less agents as some may have left or vice versa. This would become time consuming to change the vlookup formulas so they pick up from the relevent workbook.
QUESTION!!!!
I if was to have a cell within each of the weekly workbook for which i could enter the transaction week into i.e 30,31,32,33
IS there a chance to create a formula which could do the following:
1)lookup the transaction week to identify which weekly workbook to use
2)look up agent name within that workbook to find conversion
3) to leave as blank if nothing is found instead of n/a (agent maybe sick for the week so no scores maybe available.
HELP!!!!!! lol
