Cross country spreedsheet

  • Thread starter Thread starter will gerrish
  • Start date Start date
W

will gerrish

Hi All,
bit of a strange request but im seeing if anyone's ever done anything like
this or seen something like it on the internet?

Basically, I need a spread sheet that I can input the runners number, name
and school beforehand and then as they run through the finish line, I just
need to put their race number in and the order of their position and their
name and school will automatically come up.

has anyone seen anything like this? or got any ideas of how to go about it?
any help would be great.
thanks
Will
 
So if I understand you the idea is to put all of the participants into one
master sheet at the start prior to the race. This master would include the
name, school and number.

At the end of the race or as it is finishing up you woul like to type in the
runner number and their position and it will automatically bring up their
name and school.

Assuming that to be the case do a bit of reasearch on the VLookup function.
It should do what you are asking...
 
Does each (unique) competitor have a unique competitor number?

I need a yes or no answer please.

Thanks.
 
So, assume you have a table on Sheet1 with Runner numbers in col A, names in
B and School in C

in your results sheet, you have Race # in col A of results, Position in B.

In cell C2, =VLOOKUP(A2,Sheet1!A:B,2,0)

will return the runner's name

In cell D2, =VLOOKUP(A2,Sheet1!A:C,3,0)

will return their number.
 
Hi Will, I have just uploaded a (EXCEL 2007) file to:-

www.pierrefondes.com

- first file at the top of the home page.

1. Look at the Assumptions Worksheet first.

Here you will see an imaginary list of races into which various runners have
been entered.

2. If you then look at the Results Worhsheet this is where I have entered
the results of the various races. (I have done races 100 to 500 for you
inclusive).

It's very rough & very quick but I think it gives you what you want.

It needs a fair amount of tidying up but time prevents me from doing that.

I have checked all the results for races 100 to 500 inclusive and they do
echo back with the right information (runner name, runner number and runner
school).

Please hit Yes if my comments have hekped you.

Thanks!
 
Hi,

this is excellent thank you.

im wondering if you can help me with a few tweaks in it? i tidy it up so
that now we have only a Competitor and Result sheet and there is only 1 race.

Competitor sheet is formatted like so

A B C
1 CompetitorNumber SchoolNumber Name
2 1 School1 Bob
3 2 School2 Jim

and so on with 50 competitors

Results Sheet is formatted like so


A B C
D E F
1 Race Number & Positions Time RunnerName RunnerNumber RunnerSchool
2 Cross Country
3 1st #N/A 0
#N/A
4 2nd #N/A 0
#N/A
5 3rd #N/A 0
#N/A
6 4th #N/A 0
#N/A
7 5th #N/A 0
#N/A
8 6th #N/A 0
#N/A


D3 has the VLOOKUP of =VLOOKUP(B3,competitors,3,FALSE)
F3 has the VLOOKUP of =VLOOKUP(B3,competitors,2,FALSE)

whats does the change of 3 and 2 in the vlookups do?

also for some reason i cant seem to get the vlookup to look further than the
orginal 20 lines you created and push further down on to 50 lines.

i can email you the amended spreadsheet if you like?

thanks again for your help.

will
 
Hi Will, I am sitting at my desk right now (about 1830 London time Tues Sep
29/9) and will have a look at your comments over the next 90 minutes or so as
I have that time spare.

I will see if I can come up with anything.

Sorry my first Workbook was so scrappy; I was working very quickly and if I
had had more time I could have come up with a much better / cleaner solution
for you.

Steve
 
Will:-

In answer to this part:-

whats does the change of 3 and 2 in the vlookups do?

It changes the columns in the table that it is looking at.

In the example I uploaded:-

Results Worksheet / Cell I 3 has Smith in it / the 3 in the formula here
refers to the 3rd column of the competitors, "table" / so EXCEL is looking at
cell D19 which is where it gets Smith from. Note that Smith is in the 3rd
column of the competitors table. The competitors table is the Results
Worksheet cells B12 to D 31 inclusive (I gave this a range name to make
things easier).

I hope this answers your question.

Steve
 
Will:-

Have just replied to this:-

also for some reason i cant seem to get the vlookup to look further than the
orginal 20 lines you created and push further down on to 50 lines.
Click to expand...

- but got message saying site was too busy to post so my psoting may have
got lost (will repeat it if it did).

Steve
 
Will in answer to this part:-

also for some reason i cant seem to get the vlookup to look further than the
orginal 20 lines you created and push further down on to 50 lines.
Click to expand...

1. This is probably because I crated a Range Name in the Worksheet called
Results at cells B12 to D 31 inclusive.

This Range Name was called:-

competitors

2. You either need to extend the Range Name (called competitors) or create a
new Range Name.

3. To create a new Range Name:-

- highlight the cells that you wish to create the Range Name for (for
example cells A 1 to C 10 inclusive

- in the Name Box (the white area just beneath Home / Clipboard) enter the
name of the Range (for example smith)

- hit enter

4. The Range Name:-

smith

- will now have been created.

5. To check that it has been created hit the F5 key and the name:-

smith

- should appear there (if the Range Name has been created properly).

6. Select:-

smith

(in the Go To box) / hit OK / and EXCEL should highlight cells A 1 to C 10
inclusive

So the above should answer your 20 / 50 lines problem.

Steve
 
Hi Will, let me have that file ([email protected]) and I will check it
out for you.

I am signing off for today at just before 2000 London time / 1500 New York
time.

I should be able to spend about an hour or so working on your file tomorrow
Wed Sep 30/9.

Steve
 
Back
Top