excel how to create pivot table from multiple worksheets

viswa

New Member
Joined
Jan 8, 2011
Messages
3
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
press Alt +d + p and follow the needed steps.

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

hope this can help,
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top