Results 1 to 7 of 7

excel how to create pivot table from multiple worksheets

This is a discussion on excel how to create pivot table from multiple worksheets within the Excel Questions forums, part of the Question Forums category; I am using excel 2010. I am trying to create pivot table between two worksheets 'Summary' and 'Summary2'. I have ...

  1. #1
    New Member
    Join Date
    Jan 2011
    Posts
    3

    Default excel how to create pivot table from multiple worksheets

    I am using excel 2010. I am trying to create pivot table between two worksheets 'Summary' and 'Summary2'. I have identical row of data ranging from cells B5 to F5 in row 5 in both worksheets. Data in the two worksheets looks like this:
    Summary worksheet: Issues,20,3,4,5
    Summary2 worksheet: Issues,10,0,3,9
    Worksheet referes to issues from location 1 and worksheet referes to issues from location 2. Col B has title 'issues', Col C refers to issues of customer 1, Col D refers to issues of customer 2, Col E refers to issues of customer 3, Col F refers to issues of customer 4
    I go to a third worksheet and start pivot table and in the table range I give this: 'Summary:Summary2'!$B$5:$F$5. Then I Say OK. Gives error "data reference source is not valid". Can someone tell me how to select the row from two different worksheet in pivot table?
    Also I want to be able to add issues of customers between two locations and get % completion for each locaiton.
    Can someone please help?

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    665

    Smile Re: excel how to create pivot table from multiple worksheets

    press Alt +d + p and follow the needed steps.

    if you still need more assistance don't hesitate to ask

    hope this can help,
    Excel 2010

    " Who Ever Gives His Best Will Never Regret It "

    Tell me and I'll forget; show me and I may remember; involve me and I'll understand

  3. #3
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,562

    Default Re: excel how to create pivot table from multiple worksheets

    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    New Member
    Join Date
    Jan 2011
    Posts
    3

    Default Re: excel how to create pivot table from multiple worksheets

    Thnak you all. I got it working. Is there a way pivot table can compare each individual results with the grand totals. Example if I am tracking issues dealt with different groups, I want to find the % of work completed with each group with respect to totals. How can I do this?

  5. #5
    New Member
    Join Date
    Jan 2011
    Posts
    3

    Default Re: excel how to create pivot table from multiple worksheets

    Also is there a way I can have one pivot table that tracks planned work. Another pivot table that tracks actual completed work. Then can I compare the planned work with actual work, using the two pivot tables.

  6. #6
    Board Regular
    Join Date
    Aug 2006
    Posts
    57

    Default Re: excel how to create pivot table from multiple worksheets

    Quote Originally Posted by ziad alsayed View Post
    press Alt +d + p and follow the needed steps.

    if you still need more assistance don't hesitate to ask

    hope this can help,
    Hello,

    Could please give more information on how to pivot table from multiple worksheets?

    BR//Valley

  7. #7
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,972

    Default Re: excel how to create pivot table from multiple worksheets

    there is plenty of info at http://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

    note, the thread starting off giving bespoke VBA for a particular question but later in the thread is other & more general information on the subject

    regards
    "The bold don't live forever but the timid don't live at all" Marco Polo

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com