Weekly Sales to Roll Up into Full Year Summary Report

[kcallahan1]

Hello All,

I am looking to create a macro which will allow me to save a product order into a folder and then the separate summary workbook will automatically pull in that orders information.

I am fairly inexperienced with using Macros but here is my thought process of what I would like to be able to do. I can create the master summary page which will include every single order. I would need the date pulled into the next column to the right every time a new workbook is saved. I would then need a vlookup by each UPC. Not every order contains every item and the items not being ordered are simply excluded and that row is deleted so simply returning the column will not work.

I am looking for a way to receive the new order, save it in a certain place, and then go to my master workbook and hit refresh or run macro. This would be much better than creating new v lookups every time I get an order. The purpose of this will be to create charts, find trends and calculate average order sizes for each product over specific time periods to help with forecasting.

Any help would be greatly appreciated!

 

[baitmaster]

welcome to the board

Correct me if I'm wrong but what you appear to be trying to do is create an orders database, but using a windows folder structure with a series of disconnected Excel workbooks as your database, with a Summary workbook as the data analyser. This would be a very bad idea, and one that will come back and haunt you later - for example if your formulas become corrupted and you have to manually fix every single last one of them...

If you want to pass the data into a database then do so, but use a real database programme like Access. You can pass data between Excel and Access very easily if you know how, and example files are available e.g. at Case studies & example files - Spreadsheet Wizard Ltd (last link), including much of the code you'd need. Don't try to reinvent the wheel on this one, spend a little money and do it right, rather than try to botch it and spend a lot fixing it later