Turning Daily Reports into Monthly Reports

**jambezi** · 10-19-2009, 03:46 PM

Hello all:

I am trying to figure out how to turn a daily report we receive for separate items into multiple running reports for each specific items. But the problem is that another person keeps inserting new products alphabetically and messes up my automated running totals (which are tied into a cell reference, like A2) and not a product name (like Cat).

What I'd love is to move all new names to the bottom (I don't care about alphabetization) and maintain the integrity of my previous list. Please advise!

Here's an example:

Sheet 1 - Daily Report for X.X.2009

Product Name / Sales / Expenses / Receipts

Ape (A2) / 5 / 7 / 9
Cat (A3) / 3 / 6 / 9
Dog (A4) / 4 / 3 / 6
Zebra (A5) / 2 / 7 / 3

That is what we receive every day and I want to turn it into a running list of daily numbers for each thing (ape, cat, dog, zebra) like so:

Sheet 2 - Monthly Numbers for Dog (A4)

10.1.09 / 4 / 3 / 2
10.2.09 / 1 / 3 / 2
10.3.09 / 3 / 6 / 8

etc.

All things being equal, I could create a macro for that, but here's the catch

: Every so often accounting will just add a new product name (in alphabetical order!) so that it throws off all of my previous rows & totals!

So instead of importing A:3 = Cat, now all newly imported A3 numbers have to do with a newly inserted A2 in alphabetical order like Bear and Cat is now A=3. This obviously shifts all the below totals off 1 and I don't know how to get the list back to normal.

Ape (A2) / 5 / 7/ 9
Bear (A3) / 2 / 5 / 3
Cat (A4) / 6 / 3 / 2
etc.

This is where I get lost and am in need of advice.

How can I create a macro/script/filter that can maintain old alphabetical lists and search out new names (like bear, in this case) and move them to the bottom, so as to not disturb my previous cell references?

I am open to any type of solution and please ask if you need me to clarify.

Thanks in advance!

**davegugg** · 10-19-2009, 04:04 PM

It would be helpful to see how your spreadsheet(s) is(are) set up. Have you looked into the vlookup and hlookup?

**jambezi** · 10-19-2009, 04:45 PM

I have created and uploaded an example spreadsheet that shows both the problem and how I'd like to take each day's data and formulate it into a running monthly total (though I didn't create a column for monthly sums in the last report).

I hope this helps!

Please let me know if you have any suggestions about how to take the daily data, work around the problems, and create a process that standardizes it and converts it into the monthly totals. Thanks!!

**davegugg** · 10-19-2009, 05:05 PM

Ok, here is what I have. I gave you two examples of using vlookup, which I would think would be the best way to go. I also included a macro that simply moves the data over. You will have to customize it, as I don't know if the daily reports will be in the same workbook as the monthly reports. I also didn't know what the sheet names would be. I would recommend if you are building this yourself from scratch that you attempt to leave the Inventory, Receipts, etc. in the same columns on both sheets.

Let me know if you need me to help you with any refinements.