Excel 08 Formula Builder Help

[katerbugs]

I am currently using excel 08 and have made a workbook with multiple worksheets in it. Each worksheet contains information on multiple products, such as description, UPC, cost, retail, sale cost, sale retail, how many items sold during sale, etc. Sales are two weeks, so every two weeks, I add a new worksheet with new information for sales I will be running a few weeks down the road. I would like to see if there is a way that I can have excel look through the past worksheets so when I enter a UPC into the new worksheet, there would be a column at the end that would take the average items sold during past sales. For example, if I want to put UPC 1234500000 on sale in the new worksheet, that excel would look at past times I had 1234500000 on sale and take the average of how many sold during past sales and enter that number in a cell on the new worksheet. I don't know if this is relevant or not, but Column D is where I enter UPC info, Column P is where I put how many sold, and Column Q is where I want this average sold to appear (no matter what worksheet). I hope this is possible, and I appreciate any help I can get!

Thanks,
Kate

 

[peetah]

You need some type of system (possibly like Quickbooks Point of Sale) that that has a reasonable reporting tool that will let you create your own reports.

More precisely, you don't need Excel, you need a database to handle this. Import the data from your spreadsheets into the database, then write up a small report that gives you precisely the data you want.

I'm not even too sure that the Excel Data Analysis Tool Pack (available on the Windows version of Excel) will help. In fact, I'm pretty sure it won't automatically help because you'll have to be able to tell Excel what worksheets (new and old) to look at before you can even run your report.

Good luck!