Pivot table retrieving data from several worksheets
 

Hi guys,



I have data for several countries in one workbook. Every country has its own
worksheet, and every worksheet has exactly the same structure.



I've prepared an additionnal "summary" worksheet, but I would need to
retrieve 3 different sort of data (quantity by task, amounts by task, person
ID No executing the task). The summary would be (ideally) organized in the
following matrix: in lines we have countries, in columns we have the days
(F. ex. 01/Dec/04 - 31/Dec/04).



For the time being, I created 3 different "summary" matrices, but I'm asking
myself if pivot tables would not be a better solution. However, I've never
done pivot tables from multiple worksheets. Is it possible?



Thanks for your help!

Wishing you a great end of the year,



Gizmo

Hi
it is possible. Using the last option on the first dialog for pivot tables
('Multiple consolidation ranges')

"Gizmo" wrote:

Hi guys,



I have data for several countries in one workbook. Every country has its own
worksheet, and every worksheet has exactly the same structure.



I've prepared an additionnal "summary" worksheet, but I would need to
retrieve 3 different sort of data (quantity by task, amounts by task, person
ID No executing the task). The summary would be (ideally) organized in the
following matrix: in lines we have countries, in columns we have the days
(F. ex. 01/Dec/04 - 31/Dec/04).



For the time being, I created 3 different "summary" matrices, but I'm asking
myself if pivot tables would not be a better solution. However, I've never
done pivot tables from multiple worksheets. Is it possible?



Thanks for your help!

Wishing you a great end of the year,



Gizmo

You can create a Pivot Table from multiple consolidation ranges, but 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 database, perhaps using MS Access, and
you'll have more flexibility in creating the pivot table.


Gizmo wrote:
Hi guys,



I have data for several countries in one workbook. Every country has its own
worksheet, and every worksheet has exactly the same structure.



I've prepared an additionnal "summary" worksheet, but I would need to
retrieve 3 different sort of data (quantity by task, amounts by task, person
ID No executing the task). The summary would be (ideally) organized in the
following matrix: in lines we have countries, in columns we have the days
(F. ex. 01/Dec/04 - 31/Dec/04).



For the time being, I created 3 different "summary" matrices, but I'm asking
myself if pivot tables would not be a better solution. However, I've never
done pivot tables from multiple worksheets. Is it possible?



Thanks for your help!

Wishing you a great end of the year,



Gizmo




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html