Excel linked sheets

[mark0000]

Hi all,
I have several sheets named 'maze 1' through 'maze 8'. These sheets are results from a computer race, in which 6 people race through a timed computer maze. What I am trying to make is a final results sheet that will add up scores and times.

The 'maze' sheets look like this:

1 2 3

A 1 [ ] 6
B 2 [ ] 5
C 3 [ ] 4
D 4 [ ] 3
E 5 [ ] 2
F 6 [ ] 1

column 1 is Finish Order, column 2 will be dynamic data, and column 3 is points.


The 'Final results' page looks like this:

1 2 3 4 5

maze 1 maze 2 maze 3 maze 4
A Jim
B Tony
C Mike
D Bob
E Jerry
F Jack

Finishing in first place gets you 6 points, 2nd place gets 5 points, etc.

For example, let's say Jerry wins the first maze race.

If I type in 'Jerry' in cell A:2 in sheet 'maze 1' how do I get excel to return a value of 6 in cell E:2 in sheet 'Final Results'? Is there a way to get a cell to convert from Alpha to numeric?

Please help!
Thanks in advance.

 

[Nareik]

The =SUMPRODUCT() command should work here.

Something like this will work in E2
=SUMPRODUCT(INT(E1='Maze 1'!$A$2:$F$2), 'Maze 1'!$A$3:$F$3)

This formula should work for the whole of column E, you will need to update the 'maze 1' refernce for the other columns. It is possible to do this automatically using the INDIRECT command, however from your example there is not heading row of Maze 1, Maze 2, etc as the summary data is show as starting at row 1.

Kieran