Productivity Log
 

Ok, I have a difficult scenario. I have all of these folks entering all the
work they have done throughout the week into a log. I then take their weekly
report and input it into a scorecard. This is a very manual process and I
have inherited it. Any suggestions on how to better automate this.

Here are the steps I take.

I have employee input the work they did throughout the week into a simple
excel log like so:

Hard Medium Easy
ABC Inc 06/17/08 X
ZZZ Inc 06/17/08 X
CCC Inc 06/17/08 X


Then each week, I take this information and manually input it to a seperate
log which automatically calculates different scores based upon scoring of
hard/easy etc. I then automatically rank them and the final report looks
something like this.

Score Rank
Employee 1 195 1
Employee 2 130 2


Since we have over 50 employees doing this each week it is very manual. Any
suggestions on automating this ?
Thanks

Productivity Log
 

Is each employee doing their log in an individual workbook or do they simply
have a worksheet assigned to them inside a master workbook? Is your now
manually created log in this master workbook or in a workbook of its own?
What and where are the weighting numbers of the Hard, Medium and Easy
categories located at? It is always a good idea, when asking questions
online, to keep in mind that no one online knows your particular business
model, so providing details as to what the parts of it (relevant to your
question) look like is always advisable.

Rick


"drew" wrote in message
...
Ok, I have a difficult scenario. I have all of these folks entering all
the
work they have done throughout the week into a log. I then take their
weekly
report and input it into a scorecard. This is a very manual process and I
have inherited it. Any suggestions on how to better automate this.

Here are the steps I take.

I have employee input the work they did throughout the week into a simple
excel log like so:

Hard Medium Easy
ABC Inc 06/17/08 X
ZZZ Inc 06/17/08 X
CCC Inc 06/17/08 X


Then each week, I take this information and manually input it to a
seperate
log which automatically calculates different scores based upon scoring of
hard/easy etc. I then automatically rank them and the final report looks
something like this.

Score Rank
Employee 1 195 1
Employee 2 130 2


Since we have over 50 employees doing this each week it is very manual.
Any
suggestions on automating this ?
Thanks

Productivity Log
 

Each employee has their own log that they share to a shared location. At the
end of every week someone compiles this information and will place the total
number of transactions into the applicable column for each employee into a
master list. So the spreadsheet where we rank is a separate workbook.

We are trying to capture the number of deals we are processing, and based
upon the type of deal processed, the weighting will be different.

"Rick Rothstein (MVP - VB)" wrote:

Is each employee doing their log in an individual workbook or do they simply
have a worksheet assigned to them inside a master workbook? Is your now
manually created log in this master workbook or in a workbook of its own?
What and where are the weighting numbers of the Hard, Medium and Easy
categories located at? It is always a good idea, when asking questions
online, to keep in mind that no one online knows your particular business
model, so providing details as to what the parts of it (relevant to your
question) look like is always advisable.

Rick


"drew" wrote in message
...
Ok, I have a difficult scenario. I have all of these folks entering all
the
work they have done throughout the week into a log. I then take their
weekly
report and input it into a scorecard. This is a very manual process and I
have inherited it. Any suggestions on how to better automate this.

Here are the steps I take.

I have employee input the work they did throughout the week into a simple
excel log like so:

Hard Medium Easy
ABC Inc 06/17/08 X
ZZZ Inc 06/17/08 X
CCC Inc 06/17/08 X


Then each week, I take this information and manually input it to a
seperate
log which automatically calculates different scores based upon scoring of
hard/easy etc. I then automatically rank them and the final report looks
something like this.

Score Rank
Employee 1 195 1
Employee 2 130 2


Since we have over 50 employees doing this each week it is very manual.
Any
suggestions on automating this ?
Thanks

Try using this formula in your score sheet

=COUNTIFS(criteria range 1,"ABC Inc",criteria range 2,"DATE")+COUNTIFS(critieria range 3,"ABC Inc",criteria range 4,"DATE")

I use this when some of my criteria are on multiple sheets. For example if I received a file in January but I didn't complete it until February. Then my "DATE" would be a date in February and I need to add that to the other dates in February that are the same "DATE".

=COUNTIFS(July!$G$3:G120,ABC Inc,July!$I$3:I120,"8/2/2015")+COUNTIFS(August!$G$3:G120,ABC Inc,August!$I$3:I120,"8/2/2015")


I hope this helps. Good luck!