how do I build linked workbooks

[Sandre]

My company has asked me to set up a quantified evaluation for each employee.
My parameters are: phone calls, work produced and errors, checks requested,
and accounting submitted.

I am dealing with 70 employees over 5 teams, each team handling multiple
clients. Each person's stats are gathered on a daily basis, and will need to
roll up by client and team, and then into individual evaluations as well.

Is there an "easy" way to do this so that each year I don't have spend a
month manually creating all these formulas and links? Also, any suggestions
on the best setup for the daily data so it's easier to pull into higher level
spreadsheets?

Thank you all in advance

 

[dlw]

you are basically looking at one sheet with 8 columns, date, employee, team,
client, phone calls, work/errors, checks, accounting. That's your raw data.
Then just use sorts/filters/subtotals/vlookups off the raw data to produce
the higher level sheets.
I think that would be easier than having multiple sheets that roll up.

 

[Pete_UK]

It's probably better to put all your data in one sheet. This will mean
that you will need a column to denote the team, and another one to
record the date. This will make it easier to compile summary stats and
reports on separate worksheets, maybe on a monthly basis. Once you
have a monthly report sorted out, it is then quite easy to copy that
sheet into the same workbook, and just change the reference month/
year. I presume you will want to count some fields, sum some others,
and do oher analysis by two or more parameters. Consequently, you will
use COUNTIF, SUMIF, SUMPRODUCT functions within your summary sheets.
You might also want to do a Top-5 or Top-10 table, and this can also
be achieved by formula.

Hope this helps.

Pete

 

[Sandre]

Thank you both for your help. My problem is that each Supervisor will be
entering their own daily stats, but after that the rollup should be seamless
and they should never have to enter data for the other functions.

BUT

Thanks to these responses, I have been able to consolidate my spreadsheets
down to one per team, and that will make it much easier to link the rollups.

Thank you!!

 

[Eduardo]

Hi Sandre,
an userform will solve all your problems, then you can run Pivot tables to
summarize your data as you want, you can take a look to Debra web to start

http://contextures.com/xlUserForm01.html

then go to excel tips in the same page at the top and look for the 2nd part
of userform, as well check

http://word.mvps.org/FAQs/TblsFldsFms/LinesInForms.htm
http://word.mvps.org/FAQs/Customization/FillinTheBlanks.htm