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?