Create Summary data sheet from multiple sheets

HI there,

We are in an office and have weekly stationery orders we process for each of our departments within our plant. Our manager would like a summary sheet which shows for each department and product line, what the product ordered was and what the cost of that was.

Firstly, we have a stationery order sheet in which each department populates what they need to order, this then carries over to the "order master" of which the complete stationery list is ordered from...

stationery1.JPG

I added to this data by creating a "pricing master" sheet which shows the price for the product and multiples the item ordered which gives the price for that week...

stationery2.JPG

After this I have no idea how to, or what to do to create a weekly summary sheet which will show my manager what she wants.

Ideally it would show data such as

Department 1, Date 1, Date 2
Product, amount, price

Department 2, Date 1, Date 2
Product, amount, price

This is in order to compare what is being ordered each week for each department and see where we can make cost cutting decisions. e.g. why is one department purchasing 50 of a product each week, why don't they purchase 200 for the month which will allow us to save more on freight

Questions are:

1. Can someone help me? PLEASE!
2. Does anyone have a better way of doing this?

I have included the excel sheet which I have attached screen shots of. Weekly Ordering Spreadsheet V2 Costs.xlsx


I have no knowledge of VBA but can do basic macro recordings. If you can think of any easier way to produce this summary report to my manager, I would really appreciate the help.

Thank you in advance.

Thanks FDibbins,

I believe I could probably use the one workbook for quantity and price (as I have already set up in the costs spreadsheet attached to the original post - I would just need to unhide the quantities). How would I make up a summary sheet to allow the information to be wiped and recreated weekly but a history be kept easily? E.g. would a pivot table work, or is there some other magical excel thing I could use? Ideally it would be

Department Date Product Qty Price Total price for that weeks order..

Thanks in advance