• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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
0
karaflanagan
Asked:
karaflanagan
1 Solution
 
manthaneinCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now