Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I do This?
I have a spreadsheet that I've used to track employee's different types of
sick leave. On one spread sheet I created a list of the types of sick leave. Underneath I created a 5 column table to track each employee's daily use of sick leave used or awarded. The first column the user selects an employee from a drop-down list. The 2nd column is the date, 3rd column is a drop-down to select the type of sick leave, the 4th column is for the user to enter the amount of time awarded, and the 5th column is to enter the amount of time used. Out to the side I want to track each individual employee's monthly summary for each type of leave. I can create individual tables for each employee's different kind of leave and it looks like this: Monthly Summary of Sick Leave Bonus Employee 1 Month Awarded Used Balance Carryover Sept 0.00 0.00 0.00 Oct 0.00 0.00 0.00 Nov 0.00 0.00 0.00 Dec 0.00 0.00 0.00 Jan 0.00 0.00 0.00 Feb 0.00 0.00 0.00 Mar 0.00 0.00 0.00 Apr 0.00 0.00 0.00 May 0.00 0.00 0.00 Jun 0.00 0.00 0.00 Jul 0.00 0.00 0.00 Aug 0.00 0.00 0.00 The formula in the Awarded column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$D$10:$D$150) The formula in the Used column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$E$10:$E$150) It works great, but the problem is the number of individual tables. There are 10 employees and 4 different types of leave. This makes 40 monthly summary tables - ack! Is there a way to create one monthly summary table that changes to reflect whatever employee a user has selected from a drop-down list somewhere? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I do This?
Have you considered using a pivot table instead to create your reports.
Select the raw data and choose Data - Pivot Table - Follow the wizard... Pivot tables are designed to aggregate (Sum, Average,...) grouped data such that it can be presented in different ways from different view points. One thing you will probabl want to do is use the Grou feature to aggregate the data by month, quarter, year... -- HTH... Jim Thomlinson "S Jackson" wrote: I have a spreadsheet that I've used to track employee's different types of sick leave. On one spread sheet I created a list of the types of sick leave. Underneath I created a 5 column table to track each employee's daily use of sick leave used or awarded. The first column the user selects an employee from a drop-down list. The 2nd column is the date, 3rd column is a drop-down to select the type of sick leave, the 4th column is for the user to enter the amount of time awarded, and the 5th column is to enter the amount of time used. Out to the side I want to track each individual employee's monthly summary for each type of leave. I can create individual tables for each employee's different kind of leave and it looks like this: Monthly Summary of Sick Leave Bonus Employee 1 Month Awarded Used Balance Carryover Sept 0.00 0.00 0.00 Oct 0.00 0.00 0.00 Nov 0.00 0.00 0.00 Dec 0.00 0.00 0.00 Jan 0.00 0.00 0.00 Feb 0.00 0.00 0.00 Mar 0.00 0.00 0.00 Apr 0.00 0.00 0.00 May 0.00 0.00 0.00 Jun 0.00 0.00 0.00 Jul 0.00 0.00 0.00 Aug 0.00 0.00 0.00 The formula in the Awarded column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$D$10:$D$150) The formula in the Used column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$E$10:$E$150) It works great, but the problem is the number of individual tables. There are 10 employees and 4 different types of leave. This makes 40 monthly summary tables - ack! Is there a way to create one monthly summary table that changes to reflect whatever employee a user has selected from a drop-down list somewhere? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I do This?
Thank you for your reply. I have been playing with a pivot table and it
looks like it is what I need, but unfortunately, I cannot figure out how to get it to do exactly what I want. I can select an employee at the top. The first column lists the type of leave, the second column is the date the leave was used or awarded, the 3rd column is the sum awarded for that day, the 4th column is the sum used for that day and the 5th column is a balance. My problem is how do I date it to group all of the dates by month? And, the second problem is that each employee is automatically awarded 8.0 hours of a certain type of leave each month. How do I add that calculation to the pivot table? TIA S. Jackson "Jim Thomlinson" wrote in message ... Have you considered using a pivot table instead to create your reports. Select the raw data and choose Data - Pivot Table - Follow the wizard... Pivot tables are designed to aggregate (Sum, Average,...) grouped data such that it can be presented in different ways from different view points. One thing you will probabl want to do is use the Grou feature to aggregate the data by month, quarter, year... -- HTH... Jim Thomlinson "S Jackson" wrote: I have a spreadsheet that I've used to track employee's different types of sick leave. On one spread sheet I created a list of the types of sick leave. Underneath I created a 5 column table to track each employee's daily use of sick leave used or awarded. The first column the user selects an employee from a drop-down list. The 2nd column is the date, 3rd column is a drop-down to select the type of sick leave, the 4th column is for the user to enter the amount of time awarded, and the 5th column is to enter the amount of time used. Out to the side I want to track each individual employee's monthly summary for each type of leave. I can create individual tables for each employee's different kind of leave and it looks like this: Monthly Summary of Sick Leave Bonus Employee 1 Month Awarded Used Balance Carryover Sept 0.00 0.00 0.00 Oct 0.00 0.00 0.00 Nov 0.00 0.00 0.00 Dec 0.00 0.00 0.00 Jan 0.00 0.00 0.00 Feb 0.00 0.00 0.00 Mar 0.00 0.00 0.00 Apr 0.00 0.00 0.00 May 0.00 0.00 0.00 Jun 0.00 0.00 0.00 Jul 0.00 0.00 0.00 Aug 0.00 0.00 0.00 The formula in the Awarded column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$D$10:$D$150) The formula in the Used column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$E$10:$E$150) It works great, but the problem is the number of individual tables. There are 10 employees and 4 different types of leave. This makes 40 monthly summary tables - ack! Is there a way to create one monthly summary table that changes to reflect whatever employee a user has selected from a drop-down list somewhere? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I do This?
FYI, I am using Excel 2000. When I right click on the Date field, choose
Group and Outline|Group, I get a message that I cannot group that selection. S. Jackson "S Jackson" wrote in message ... Thank you for your reply. I have been playing with a pivot table and it looks like it is what I need, but unfortunately, I cannot figure out how to get it to do exactly what I want. I can select an employee at the top. The first column lists the type of leave, the second column is the date the leave was used or awarded, the 3rd column is the sum awarded for that day, the 4th column is the sum used for that day and the 5th column is a balance. My problem is how do I date it to group all of the dates by month? And, the second problem is that each employee is automatically awarded 8.0 hours of a certain type of leave each month. How do I add that calculation to the pivot table? TIA S. Jackson "Jim Thomlinson" wrote in message ... Have you considered using a pivot table instead to create your reports. Select the raw data and choose Data - Pivot Table - Follow the wizard... Pivot tables are designed to aggregate (Sum, Average,...) grouped data such that it can be presented in different ways from different view points. One thing you will probabl want to do is use the Grou feature to aggregate the data by month, quarter, year... -- HTH... Jim Thomlinson "S Jackson" wrote: I have a spreadsheet that I've used to track employee's different types of sick leave. On one spread sheet I created a list of the types of sick leave. Underneath I created a 5 column table to track each employee's daily use of sick leave used or awarded. The first column the user selects an employee from a drop-down list. The 2nd column is the date, 3rd column is a drop-down to select the type of sick leave, the 4th column is for the user to enter the amount of time awarded, and the 5th column is to enter the amount of time used. Out to the side I want to track each individual employee's monthly summary for each type of leave. I can create individual tables for each employee's different kind of leave and it looks like this: Monthly Summary of Sick Leave Bonus Employee 1 Month Awarded Used Balance Carryover Sept 0.00 0.00 0.00 Oct 0.00 0.00 0.00 Nov 0.00 0.00 0.00 Dec 0.00 0.00 0.00 Jan 0.00 0.00 0.00 Feb 0.00 0.00 0.00 Mar 0.00 0.00 0.00 Apr 0.00 0.00 0.00 May 0.00 0.00 0.00 Jun 0.00 0.00 0.00 Jul 0.00 0.00 0.00 Aug 0.00 0.00 0.00 The formula in the Awarded column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$D$10:$D$150) The formula in the Used column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$E$10:$E$150) It works great, but the problem is the number of individual tables. There are 10 employees and 4 different types of leave. This makes 40 monthly summary tables - ack! Is there a way to create one monthly summary table that changes to reflect whatever employee a user has selected from a drop-down list somewhere? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I do This?
I am beginning to see that a pivot table may not work for what I need. I
solved the date group issue by not selecting blank rows in my data. However, when the user adds an entry to the original table, it does not update the pivot table because the pivot table is only looking at a set number of rows, e.g., Rows 2 - 5. And the second problem is that I need to keep each employee's running balance of each type of leave. I do not know how to do that with a pivot table. I know I can add a formula and total the balance of leave for each month, but not how to add a formula that then calculates Field1 + Awarded - Used. (I'm not sure if this makes sense, but its the best way I can explain it without having a picture.) Any thoughts? I'm losing hope. I'm beginning to think I'm going to have to create all the individual summary tables - ugh! S. jackson "S Jackson" wrote in message ... FYI, I am using Excel 2000. When I right click on the Date field, choose Group and Outline|Group, I get a message that I cannot group that selection. S. Jackson "S Jackson" wrote in message ... Thank you for your reply. I have been playing with a pivot table and it looks like it is what I need, but unfortunately, I cannot figure out how to get it to do exactly what I want. I can select an employee at the top. The first column lists the type of leave, the second column is the date the leave was used or awarded, the 3rd column is the sum awarded for that day, the 4th column is the sum used for that day and the 5th column is a balance. My problem is how do I date it to group all of the dates by month? And, the second problem is that each employee is automatically awarded 8.0 hours of a certain type of leave each month. How do I add that calculation to the pivot table? TIA S. Jackson "Jim Thomlinson" wrote in message ... Have you considered using a pivot table instead to create your reports. Select the raw data and choose Data - Pivot Table - Follow the wizard... Pivot tables are designed to aggregate (Sum, Average,...) grouped data such that it can be presented in different ways from different view points. One thing you will probabl want to do is use the Grou feature to aggregate the data by month, quarter, year... -- HTH... Jim Thomlinson "S Jackson" wrote: I have a spreadsheet that I've used to track employee's different types of sick leave. On one spread sheet I created a list of the types of sick leave. Underneath I created a 5 column table to track each employee's daily use of sick leave used or awarded. The first column the user selects an employee from a drop-down list. The 2nd column is the date, 3rd column is a drop-down to select the type of sick leave, the 4th column is for the user to enter the amount of time awarded, and the 5th column is to enter the amount of time used. Out to the side I want to track each individual employee's monthly summary for each type of leave. I can create individual tables for each employee's different kind of leave and it looks like this: Monthly Summary of Sick Leave Bonus Employee 1 Month Awarded Used Balance Carryover Sept 0.00 0.00 0.00 Oct 0.00 0.00 0.00 Nov 0.00 0.00 0.00 Dec 0.00 0.00 0.00 Jan 0.00 0.00 0.00 Feb 0.00 0.00 0.00 Mar 0.00 0.00 0.00 Apr 0.00 0.00 0.00 May 0.00 0.00 0.00 Jun 0.00 0.00 0.00 Jul 0.00 0.00 0.00 Aug 0.00 0.00 0.00 The formula in the Awarded column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$D$10:$D$150) The formula in the Used column is: =SUMPRODUCT(--($A$10:$A$150="Amy Castaneda"),--($C$10:$C$150="Sick Leave Bonus"),--(MONTH($B$10:$B$150)=9),--$E$10:$E$150) It works great, but the problem is the number of individual tables. There are 10 employees and 4 different types of leave. This makes 40 monthly summary tables - ack! Is there a way to create one monthly summary table that changes to reflect whatever employee a user has selected from a drop-down list somewhere? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|