Excel Data Consolidation Question/Formula pulling data from multiple worksheets

Posted on 2011-04-27
Last Modified: 2012-05-11
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.
Question by:karaflanagan
    LVL 7

    Accepted Solution

    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.

    - Select any data in the pivot and  then refresh

    - 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..

    LVL 5

    Expert Comment


    Author Closing Comment

    Thank you for your help with this.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    VBA Excel Gantt 1 28 Excel Add-in TaskPane not opening 3 35
    Google  (Get  Users Email) 2 22
    Excel Formatting test 26 36
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now