Monthly Pivot Tables In an Annual Report

Question

I was given 12 tables of purchase orders (one for each month) that my school has made and was asked to create an annual report listing purchases for each month which is pretty simple.

The thing is that they would like a spreadsheet that can input monthly data for the next fiscal year going forward (since I will no longer be working with them soon).

Each entry consists of Purchase Date, Supplier Name, Item Name, Total Cost. So for example, 1/1/2015, Dell Computers, Inspiron 11 3000, $449.99

What I've done is created 12 tabs and labeled them each with their own individual months where they can manually just paste the purchase orders and a Summary tab will display all the data from these 12 tabs.

1. Is there a way to have a premade 'PivotTable1' generate a table for whatever information is pasted in tab 'January'?

2. After having each month's data in a pivot table, in my Summary tab, the first column is all the Suppliers (Such as Dell, Acer, Starbucks, etc)

Lets say in February someone decided to purchase from Apple Inc for the first time, how would I reflect that in my Summary tab with a new Supplier?

My original strategy was the paste the preexisting Suppliers in the Summary table and use the Match function to appropriately sort the purchases into their appropriate rows. With this situation I would need another way to dynamically generate the Suppliers column.

(I'm not scared of using vba but I am a beginner at it)

Answer 1

The Pivot Table can be refreshed by right-clicking it and clicking 'Refresh' (or Options > Refresh). Note though that if the input data becomes any larger than the original data, it won't pick up the additional columns and rows. It'd be good practice to generate the pivot table off more rows than you need initially and instruct the next user to not exceed the paste area (shade it in some color).

If more rows and columns are needed, the next user can use Option > Change Data Source or insert more columns and rows within the data-dump area before pasting in the data.

For a dynamic supplier list, the best and easiest solution would be to take advantage of the dynamic rows in a Pivot Table by pasting all 12 months of data in a single sheet and creating the Pivot Table off that (as noted in the comments). If you're absolutely wedded to using 12 sheets, then you could either record (then edit for generalization) a macro that copies the suppliers from all 12 sheets and removes duplicates (Data > Remove Duplicates) or have a formula that reads every supplier into the Summary sheet + another formula that generates an identifier + another table with the identifiers that reads off the supplier names. But really, the single-sheet approach is best as other approaches would just be workarounds to implement a functionality that already exists just by making a minor change to how your data is stored.