Dynamically populating a pivot table with multiple worksheets in Excel

Question

I have a workbook that contains individual sheets for each month. Each sheet contains these columns:

Publication Title | # of Downloads | Month | Year

Each worksheet has 200+ entries, where Publication Title is unique on each sheet.

I am attempting to create a report that would get the data from each month's sheet, calculate the item's ranking for each month, and calculate a grand total of Downloads for each item. Something like this:

Publication Title | Jan Downloads | Jan Ranking | Feb Downloads | Feb Ranking | Total Downloads | Overall Ranking

I made some progress using a pivot table. The limitation I've run into, however, is that I can't figure out how to generate the pivot table with data from multiple sheets. At the moment I am manually pasting the data for each month (200+ items) into one consolidated sheet, adding the Month and Year columns, and creating the pivot table from that.

Is there a solution that would allow me to add a new sheet each month, and dynamically update the pivot table with a new column for that month?

Answer 1

The underlying problem here is wrong data architecture. You are spreading data input over several sheets and then need to consolidate to report on the data. That is the wrong way around.

Use only one sheet for data entry.

With all the data in one place, there are no problems to create pivot tables, or even a sheet for each publication that breaks the data down by publication title.