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

[Guest]

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.

 

[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.

 

[Guest]

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 |:>)

 

[Guest]

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.

 

[Guest]

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