I am definitely new to this. So here it goes. I am
currently in the navy. My officer in charge has a policy
that all trainees be tracked (on critique sheets) by
their performance and the number of hours the had ojt.
He wants a program that would allow him to 1) enter and
store data on a given date 2) calculate the number of
hours of training for the given month 3) create a profile
sheet of each trainee that keeps track of their total
hours and the last three performances . There are 21
trainees. I have excel 2002. I have been working on
this for about a week now, with no luck. I thank you for
your time.

Create a Database with each event in a separate row. Columns would indicate
the information

Date Trainee_Last_Name Trainee_First_Name SSN OJT_Task Hours
Sequence

Headings such as these would be in the first row of the worksheet.

for sequence you would go to (in the example) G2 and enter a formula like

=if(countif($D$2:D2,D2)Countif($D$2:D2)-3,"Last 3","")

then dragfill this down the column. Column D holds the social security
number so you get a unique count for each seaman.

Assume this sheets name is Data

Now do Insert=Name=Define

Name: Database
RefersTo =Offset(Data!$A$1,0,0,CountA(Data!$A:$A),7)

[ the 7 means 7 columns wide. Adjust to fit your actual database. The
sample has 7 columns).
then click Add

Now go to the name box in the upper left corner, enter Data and then Enter
and it should highlight you database. If not, you need to correct your
defined name by doing Insert=Name=Define again.

Now to get you summary information, you can do

Data=PivotTable And Pivot Chart

It should default to you defined name as the source ("database"). If not,
type in the name Database.

go to the next dialog in the wizard and select layout.

Drag SSN to the page field row (or perhaps you want to create a "Dummy"
column in your database that contains a formula like

=B2 & ", " & C2

so it shows LastName, FirstName

then use that as your page field (each column in the range database must
have a column name similar to the original example, even dummy columns).

Put sequence in for the second page field

Put in Date as the first row field and hours in the Data field.

You can put OJT_Task in the column field if you want that level of detail
and there are only a countable number of unique categories for each seaman.

Now select new sheet and designate a cell like A10 (leave some room at the
top) and click finish

When the pivot table is created, you should have your basic profile.

Click on the SSN or Dummy Name field and select an individual.

If you want your data grouped by month, right click on any field in the date
area and select group. Group on Year and Month.

Repeat and ungroup if you want details.

If you want to see Top 3, in the Sequence dropdown select Last 3.

select the complete pivot Table and copy it and paste it to the right of the
original. You can then format as you wish, so you can have two simultaneous
views.

There is no need to have separate sheets for each seaman. You can just
select the name in the dropdown.

I wouldn't suggest putting one pivot table below another because different
views will have different numbers of rows and this could cause problems if
there isn't enough room to display the new rows.

See Jon Peltier's site for additional information:

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

--
Regards,
Tom Ogilvy





"PO2Benn" wrote in message
...
I am definitely new to this. So here it goes. I am
currently in the navy. My officer in charge has a policy
that all trainees be tracked (on critique sheets) by
their performance and the number of hours the had ojt.
He wants a program that would allow him to 1) enter and
store data on a given date 2) calculate the number of
hours of training for the given month 3) create a profile
sheet of each trainee that keeps track of their total
hours and the last three performances . There are 21
trainees. I have excel 2002. I have been working on
this for about a week now, with no luck. I thank you for
your time.

Correction on one formula -

=if(countif($D$2:D2,D2)Countif($D$2:D2)-3,"Last 3","")

should be:

=IF(COUNTIF($D$2:D2,D2)COUNTIF($D:$D,D2)-3,"Last 3","")

--
Regards,
Tom Ogilvy





"Tom Ogilvy" wrote in message
...
Create a Database with each event in a separate row. Columns would
indicate
the information

Date Trainee_Last_Name Trainee_First_Name SSN OJT_Task Hours
Sequence

Headings such as these would be in the first row of the worksheet.

for sequence you would go to (in the example) G2 and enter a formula like

=if(countif($D$2:D2,D2)Countif($D$2:D2)-3,"Last 3","")

then dragfill this down the column. Column D holds the social security
number so you get a unique count for each seaman.

Assume this sheets name is Data

Now do Insert=Name=Define

Name: Database
RefersTo =Offset(Data!$A$1,0,0,CountA(Data!$A:$A),7)

[ the 7 means 7 columns wide. Adjust to fit your actual database. The
sample has 7 columns).
then click Add

Now go to the name box in the upper left corner, enter Data and then Enter
and it should highlight you database. If not, you need to correct your
defined name by doing Insert=Name=Define again.

Now to get you summary information, you can do

Data=PivotTable And Pivot Chart

It should default to you defined name as the source ("database"). If not,
type in the name Database.

go to the next dialog in the wizard and select layout.

Drag SSN to the page field row (or perhaps you want to create a "Dummy"
column in your database that contains a formula like

=B2 & ", " & C2

so it shows LastName, FirstName

then use that as your page field (each column in the range database must
have a column name similar to the original example, even dummy columns).

Put sequence in for the second page field

Put in Date as the first row field and hours in the Data field.

You can put OJT_Task in the column field if you want that level of detail
and there are only a countable number of unique categories for each
seaman.

Now select new sheet and designate a cell like A10 (leave some room at the
top) and click finish

When the pivot table is created, you should have your basic profile.

Click on the SSN or Dummy Name field and select an individual.

If you want your data grouped by month, right click on any field in the
date
area and select group. Group on Year and Month.

Repeat and ungroup if you want details.

If you want to see Top 3, in the Sequence dropdown select Last 3.

select the complete pivot Table and copy it and paste it to the right of
the
original. You can then format as you wish, so you can have two
simultaneous
views.

There is no need to have separate sheets for each seaman. You can just
select the name in the dropdown.

I wouldn't suggest putting one pivot table below another because different
views will have different numbers of rows and this could cause problems if
there isn't enough room to display the new rows.

See Jon Peltier's site for additional information:

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

--
Regards,
Tom Ogilvy





"PO2Benn" wrote in message
...
I am definitely new to this. So here it goes. I am
currently in the navy. My officer in charge has a policy
that all trainees be tracked (on critique sheets) by
their performance and the number of hours the had ojt.
He wants a program that would allow him to 1) enter and
store data on a given date 2) calculate the number of
hours of training for the given month 3) create a profile
sheet of each trainee that keeps track of their total
hours and the last three performances . There are 21
trainees. I have excel 2002. I have been working on
this for about a week now, with no luck. I thank you for
your time.