excel how to create pivot table from multiple worksheets

[viswa]

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?

 

[ziad alsayed]

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

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

hope this can help,

 

[VoG]

Also see http://www.contextures.com/xlPivot08.html

 

[viswa]

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?

 

[viswa]

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.

 

[Valley]

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

 

[Fazza]

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