Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I run a pivottable report w/ multiple worksheets?
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. |
#2
|
|||
|
|||
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 he 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. May-Yen wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
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 DataImport External Data options and review the Help files for more detailed info. Good Luck |:) "May-Yen" wrote: 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. |
#4
|
|||
|
|||
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. :-) "CyberTaz" wrote: 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 DataImport External Data options and review the Help files for more detailed info. Good Luck |:) "May-Yen" wrote: 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. |
#5
|
|||
|
|||
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 "Debra Dalgleish" wrote: 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 he 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. May-Yen wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a push report in Excel? | Excel Discussion (Misc queries) | |||
Linking worksheets after runnning report | Excel Discussion (Misc queries) | |||
Microsoft Access Report into Excel Spreadsheet | Excel Discussion (Misc queries) | |||
XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) | |||
How do I use multiple worksheets (List) in a Pivot Table Report? | Excel Worksheet Functions |