Excel Data Consolidation Question/Formula pulling data from multiple worksheets

Ok, so what I would like to do, I have a report that I pull daily that gives me the total number of phone calls made in a day and total talk time.  I have another spreadsheet that I link it to.  How can I get the data for each salesperson into the correct spot on the other spreadsheet?  I want to take data from the attached April 2011 spreadsheet and make it so that it automatically fills in the total calls and total talk time on the AE Metrics spreadsheet for the correct employee.  If I need to set things up differently, please let me know.  Also, I don't want to have to do each formual individually.  If there is an easier and faster way to pull this data, please let me know what you think.  I have attached both spreadsheets.
AE-Metrics-2011.xlsx
Updated-April-2011.xlsx
karaflanaganAsked:
Who is Participating?
 
manthaneinConnect With a Mentor Commented:
Please  see my suggested  format  below
this worksheet  utilizes tables and  pivottables to  get your  report..

first change is  I made it  into just  one workbook with  3 sheets..

Source data trial  
-  this will hold the daily extraction file..  
-  To add data  
-- copy the the data from extracted,  from the field Device to Avg TalkTime
-- Append data  to this work sheet on Column B
-- add Date value  to Column A
-- the formula on Column I and H will automatically be added.

Pivot
- Select any data in the pivot and  then refresh


AE-Metrics
- instead  of having  one sheet  per person.. you should just utilize the filter to  filter perperson..
- to add record
-- Type In new AEName, WEEK and Date Value   on the last  blank cell
-- the formula for ID, # of calls and Phonetime will automaticall appear..




NewFormat.xlsx
0
 
karaflanaganAuthor Commented:
Thank you for your help with this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.