Morning everyone, I was wondering if anyone knew a way I could do the following:
I need to design a workbook for work, which the supervisor can use every day to add a new row of information.
Basically, we deal with 14 branches. Each branch sends us stuff on a daily basis. There are 3 different types of information each branch can send. Let's say "X, Y and Z". We're wanting to trace how many X, Y and Z's each branch sends on a weekly and monthly basis so we can identify trends.
So the worksheet looks as follows and is called "Daily Data"
........|BRANCH 1 |BRANCH 2 | BRANCH 3 |
DATE | X | Y | Z | X | Y | Z | X | Y | Z |
So I've made a userform which will allow the supervisor to input all this data in and each day it creates a new row with the date and then the amounts of X, Y and Z received for each branch.
The supervisor will want to see a Weekly and a Monthly report of what has been received in total.
We work monday to friday, so the dates will go for example 07/12/09 to 11/12/09 and then will skip to 14/12/09 to 18/12/09 and so on.
So in the create weekly report userform, I first of all need a way to make the drop down menu only show the week beginnings i.e. only the dates which fall on a monday i.e. 07/12/2009, 14/12/2009 etc. And then when that week is selected, click a button and it pulls all the amounts of X, Y and Z for each branch over into a spreadsheet for monday-friday between that date range so you get a report of how many of X each branch has sent for that week, how many of Y, and how many of Z and then a total number.
I'd like a report that would show for example... and that'd be the weekly total
BRANCH X Y Z TOTAL
BRANCH 1 2 1 0 3
BRANCH 2 2 1 1 4
BRANCH 3 3 0 2 5
TOTAL 7 2 3 12
I could easilly do all this in PHP... but my work does not have a way of supporting PHP and a MySQL database... so I need to see if there's a way to do it in excel.
I'd be grateful if anyone can help :-) thanks in advance
Bookmarks