personnel performance scorecard

Can you be more specific about the kind of errors you want to record?
If you mean deviations from the set targets then there is a challenge, because achieved and target are (I guess) for the whole year, whilst the recording is monthly.
And I would add a column "prospected" which shows a prediction of achievement by the end of the year based on results sofar.

ERROR details would be for instance hospital , ward , date, start and end time,and reason

Puzzles me. I believe you're operating some kind of call center? What kind of business are dealing with?
Having an idea of that would help me.

Will look at the sheet in the meantime and come up with some ideas. Hope I want shock you too much

Here's a workbook with some suggestions.
Still not sure if I'm getting anywhere near to what you intend with the "errors". Just gave it shot.

I suggest using a table like the enclosed that you can fill in on a regular basis whether daily, weekly or monthly and this will allow you to filter for the employee by name and date. You can also do group analysis by filtering on any of the columns to get statistics for that particular subject. I have included a TOTALS row at the bottom of the table. You can change what kind of calculation is made for each column. The totals are adjusted according to what filter has been applied. If you select one employee, the totals are for that employee.

This is quite confusing.

Does this workbook represent one person? If it represents more than one person, where are the statistics for each individual?

The organization of the data doesn't appear to be optimal for extracting data on any one individual if this is the way the data is recorded per individual.

I think that something like this (red tabs) would be a better way to list your data and use a Pivot table to get the summaries.

Here's an example of how things could be linked together. Used the data you supplied. That helped a lot!
Haven't been looking at the "one-to-one" sheet yet.
The sheet "Fill rates per day" contains a box with some questions.

Serious issue is how to deal with summarizing months and employees.
Pivot tables like Newdoverman suggested would be an option to deal with that.
I'm not familiar with pivot tables (never use them), but I'm sure Newdoverman would be happy to help you with that. Would be a chance for me to learn
On the other hand you should realize that at the end you will be to one to maintain and change the workbook if necessary.
That means that for that you must understand pivot tables.

If however, you're not feeling comfortable with pivot tables we can discuss other solutions.
Guess at this point I need your answer to the questions and your view on how to move forward.

OK. Thx for the answers. Anxious to hear if you want to jump into pivot tables
Will look into other solutions in the meantime.

If you want to learn about Pivot tables and a host of other Excel things this is a very good site.

http://contextures.com/

There are also many instructional videos on YouTube. Just search for excel and you will be presented with a lot of choices. I have found that ExcellsFun has over 1000 videos starting with the very basic and works up from there. These videos have spread sheets for download to follow along with the lessons.

https://www.youtube.com/user/ExcelIsFun

Here's a redesigned workbook with the separate sheets merged into one personal sheet.
Still working on it. Just for you to see if that would be working for you.

Here's the redesigned worbook again. Totals are working now.
Not sure what you want to put in the one-to-one section.
Added sheet protection to prevent formulas become overwritten. No password though.
Green zones are input zones.

A filter only applies to sheet it's on. So, you cannot jump to an employee's sheet thru the filter.
Alternatively you could design an overall sheet with a summary for all employees. From there you could jump to an individual employee's sheet thru a hyperlink.
Guess you will want to have an overall overview anyway. So that would match perfectly.

would that be hard to achieve

Not really. See attachment.

my works excel is 2003 how do i save these file so they run ok at work,im guessing i save as 2003 workbook

Yes. Doing that you will probably get a warning about possible loss of functionality. But I haven't seen any difference between the two versions.
Xls-files, however, are much bigger than xlsx-files!

Does this also mean that at work you're still on Win XP? Could be a serious security risk!

Originally Posted by dougers1

how do i get this to work
if i enter data in cells in one sheet to automatically copy the data to the same cells in other sheets.as some of my data i collect will be the same for each staff member,,,save on typing time

You can select multiple sheets by clicking on their tabs while holding down [Ctrl]-key. Then everything you type in the sheet you're viewing will automatically by copied to the other selected sheets, like a carbon copy. Goes also for formatting, inserted/deleting rows and columns etc.
Nice feature, but make sure that the sheets have exactly the same layout.

A good strategy would be to have one sheet as a template and than after you're satisfied with it make a copy of the template for all employees. Then you know for sure they're all the same.

BTW, as you're inserting new rows, it's a good thing to add using names. This makes your sheets more flexible.
Normally Excel automatically adjusts cell references in formula when rows and/or columnd are inserted or deleted. In the Overview sheet there are cell references done by the indirect function.
In that case the reference is just a string and those are not adjusted. By replacing these references with names these references also become flexible.
As these names will be in the employee sheets, it is best when they are defined in the template sheet. When you then create employee sheets from the template these names are automatically copied along.
Saves a lot of clicking and typing again

Hi
I have mutiple sheet in a work book from Jan'14 - Dec'14.
In all these sheets i have My team members KPI is measured for every month. I need to share the consolidated view by sum the value for each Team member KPI in Final Sheet to explain the Year to Date Performance.

