Simplifying statistical spreadsheets

[RMP]

Hello,
Please can you provide me with some ideas. I am trying to create a
spreadsheet which shows the throughput of a team of 7 people and calculates
weekly and monthly totals keeping manual input to a minimum.

We deal with 25 different types of tasks on the team and what I need to do
is create a spreadshhet where you enter the totals of work completed by each
team member, by type of work and insert formulas that create weekly and
monthly totals.

I'm trying to avoid having a complex spreadsheet and want to make it as time
efficient as possible. Basically entering the data under each members name
showing the type and amount of work completed each day but feeding the totals
through to another worksheet (which i can handle)

My main problem is that I would have to have 7 columns (1 for each member)
for each day and by the end of the month the spreadsheet would be too busy
and too complicated.

Are you able to help.

Thanks

 

[Roger Converse]

Can you build your table down?

Name Task Date Hours
Roger Task1 12/14/2007 4
Roger Task2 12/14/2007 4
Roger Task1 12/15/2007 4
Roger Task2 12/15/2007 4

From there you could pivot table and add calculated fields for your
spreadsheet or create an Excel query and run from there.

Thanks,
Roger

 

[RMP]

Thanks that was very helpful. Still getting to grips with the pivot tables
though. Won't take me long.

One more question please: If I'm to set a template spreadsheet to be used
every month, how can I protect all the formulas so that no1 can alter them by
mistake? Can I lock the formulas and tables at all?

Thanks again.

 

[cindyshanks]

Build your table with each row as a staff record. Time is one column and a
validated drop-down list of the 25 categories. Set up a new sheet for weekly
report
adding formulas to using COUNTIF to count "type of work" and multiply by
total hours for that week. Copy that formula down for each type of work.
Once that weekly report is done you can copy it for each week and copy and
modify for the final monthly. Once the formulas are all done, the workbook
will do all the work for you and you just print the reports.

Another option is to set up to use pivot table. You can refresh the pivot
table every time you need a summary of the revised data.

 

[RMP]

Thanks for your help.

Are you able to help with this question please:
One more question please: If I'm to set a template spreadsheet to be used
every month, how can I protect all the formulas so that no1 can alter them by
mistake? Can I lock the formulas and tables at all?

 

[Stephen]

Tools > Protection > Protect Sheet
will protect all cells that are locked. By default, all cells are locked, so
protection applies to them all. So what you have to do is to "unlock" those
cells that you DON'T want protected (such as those into which you will be
entering data) BEFORE protecting the sheet. To do this, select those cells
and use:
Format > Cells > Protection tab
Remove the tick in the Locked box.