How to aggregate data from all sheets (from the same file) into a "recap sheet"

Dear community,

I am struggling for days with the following problem:

- I have several sheets in an Excel file
- All sheets have the exact same table format (same column names and same number of column)

I would like to create a "Recap sheet" in which there is the same table format as existing sheets that would aggregate data when I filter it. To be clearer. I would like to be able to select a column filter and then, the data automatically aggregates (retrieving all the lines from all sheets that meet the selected filter).

I added a sample file with three sheets, each sheet contains the same table format with four columns: Edition / Name / Quantity / Type

I would like to create another sheet with a table with the same number of column (of if I can select which one it is even better). I tried Pivot Table reports, I tried external data queries ... without success :-(

Many thanks in advance for your support and I hope I have been clear enough.

PS: the actual file with the data contains table with 10 columns and between 200 and 500 lines each. The table will evolve with time with new sheets added to it.

Mathieu

Since you are using Excel 2016, you have Power Query available. In your version, it is called Get and Transform Data and is found on the Data Tab.

In a new workbook, click on Get and Transform-->Get Data-->From File-->From Workbook. Select the file with the sheets you wish to append (join)

A new window will open and list all the tables. Select the box to select multiple tables. Now check the tables you wish to join and then click on Transform at the bottom.

The Power Query Editor will open and in the left will list all the tables you have selected.

On the Home Tab, select Append Queries and select Append Queries as new.

A new window will open, select 3 or more tables

Highlight the table to be appended.

On the Home Tab, select Close and Load, select Close and Load to and follow the instructions in the window to direct where you wish the output.

Your tables have been joined and you may filter as needed. If you are only going to filter one column all the time, post back and I will show you how to build a parameter into your query.

The file is attached so you can review the steps I have taken.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.