Ok I have created a data sheet for scoring canidates on a test. At the end of the test i would like to give them there average score based on all 3 judges.

I have created individual judging section where each judge can assess a canidate independantly and also see how that affects the canidates average score.

I have also made a sheet that will average the individual judges scores to make up a average for each possible gradeded technique.

I am intending on making a sheet that can propigate this data to a single page as opposed to printing 6 pages with one row accross the top.

I do not wish to havto manually put in a cell address like " ='Canidates Score Avg HORIZ'!D2" for each cell manually.

I have tried to transpose it to vertical so i could use the autofill handle and just drag down. = failed

So i have 3 sheets so far

Yudanja 1.1 = for judges
Canidates Score Avg HORIZ = averaged data for each canidate with headers
Canidates printable = template i would like to try and fit the data to one page.

Is there any easy way to get the data where i want it to go without plugging in each cell manually? im open to other layout options.

My biggest thing is they get all the data with only there score not all the canidates scores and that it fits on one page and is readable and not all mashed together. i would greatly appreciate any help in being pointed in the right direction.

I dont do much with macros or script but im willing to try.

heres link to what i have via dropbox

https://www.dropbox.com/s/kawynxc635...01.1.xlsx?dl=0

It would be a lot easier to manage/maintain if you put all the averages
on the score sheet, IMO! Also, I suggest you eliminate all duplications
of the headers.

Also, group the judge's scores for each student together; meaning each
student has a 'section' consisting of 1 row for each judge's score, and
an averages row.

I use 'modules' in my student grades manager app that group course
'sections' together. Each module totals scores similar to how you do
it. IMO, this is what you should do with your 'sections' (ie: Creeds,
Physical Training, Strikes and Punches...). Give these modules
ColAbsolute-RowRelative local scope defined names to use in formulas.
Insert a row above the headers to label each module, using shading to
indicate which cells in the module their respective defined name refs.
Use the actual defined name for the module labels.

I recommend not duplicating the 1st "AVG TOTAL" col ("B:B") in every
module, and use FreezePanes at col "C" in the row below the headers.

Give each student section as fully relative local scope defined name
that refs the judges' scores for each col.

Give the entire input area a fully absolute local scope defined name,
and include 1 extra row/col for adding more sections/modules. Format
the extra row/col to resemble a border within which to insert groups of
student sections or score items/modules.

Don't use garrish colors or excessive uppercase to minimize eyestrain
while working in the sheet.

You can have a look at how to implement everything I've mentioned here
in a sample rework of your project you can download from here...

https://app.box.com/s/23yqum8auvzx17h04u4f

This sample makes extensive use of defined names in formulas for easier
maintenance and understanding. Thus, no cell addresses appear in
formulas. The advantages of this concept should become apparent from
studying the formulas!

Only the headers row contains ConditionalFormatting. I retained shading
every even col within a module, but using a 'softer' color so my eyes
wouldn't hurt while working on this.

Your feedback is invited...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

My biggest thing is they get all the data with only there score not
all
the canidates scores and that it fits on one page and is readable and
not all mashed together. i would greatly appreciate any help in being
pointed in the right direction.

This will probably require VBA. My student grades manager app mentioned
in my other reply prints out reports for selected students or the
entire 'class', each on a separate sheet. The sheet also allows
filtering student data for a single student so the scores can be
viewed/discussed directly with the instructor without the other student
data viewable. Perhaps you can do similar!

During my reworking of your project it seemed likely that it would be
worth trying out in that app. The format is school-based in that it
returns PointAvg, letter grade, and percent avg based on a GradesTable.
I might try your project to see how it fits in. The obstacle as I see
it is going to be handling the 3 judge gradings, but 3 rows per student
will definitely work similar to my rework sample. I give it a whirl and
report back...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok.., normally my app uses 1 sheet per class/instructor meaning each
instructor would input scores for a single class. In your scenario each
judge would have their own sheet for the same group of students. I was,
however, able to set up on a single sheet similar to how I did in my
rework sample.

Each module shows Total Score and AVG only because each is 'weighted'
in the Summary module. For example, your "Kicks" module has 13 score
items and if each item's perfect score was 10 then the total score
possible is 130. If the 'weighted' value of "Kicks" is 10% of the
entire course then the total possible mark for "Kicks" is 13 for the
entire course. If your courses are structures as belt levels then you'd
use 1 sheet per class per belt level per instructor (judge).
Optionally, you could put all instructor/judge data on a single belt
level sheet in two ways:

1 row per instructor/judge per student
1 row per student using formula style input from each judge;
Example:
=8+9+8
OR
=AVERAGE(8,9,8)

