How Do I do This?

[S Jackson]

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?

 

[Jim Thomlinson]

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...

 

[S Jackson]

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

 

[S Jackson]

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]

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