W
WorkerB5
I've got a large volume of data based on tranists of data. A typical ro
entry would be (with : signifying a new column of data in the row)
DATE : NAME : REF : TEXT : VAR :
DATE is the date the item was sent via transit
NAME is "Surname, Initials" format
REF is in the format "AA 11 11 11 A"
TEXT is just to let the user know what was in the tranist
VAR is the type of tranist, there are 5 types, call them A, B, A/B,
and D. A/B is a transit of the same thing to two places. These are se
codes that don't change.
There are 13 worksheets, one for each month, and a final one fo
calculating averages, percentages and so on.
I already have monthly averages and a set up which will autupdat
itself as soon as a new claim is entered, but what I need it to do i
find a total for each week as well, then output near the foirmula th
ending date for that weeks data, all automatically so that a user ca
just take the blank workbook and enter their data and have the stat
pop up as if by magic. Some of the data for each week will naturall
fall across two months which presents a further problem.
My initial reaction was to stick all the data on one spreadsheet an
use the various date functions and endless countif and if functions
but that's a nightmare and makes it very user unfriendly. So, i'
looking to Excel VB or excel macro scripting to sort this out... onl
thing is I don't know a thing about macro scripting or Excel VB,
can't find a website on it and I can't find a book either, but I di
find this place so maybe there is hope.
Ultimately I want to create a setup in which the user will press
shortcut or button and the user will then be prompted to enter data
and the statistics will just auto update and be flexible enough that i
will still be usable in 5 years time... but I don't expect help o
that, just to let you know where I want to take this. I'd be overjoye
with the information on how to make the spreadsheet display weekl
totals.
I'm currently using code like
Code
-------------------
=COUNTIF($E3:$E140,"A")+COUNTIF($E3:$E140,"A/B"
-------------------
to gather the information, but I also want this to be conditional o
the date, so it will only gather the above data for a given 5 day
period, which may or may not need to go over two worksheets... Is i
possible without resorting to Excel VB or macro scripting, and if i
needs that, can anyone point me at a good source of information?
WorkerB
entry would be (with : signifying a new column of data in the row)
DATE : NAME : REF : TEXT : VAR :
DATE is the date the item was sent via transit
NAME is "Surname, Initials" format
REF is in the format "AA 11 11 11 A"
TEXT is just to let the user know what was in the tranist
VAR is the type of tranist, there are 5 types, call them A, B, A/B,
and D. A/B is a transit of the same thing to two places. These are se
codes that don't change.
There are 13 worksheets, one for each month, and a final one fo
calculating averages, percentages and so on.
I already have monthly averages and a set up which will autupdat
itself as soon as a new claim is entered, but what I need it to do i
find a total for each week as well, then output near the foirmula th
ending date for that weeks data, all automatically so that a user ca
just take the blank workbook and enter their data and have the stat
pop up as if by magic. Some of the data for each week will naturall
fall across two months which presents a further problem.
My initial reaction was to stick all the data on one spreadsheet an
use the various date functions and endless countif and if functions
but that's a nightmare and makes it very user unfriendly. So, i'
looking to Excel VB or excel macro scripting to sort this out... onl
thing is I don't know a thing about macro scripting or Excel VB,
can't find a website on it and I can't find a book either, but I di
find this place so maybe there is hope.
Ultimately I want to create a setup in which the user will press
shortcut or button and the user will then be prompted to enter data
and the statistics will just auto update and be flexible enough that i
will still be usable in 5 years time... but I don't expect help o
that, just to let you know where I want to take this. I'd be overjoye
with the information on how to make the spreadsheet display weekl
totals.
I'm currently using code like
Code
-------------------
=COUNTIF($E3:$E140,"A")+COUNTIF($E3:$E140,"A/B"
-------------------
to gather the information, but I also want this to be conditional o
the date, so it will only gather the above data for a given 5 day
period, which may or may not need to go over two worksheets... Is i
possible without resorting to Excel VB or macro scripting, and if i
needs that, can anyone point me at a good source of information?
WorkerB
