Creating a Scoresheet

Should be an easy fix for experts, but I'm not one of those...

I'm trying to create a "scoresheet" in Excel where a racer has a name and an ID number on the main tally worksheet, with columns relating to their scores for each race. Subsequent worksheets have results from each race (one race per sheet), with the individual referenced by their ID number.

How do I have the main tally sheet "find" that person's score for each event in separate columns so that it can add up their totals? I tried an IF formula to find each score in the subsequent sheets, but that didn't work.

Is there a simple formula to do this? I've been achieving the results by importing the worksheets into a Filemaker database (where the ID number "matches" and updates each score), but that's a pain, and I'd rather do it all in Excel.

Thanks,

Mark

See workbook attached

Thanks Steve and JBeaucaire,

I think I've basically got it now. Just two more essentials to fix and I think it will work perfectly:

The formula I have for each race result is:
=VLOOKUP($D3,Race1!$B$1:$C$61,2,FALSE)

1) If a race doesn't have someone's particular ID number, it returns the cell with a "#N/A." I need it to return me a "0" (a zero) number. Otherwise, the subsequent calculations all give me a #N/A.

2) I need to find the two LOWEST scores and subtract those from the subtotal. I know how to find the single lowest score (=MIN(E3:J3)), but how do I find the SECOND lowest score in the row so I can subtract that as well?

Thanks so much! This really helps me out a lot. My attempt is attached.

Mark

DonkeyOte,

Thanks so much for these. Only one problem is that the assumption you made isn't quite correct -- we actually have to NOT ignore any 0's in finding the minimum values. Basically, the athlete is allowed to "drop" two races, whether they do poorly or they miss because of illness, etc. If they miss a race, they get a zero, so they are allowed to drop that and it becomes one of the minimums. So we would have to adjust that formula.

Secondly, the formula you gave for the lookup is really complex to me, so, as always with these things, it would be great (if you didn't mind) if you could give me a quick overview English explanation as to what it's doing. That always helps me when I try to figure things out on my own. The original formula for the lookup from SteveR made some sense to me (as a non-expert) but the one in your sheet was way beyond my capability to understand it.

=VLOOKUP($D3,Race1!$B$1:$C$150,2,FALSE)
vs.
=LOOKUP(9.999999999999E+307,CHOOSE({1,2},0,VLOOKUP($D3,INDIRECT("'"&E$2&"'!$B$1:$C$150"),2,FALSE)))

Or maybe an English explanation for this stuff is irrelevant, as I'll never completely understand it anyway

Thanks for the info? I will try to digest all of that. To answer the specific question:
The athletes (in this case) are allowed to drop 2 races out of the 6. Whether they score poorly or don't show up doesn't matter, so yes, if there are two zero's, they are allowed to drop both zeros.

Thanks!

Mark

OK, now I'm confused as to which you think is the better formula to use. It's starting to look all Greek to me!

=LOOKUP(9.999999999999E+307,CHOOSE({1,2},0,VLOOKUP($D3,INDIRECT("'"&E$2&"'!$B$1:$C$150"),2,FALSE)))

or

=IF(ISNA(MATCH($D3,INDIRECT("'"&E$2&"'!B1:B150"),0)),0,VLOOKUP($D3,INDIRECT("'"&E$2&"'!B1:C150"),2,FALSE))

You say that you prefer the "lookup" method, but to me they both look like they are doing a "lookup." Remember, I have no idea how these formulas work -- it's like magic.

Thanks,

Mark

Thanks very much. That seems to work great. I have no idea how, of course, but for now, it's all good.

Mark