Help with looking up test score on another sheet

[kimberly13]

I am making a Physical Fitness Test scoring workbook book for my Young Marine Program, but I am haing problems with the "lookup" part of it.

Each Child have thier own sheet with the infor across the top

Name - Age - #of sit up - Score - # of Push ups - Score - etc . . . .

On another sheet I have the scores - which you find by cross referencing age and the going down to the number of sit ups or other event.

I have tried several "lookup" type formulas with no luck.

I then have the Scores charted so the kids can see how they improve with each Fitness test.

any suggestion?????

 

[Dave Patton]

I am not sure of your question.

If you require a total by age and activity, consider the following
1) organize the sheets by Age =sum(Joe:Bill!G2)

or
2) insert 2 blank sheets and name them Start and End
put sheet Start before relevant sheets for people
put sheet End after sheets for people

On Summary Sheet with Age in say A2 and using a function from the free Add-in Morefunc (see Add-in information at top of message list).

=SUMPRODUCT((THREED(Start:End!$B$2)=A2)+0,(THREED(Start:End!$G$2)))

This sums G2 for the people with ages specified in cell B2 that equal the criteria shown in A2.

If this helps, please advise. If you require the last number in a particular column, you could check a thread from yesterday. Last in multiple Tabs.

You could probably keep the spreadsheet simple if you determine the data that you need and assemble it in a common area on each sheet.

With the above example, G2 could show the last number of situps for that person if that is relevant.


.

 

[Dave Patton]

with statistical information in Row 2

Summary sheets shows names in a range such as A7 to Ax
Ages could be in C7 to Cx

formula like =LOOKUP(BigNum,INDIRECT("'"&A7&"'"&"!g1:g1000"))

would give last number for person in A7 with info in Column G

for information for all people aged say 9

use sumif on information recapped above

=SUMIF(C7:C9,9,E7:E9)

revise references and ranges as necessary

 

[kimberly13]

ok, let me see if I can bee a little clearer.

Each Child have thier own sheet with the infor across the top

Name - Age - #of sit up - Score - # of Push ups - Score - etc . . . .

Jimmy - 11 - 22 - ???? - 25 -???? etc.....


On one score lookup page I have

Ages across the top and number reps down the side

11 - 12 - 13 - 14 - 15
1_______55_____54_____48____47_____44____
2_______56_____55_____53____52_____46____
3_______57_____56_____50____55_____48____
4_______58_____57_____51____58_____49____
5_______59_____58_____52____59_____51____


I Need a formual in the "score" cell to take Jimmy age and find that the row "A" and then find the number of reps in col 'A" and give me the score in the cell where they meet.

Is that a better explaination?? I hope!!

I will be also looking at the other thing suggested[/u]

 

[Aladin Akyurek]

You seem to have a lookup table. But it's unclear to me what must be looked up from the lookup table. One lookup value is age, but what is the other - # of sit ups maybe?

 

[kimberly13]

Yes age is the first item to be looked up, then number of sit ups. and Where the two meet on the table (B5= 59) would be the points it is worth (in bold)

11 - 12 - 13 - 14 - 15
1_______55_____54_____48____47_____44____
2_______56_____55_____53____52_____46____
3_______57_____56_____50____55_____48____
4_______58_____57_____51____58_____49____
5_______59_____58_____52____59_____51____

 

[Aladin Akyurek]

Yes age is the first item to be looked up, then number of sit ups. and Where the two meet on the table (B5= 59) would be the points it is worth (in bold)

11 - 12 - 13 - 14 - 15
1_______55_____54_____48____47_____44____
2_______56_____55_____53____52_____46____
3_______57_____56_____50____55_____48____
4_______58_____57_____51____58_____49____
5_______59_____58_____52____59_____51____

kimberly13.xls
	A	B	C	D	E	F	G
6	Name	Age	SitUps	Score	PushUps	Score
7	Jimmy	11	5	59	4	58
8
9
10
11
12		11	12	13	14	15
13	1	55	54	48	47	44
14	2	56	55	53	52	46
15	3	57	56	50	55	48
16	4	58	57	51	58	49
17	5	59	58	52	59	51
18
Sheet1

Formulas...

D2:

=VLOOKUP(C7,$A$13:$F$17,MATCH(B7,$A$12:$F$12,0),0)

F2:

=VLOOKUP(E7,$A$13:$F$17,MATCH(B7,$A$12:$F$12,0),0)

You might need to change 0's (for exact match) to 1's (for approximate match).

 

[kimberly13]

RESOLVED-- Help with looking up test score on another sheet

THANK YOU SO MUCH!!!!! You are a life/time saver. I have 50 some kids to do this for each month and it was taking me FOREVER!!!

I really appreciate your help!!!

 

[kimberly13]

NOT RESOLVEd!! looking up test score on another sheet

OK I tried what was given to me and it didnt work???????? Are you sure it isnt still MONDAY???? What are the $ signs in the formula for??

 

[Aladin Akyurek]

Re: NOT RESOLVEd!! looking up test score on another sheet

OK I tried what was given to me and it didnt work???????? Are you sure it isnt still MONDAY???? What are the $ signs in the formula for??

$A$13:$F$17 is the lookup table which you need to lock when you copy the VLOOKUP formula to other cells. The same holds for $A$12:$F$12 which is a lookup vector.