Is it possible to create a Pivot Table from multiple sheets?
CARPEDIEM84530
Posted messages
3
Registration date
Status
Member
Last intervention
-
CARPEDIEM84530 Posted messages 3 Registration date Status Member Last intervention -
CARPEDIEM84530 Posted messages 3 Registration date Status Member Last intervention -
Hello,
I created a document a few years ago for my daughter's accounting. (Excel version 16.16.8, Mac under High Sierra).
The expense sheet spans from row 1 to 481, divided into 12 elements (row 1 to 36 (January), row 37 to 72 (February), row 73 to 108 (March), etc., columns A to G). Each supplier is assigned a reference number 1, 2, 3, etc.... (See attached file)
The Pivot Table created from this sheet works perfectly with the formula:
‘CHARGES 01’!$A$2:$G$481 (see file)
As the number of monthly purchases has become too large, I created 12 identical expense sheets (one for each month).
Unfortunately, I am unable to modify the formula so that the Pivot Table aggregates the amounts from all the sheets.
I have tried different approaches and nothing works, which is why I am seeking advice from the forum members.
With my thanks
ANASTASIO
I created a document a few years ago for my daughter's accounting. (Excel version 16.16.8, Mac under High Sierra).
The expense sheet spans from row 1 to 481, divided into 12 elements (row 1 to 36 (January), row 37 to 72 (February), row 73 to 108 (March), etc., columns A to G). Each supplier is assigned a reference number 1, 2, 3, etc.... (See attached file)
The Pivot Table created from this sheet works perfectly with the formula:
‘CHARGES 01’!$A$2:$G$481 (see file)
As the number of monthly purchases has become too large, I created 12 identical expense sheets (one for each month).
Unfortunately, I am unable to modify the formula so that the Pivot Table aggregates the amounts from all the sheets.
I have tried different approaches and nothing works, which is why I am seeking advice from the forum members.
With my thanks
ANASTASIO
4 answers
-
Good evening.
Yes, it is possible to take multiple sheets into account, but not simply by modifying the formula!
When you open the Create PivotTable tool at the bottom of the window, there is the option "Add this data to the data model". So you need to learn how to manipulate data models.
When you're in the PivotTable, the right pane displays the fields of the current table, and by clicking on MORE TABLES a window repeats that you need to use a data model.
--
Retirement is great! Especially in the Caribbean...
Raymond (INSA, AFPA) -
Hello,
Thank you but nothing matches or I am being obtuse. -
https://support.microsoft.com/fr-fr/office/cr%c3%a9er-un-mod%c3%a8le-de-donn%c3%a9es-dans-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b?ui=fr-fr&rs=fr-fr&ad=fr
--
Retirement is nice! Especially in the Caribbean...
Raymond (INSA, AFPA) -
HELLO,
As specified in my profile, I am not on Windows but on Mac.
The proposals are not the same.
Thank you