this has thrown a spanner in the works for me,,i like what you have done and now wondering how i could get this to match up with what i have,


how did you do that dashboard. the links are great.is this easy to learn

@Vinothsm
if you think you can add to this thread, please be welcome. If you questions of your own, please start you're own thread to avoid confusion. See forum rules #2
Fancy sheet though

@Dougers1
The dashboard is done with SmartArt. If you get tired of these boring formulas have some fun with that. Will cheer you up

Shouldn't be too difficult with the wizard.

@Vinothsm
Thx! If you want me to take a look pls send me the link to the thread.

@Vinothsm
you're still hyjacking this thread (and others). Again, please start your own thread and you will get all the help you need.
I will have a look at your workbook and post any comments in your thread as soon as it's there.
In the FAQ you can read how to create a thread.

Gave it a try with a pivot.
Cannot say I'm convinced yet.
It's an awful lot of work making a datasheet fit for use with a pivot table. See sheet "Data".
I think that format will not make data entry any easier.
Created a pivot table on sheet "Pivot" showing the result so far.

Then again, like I mentioned before, pivot tables are not my cup-of-tea, so probably I haven't discovered all the features yet or (also likely) I'm using a wrong approach.
See what you find of it.

decided to try it this way , need some help with formula for total talk time colmn i can get the traffic light system to work in the ist cell but not the rest of the cells in column ?

also want to be able to do it on new sheet for month

for example
jan feb ect
name 1
name 2
ecct

For the traffic lights the conditional format can only handle absolute references (e.g. $K$3).
You probably created the conditional format for the 1st row and then copied the format to the other rows.
With relative references (e.g. K3) Excel would automatically adjust the reference, but not so with absolute references.
That makes that the conditional format of he 2nd row etc keeps looking at the first row (i.c $K$3) and so the 1st row seems to work and the others don't.

To solve this I inserted an additional column holding the formula of the conditional format, but it's only showing the trafic light. That's an option you can set.

Added some additional comments in the attached sheet.

Will get back to you about to other things.

thanks again, can it be sorted so that the cell changes colour depending on the result

You can sort on the cell pictogram, but you have to do that manually.

also want to be able to do it on new sheet for month

for example
jan feb ect
name 1
name 2
ecct

Isn't this similar to what I posted earlier (see post #25)? That layout could of course also be with months in the colums and rows for the employees.
Attaching another example based on your last post.
Maybe I'm way off of what you intend. Of so, could make up a sample of how you would like the sheet to look? No need for formulas or other technicalaties.

Ok, look at your formulas.
Some comments/tips:
- At some point in time you will probably want to represent time as time instead of decimal values. That's where the formulas need adjustment.
But as long as you keep showing evething as decimal it will be ok as is.
In the attached sheet I show you what will happen and how you can adjust the formulas

- Also I would recommend the use of absolute and relative referencing when applicable. Always using relative references can have Excel change formulas inadvertedly and above all unexpectedly.

- Finally I introduced the use of names which make your formulas more readable. I think this is one of the most powerful features of Excel.
The attached sheet also contains examples hereof.

i can filter by name for each month wich would show the total for the month

Looking at the attached file you just did that.
Only issue is that the month column is not part of the table. That might cause some problem.

Aha! I get it (finally)
For that you need the sheets with the daily input to be integrated in the workbook. Then you can collect the totals with some SUMIF-like function.
Actually kinda did that already in KPI_example_redesigned.xlsx (see post #25). The sheet Overview could be replaced by the overview from new_sheet.xlsx or book2.xlsx, what you prefer.
If this is what you're looking for I can make up an integrated example.

One quick question:
how will input the data like inbound calls, utbound calls, login time, etc.
Is there some kind of call center management system exporting this data or are employees filling in forms or something else.
Just for the data collections sheet(s) to adjust as much as possible to your way of working.

Ok, here's the example I promised you. Still a lot to do, but it should show how to collect data from the sheets.
Comments are on the sheets.
I will explain some complex formulas later. First see if this is the way you want to go.
If so I would propose to further build on this version of the workbook.

Tip: you can follow the evaluation of a formula live when you select the cell containing it and - in the Formula's menu - select Evaluate Formula.
Nice feature to help you understand what's going on.

Glad you're happy with the workbook. Now we can work towards a workbook you can put to work for you. Assume that in the end that's what you're after
Here's a new version.

• Added a Totals section to the individual sheets (including the template)

• Added the Center Daily Totals sheet.

I think next step should be for you to decide on the data items to include.
Not all data items are covered by the individual sheets as well as the totals sheets.

And of course going through the sheets and see if you understand all the formulas. Notice the tip in my previous post.

Here's a new version with the adjustments required.
Need to look into the traffic lights yet.

Now working on some decent documentation, so you can handle any further changes yourself. Like adding the other employees ...
Will still help you out if you get stuck, of course.

Edit: documentation ppt (zipped) attached.