How do I run a pivottable report w/ multiple worksheets?

M

May-Yen

How do I run a pivottable report w/ multiple worksheets as my data sources.
All worksheets are from same excel file.

I would be pleased if someone can advise.

Thank you.
 
D

Debra Dalgleish

If you create a Pivot Table from multiple consolidation ranges, you
won't get the same pivot table layout that you'd get from a single
range. There's an example here:

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

If possible, store your data in a single worksheet, or in a database,
and you'll have more flexibility in creating the pivot table.
 
C

CyberTaz

Hi May-Yen-

AFAIK, Pivot Table Reports must use only one source or list of records. They
cannot directly analyze data stored on multiple sheets or in multiple books.

What you will first need to do is:

a) Consolidate all data into a single list on the same sheet, or

b) You may be able to 'pull' the data together using a Query that retrieves
the data from it's respective sources.

You may want to take a look at the Data>Import External Data options and
review the Help files for more detailed info.

Good Luck |:>)
 
M

May-Yen

Hi CyberTaz,

Thank you very much. Really appreciate your help. I was not sure if the
pivot table could extract datas from mulitple worksheets. I tried to find
reading instruction and couldn't find it. I'm so glad that I stumbled onto
this forum. I didn't know about it until today!

Again, thank you. :)
 
M

May-Yen

Hi Debra,

Thank you for your reply to my question. CyberTaz also responded to my
question and both of your answers gave me a better understanding of how pivot
table works.

I will definitely go to the link you've provided.

Many thanks!!
May-Yen
 
Top