Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simplifying statistical spreadsheets
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 -- RMP |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simplifying statistical spreadsheets
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" wrote: 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 -- RMP |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simplifying statistical spreadsheets
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. -- RMP "Roger Converse" wrote: 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" wrote: 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 -- RMP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simplifying statistical spreadsheets
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" wrote: 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 -- RMP |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simplifying statistical spreadsheets
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? -- RMP " wrote: 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" wrote: 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 -- RMP |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simplifying statistical spreadsheets
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. "RMP" wrote in message ... 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? -- RMP " wrote: 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" wrote: 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 -- RMP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code needs simplifying | Excel Worksheet Functions | |||
Simplifying VBA code | Excel Worksheet Functions | |||
simplifying routine | New Users to Excel | |||
Simplifying a formula | Excel Worksheet Functions | |||
Simplifying formula | Excel Discussion (Misc queries) |