G
gr8guy
Hi,
I have a workbook with 2 sheets. sheet1 contains a 12 month tabular data (like a calendar) of Agents Promises taken/Kept productions in a callcenter:
it is something like this:
January
-------------------------------------------------------------------
Name | Taken | Kept | Kept% | Wrap | Quality|
-------------------------------------------------------------------
Don | | | | | |
-------------------------------------------------------------------
Liza | | | | | |
-------------------------------------------------------------------
Emily | | | | | |
-------------------------------------------------------------------
Sana | | | | | |
-------------------------------------------------------------------
February (.....this is on right side of January)
-------------------------------------------------------------------
Name | Taken | Kept | Kept% | Wrap | Quality|
-------------------------------------------------------------------
Don | | | | | |
-------------------------------------------------------------------
Liza | | | | | |
-------------------------------------------------------------------
Emily | | | | | |
-------------------------------------------------------------------
Sana | | | | | |
-------------------------------------------------------------------
On Sheet 2, there is a Filtered & Sorted Database (given Dynamic Name - "Database" using offset function) like this:
P.S: Kept% = Kept/Taken*100
Month | Name | Taken | Kept | Kept% | Wrap | Quality|
------------------------------------------------------------------------------
January | Don | 500 | 415 | 86 | 12% | 100 %|
January | Sana | 475 | 300 | 75% | 16 % | 98 % |
February| Sana |
.................. and so on........
My problem is to get the Following data from Sheet2 into Sheet1 in the individual cells as per the Month & Agent name & the header names viz. Taken/Kept/Kept%/Wrap/Quality. I want a formula or VBA code which will get this data based on the above which i stated. I tried Double Lookup using Offset & match from Chip Pearson's website but it works for 2 criteria not for 3.
Also, If for some reason in a particular month, if some agent was missing/left or changed to some other team or some new agent joined my team, the database will show the scores of the agent for that month, but how would i get the his name into sheet1 in the table as per the month.
If somebody requires me to mail a copy of the workbook, pls let me know.
I just need a formula for each individual cell in sheet1.
Any better ideas are most welcome!
Rgds,
Eijaz
I have a workbook with 2 sheets. sheet1 contains a 12 month tabular data (like a calendar) of Agents Promises taken/Kept productions in a callcenter:
it is something like this:
January
-------------------------------------------------------------------
Name | Taken | Kept | Kept% | Wrap | Quality|
-------------------------------------------------------------------
Don | | | | | |
-------------------------------------------------------------------
Liza | | | | | |
-------------------------------------------------------------------
Emily | | | | | |
-------------------------------------------------------------------
Sana | | | | | |
-------------------------------------------------------------------
February (.....this is on right side of January)
-------------------------------------------------------------------
Name | Taken | Kept | Kept% | Wrap | Quality|
-------------------------------------------------------------------
Don | | | | | |
-------------------------------------------------------------------
Liza | | | | | |
-------------------------------------------------------------------
Emily | | | | | |
-------------------------------------------------------------------
Sana | | | | | |
-------------------------------------------------------------------
On Sheet 2, there is a Filtered & Sorted Database (given Dynamic Name - "Database" using offset function) like this:
P.S: Kept% = Kept/Taken*100
Month | Name | Taken | Kept | Kept% | Wrap | Quality|
------------------------------------------------------------------------------
January | Don | 500 | 415 | 86 | 12% | 100 %|
January | Sana | 475 | 300 | 75% | 16 % | 98 % |
February| Sana |
.................. and so on........
My problem is to get the Following data from Sheet2 into Sheet1 in the individual cells as per the Month & Agent name & the header names viz. Taken/Kept/Kept%/Wrap/Quality. I want a formula or VBA code which will get this data based on the above which i stated. I tried Double Lookup using Offset & match from Chip Pearson's website but it works for 2 criteria not for 3.
Also, If for some reason in a particular month, if some agent was missing/left or changed to some other team or some new agent joined my team, the database will show the scores of the agent for that month, but how would i get the his name into sheet1 in the table as per the month.
If somebody requires me to mail a copy of the workbook, pls let me know.
I just need a formula for each individual cell in sheet1.
Any better ideas are most welcome!
Rgds,
Eijaz