Hi Twinny. Couple of questions for you:
1. what version of excel are you using? The sample file I've posted will only work in excel 2007 or greater, but I can make it work in excel 2003 and below if required.
2. Are you familiar with pivot tables? They can be very helpful when grouping data by days, weeks, months, years etc and you can also display totals as sum, percentage, and a number of other calculations. Check out the sample file I’ve posted. My sample does not exactly match your desired table structure… partly because the way you have your sample tables laid out doesn’t really make sense to me.
For instance, you said that a daily report is used to input daily stats of sales activities. But there’s no date field on this daily report, and it just shows data for one day, so I don’t understand how users are currently entering data from each subsequent day, and where that data is being stored so that you can retrieve it for the weekly and monthly views. Perhaps it gets put in a database? Or perhaps you have not got that far yet?
Also, usually the point of having a weekly or monthly view is just to summarise the daily transactions up into weekly or monthly totals, but your tables don’t do that…instead they just show either 1 day’s worth of transaction totals ,, 1 week’s worth of transaction totals, or 1 month’s worth of transaction totals. If this is all that is really required, then you really only need one table where you hide all but the selected row for the daily view, all but the selected week’s rows for the weekly view, and all but the selected month’s rows for the monthly view. But I don’t understand the point of this?
You’ll see from the attached file that instead of an input table, my method requires users to add their data at the bottom of an excel table that simply has “Date”, “Item”, “Status”, and “Number” columns. That’s because pivot tables need to draw data from what’s called a flat file structure, as opposed to the table (tabular structure) that you currently have . It is possible that a macro could take the daily data from your Daily table, then write it to a flat file, but before we start down that path it would be good to hear from you further about what you need, and whether my approach would be suitable.
If you want to get more familiar with pivot tables, I highly recommend the Contextures site (
www.contextures.com) and also Debra's books on pivot tables.
