Sheet 1

john
mark
eric

Sheet 2
A B C D E
1 john mark john john eric
2 open(10) 10 10 10 5 10
3 body(10) 10 10 5 10 n/a
4 close(5) n/a 5 0 5 0
5 total (25) 20 25 15 20 10

The numbers in ( ) are max pts that can be awarded per category.

With the sample table, what I need to do is total all the point for all data
in sheet one(john, mark and eric) and divide that with the total possible
points per evaluation(john=70-20+25+25, mark=25, eric=15) BUT I should only
total and divide the first 2 sample data.

So in this case for mark its 25/25
for Eric 10/15
for john 35/45 (first 2 - 20 + 15 / pts possible 20 + 25)

If you can give me just one formula without a helper cell, that would be
perfect.. please include explanations so that I can futher analyze the
formula.

Again, Tnx for all your help.