Why not use a Pivot Table to report on the data in Columns A:D?

Group Rows by Year, Major and Name, use Max(GPA) as data, and filter Name to
show top 1 based on Max(GPA)

This would easily give you a quick snapshot report of all the champions for
each Year / Major combination.

--
Olly


"gsd" wrote in message
oups.com...
Hello to y'all Excel Guru's,

We have hundrets of students in different years of study ( year 1
through year 4 ). In Column "A" I write the year of Study, "B" the
name of the Student, "C" contains the Major-of-study and "D" contains
the GPA.
The other columns are used to enter the different Grades.

E.G.

A B C D
E F G =......
Year Name Major GPA Math English
Phyiscs

1 name1 Mechanics 2,98
1 name2 Art 1,96
2 name3 Mechanics 2,25
1 name4 Art 1,96
3 name5 Mechanics 2,36
4 name6 History 3,2
3 name7 Mechanics 2,42
1 name8 Art 2,02
etc....

We keep entering the grades as they come.( Column E through AZ ). I
use the Worksheet_Change event
to calculate the new GPA when a new grade is entered. This works
fine.

Here my event.
----------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column 5 And Target.Row 3 Then
RememberRow = Target.Row
Calculate_GPA
End If

End Sub

' I use a public variable (RememberRow ) to store Target.Row. because
the event changes the focus of the active
' cell after the user hits Enter.
' In my procedure "Calculate_GPA" I use this variable for calculating
and writing the GPA in the correct Cell.
----------------------------------------------------------------------------------

The Sheet must also be flexible in terms of entry of new students,
changing Majors, or eliminating students. This
means I do not want to work with fixed formulas ( keeping in mind that
unexperienced users will enter Data )
and for this reason I thought that the Change-Event would be a good
solution. This way users can not easily manipulate or delete or
overwrite formulas ;-)


Now my Problem :
We do have system that the currently best GPA of the Major (Column C)
in the year of study ( Column A ) gets on the Board of Exclence.
( Champion )
Of course this may change on every Entry..which may be on a daily
basis.
There may also be two or more Champions ( see name2 and name4 ) which
are in the same year, same major and do have an equal GPA, which
happens to be the best in the category.

Can a function be written, that triggers on Data-Entry, and finds the
following :
a) What is the best grade in the Major ( Column C ) and the Year of
Study (Column A ) of ActiveCell.Row
b) Highlite the Name and the GPA of the found Values.
c) maybe even write the best GPA in a List on another Sheet ? ( I
could creat a List with the years of Study and the possible majors,
where the best GPA could be stored and maybe used for a later V-
Lookup ? )

In other words...
Find the year of Study AND the Major of Study of the Active Cell.Row
( where I enter the Grades and calculate the new GPA )
Then go ahead and rank all entries of the active Year of study AND the
active Major of study.

In the above Example :
Let's say I enter Grades of a Math Test for name 4. The
Worksheet_Change event triggers and calculates the GPA to be 1.96.
Now I need my funktion ( or any other solution ) that Resets all "Old"
highlites of the ACTIVE Year and ACTIVE Major and then highlites name2
AND name4 because they currently carry the title of best GPA in their
major and year of study.
I need the Reset because the old Champion loeses his/her Title ;o)
However, the other majors and years of study did NOT change and shall
remain as they are.

Can somebody advise me please ?

Oh.. we are using Excel 2003 on Windows XP ;-) and we are on a
Newtork with different users ( teachers )

Regards,
Gernoth