formula to move a scored number to a score sheet

[kman]

i run a race team. we have 20 teams competeing. we select 7 riders each to
compete in various age and skill levels. we sort the riders by age and skill
level. as we score the races, i need a formula that will send the results
back to the team score card. for example i have 20 score card, one for each
team. each card has 3colums with 7 cells. after we sort the riders they are
no longer grouped by teams. the formula would have to know to send the score
of a rider from team (a) to the correct score card and if the first cell has
a score in it, to place it in the next cell in the column and so on until all
seven riders of each team have their scores end up in the proper team score
card. maybe this can not be done, but i have to ask. thank you

 

[AltaEgo]

First, let's see if I have the concept right:

You need to enter race results into your worksheet and have the race score
appear against relevant riders in the team section of the worksheet which
currently consists of an area of cells (60 columns by 7 or 3 columns by
210).

Let's start with layout.

TEAM DETAILS

Excel works best with data if they are laid out as a flat file database. So,
your team details would best be laid out Team, Name, age, Points,
Whatever...
You don't need to organise these into team blocks. You can filter or sort
to see who belongs to which team or do a temporary sort or even use these
data as a lookup table for your neatly arranged team blocks in other areas
or your spreadsheet.

RACE RESULTS AREA

Rider Name, Race Number, Place or points

(NB data validation will ensure no spelling problems
http://www.contextures.com/xlDataVal01.html)

Getting to the answer to your question:

The easier method for getting results into table:

Organise your races and enter results by Rider.

To transfer result to your teams table, use vLookup:

http://www.contextures.com/xlFunctions02.html

NOTE CAREFULLY the need to have data in order in the lookup data area (you
can sort in order any time), .

Index and Match is more flexible than vlookup but is slightly more complex.
One advantage of Index and Match is you don't need your rider name to be
first in the results data. Transfer your results to your teams table using
Index and Match:

http://www.contextures.com/xlFunctions03.html


The basic layout above would work once per meet for each rider. So, if this
is a single meet with one race per rider on the worksheet - no problems.

More than one rider called Tom Brown? You will need a method to work out who
is who. My preference would be a unique number for each rider (RiderID) but
you might use something different (Thomas Brown, Tom Brown; Tom A Brown).


NOTE all of the above are based on my beliefs about your needs. If you need
something different, your problems WILL be solvable. Depending what you
need, some problems may need to be solved by programming. Don't be daunted
by this. There are many who are all too willing to help solve such problems.
Just keep asking questions.