Pivot table multiple worksheet data source errors

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?

 

Question Info


Last updated February 21, 2018 Views 5,007 Applies to:
Answer
Answer

The only way for that to do is POWER PIVOT. use can use multiple sheets to create an pivot in this new feature.

This is new feature in excel 2010. But as you have less data, i request you to copy both th data to new sheet and then apply pivot to it.

You cannot use both sheets to perform or create pivot table.


Keep Eye on your TIME, Not on your WATCH.
If this response answers your question then please MARK as ANSWER

****Keep Eye on your time not on your watch****

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.