0
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.
Is there a way to have a premade 'PivotTable1' generate a table for whatever information is pasted in tab 'January'?
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)
Is it possible to have all of the purchase data for an entire year in a single table? Think of your spreadsheet in terms of database normalization. – Jason Aller – 2015-05-19T15:50:45.210
I agree with @JasonAller: don't create a tab for each month. Instead add a column to 1 table which indicates the month. Now you can filter by month easily and create a single Pivot Table. If you really need all the tabs, you can 1) use a
– Byron Wall – 2015-05-19T16:30:02.997Table
as the Data Source so that it expands automatically with new data and 2) this really depends on the end goal. It's possible to use formulas to get a list of unique items (see #2) if you already have all the suppliers in a column from Pivot TabelsWould it be possible to have a pivot table constantly update as I add on Purchase Orders? – Andy Wong – 2015-05-19T16:45:39.870