...where, in all practicality, each judge would have a printout that
gets hand written inputs to be entered into the class sheet later on.
I'm inclined to go with the latter method so the score item perfect
score value doesn't need fudging with, and reports currently process a
student list as 1 row per student.

Ultimately, each module makes up a percentage of the entire course...

Creeds: 2*20=40; Weight=10% (4)
Physical Training: 5*20=100; Weight=5% (5)
Strikes&Punches: 5*10=50; Weight=10% (5)
Combinations: 5*25=125; Weight=10% (12.5)
Forms: 5*20=100; Weight=10% (10)
Weapons: 4*50=200; Weight=10% (20)
Kicks: 10*13=130; Weight=10% (13)
Sparring: (3*10)+(6*30)=210; Weight=10% (21)
Self Defense: 10*10=100; Weight=10% (10)
Board Breaks: (4*15)+(2*20)=100; Weight=10% (10)
Bonus Material: 3*100=300; Weight=5% (15)

...which represents 100% of the course (not factoring if the Attendance
module has a weight). That amounts to 1455 possible score. When
weighted as indicated, final average is based on (actual score)/(total
score) expressed as percent where total score evaluates to the sum of
the weighted values.

The final grade/ptAvg is defined in the GradesTable for given ranges of
final average. I guess that you need to have something that automates
printing reports, but my app does this per student by module items, per
module, and final score values/grades. Reports can be displayed in
PrintPreview, and are generated for selected students only OR the
entire class.

If interested, I can prep 2 class sheets that example the above and
post a link. Please advise...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok this seems like it posses some possible solutions but there is a reason why all students are grouped together. as the scores are entered the conditional formattig will reflect how they are doing in comparison to the group. as well as they will all be judged at the same time so the least ammount of navigation time for judges the better. i do like the idea of total average freez frame/cells to eliminate the replication of that part of the code. I will look over some of your other suggestions. thankyou for the input.

Thank you for lookin at that,, i would be interested in taking a look at it.

The only thing is the sum is not = to the whole total of parts, i.e some things may be worth more. like PT with only 5% it is closer to 15% there is a part of the grading system that is not accounted for in the sheet. When they have over a 9.0 they pass that segment with high marks and based on how many segments they get high marks on can change the result of there test / rank. also the test is a 3 test process that takes 6 month's between each test and over the course of the testing process they will need to have high marks or high passing grade in 85% of all catigorys atleast once. i havent made the sheet to accomidate more than one test at this point but may be looking to softwhere stronger than excel in the futre...... its just what i know so it will hafto do for a functioning prototype

Ok this seems like it posses some possible solutions but there is a
reason why all students are grouped together. as the scores are
entered
the conditional formattig will reflect how they are doing in
comparison
to the group. as well as they will all be judged at the same time so
the
least ammount of navigation time for judges the better. i do like the
idea of total average freez frame/cells to eliminate the replication
of
that part of the code. I will look over some of your other
suggestions.
thankyou for the input.

I didn't know the criteria for the CF and so is why I deliberately
didn't do any other than the headers row!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

So how do i print a summery for each cannidate?

'GS[_2_ Wrote:
;1620239'] Ok this seems like it posses some possible solutions but
there is a-
reason why all students are grouped together. as the scores are
entered
the conditional formattig will reflect how they are doing in
comparison
to the group. as well as they will all be judged at the same time
so the
least ammount of navigation time for judges the better. i do like
the idea of total average freez frame/cells to eliminate the
replication of
that part of the code. I will look over some of your other
suggestions.
thankyou for the input.-

I didn't know the criteria for the CF and so is why I deliberately
didn't do any other than the headers row!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

So how do i print a summery for each cannidate?

That's going to need VBA to automate as mentioned in my other post
about my student grades app. My routine is specific to the structure of
my class sheets and so writing code for your class sheets would be
different where named ranges are refs for data sources. I'm doing a
sample workbook now and I'll create a report for student "bob" and
leave the report sheet in the file (this is a temp wks used for
PrintPreview that gets deleted after printout). I'll put a link for you
to have a look...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok, I finished doing the various examples of doing your student grades
in my app. The file contains 6 class sheets and 1 sample report sheet
for "bob". Comments are included to explain each sheet's structure
where clarity is warranted. Otherwise, the formulas are
self-explanatory.

The app has its own menus/toolbar so you won't be able to use its
functionality while viewing it without using its portable
ReportGenerator addin.

Use the downlink posted earlier and look for "Yudanja1.1.xls" and
"MxpReports.zip".

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion