This is what I have. I have arrived at this with scores taken from 5 score
sheets per competitor, the top and lowest scores (each) have been discarded
and what I am left with is the three middle scores calculated on this results
page, (calculation formulas etc are hidden columns because this page is to be
printed off and I dont want the individual judges scores seen). The 3 are
averaged to give a winner, 2nd,3rd,4th, & 5th placing.

What I would like to do is for this list to automatically sort itself into
the correct running order and place a (1st), (2nd ), 3rd) etc before or after
the placings achieved. I can manually sort it by selecting a row number,
data, sort etc but it needs to be automatic. I have fumbled around trying
large, index, if etc, and cannot get it to work. Everytime the list sorts
itself it leaves some behind or automatically changes the formula to suit
itself ! Can you please help. I need to put this to bed today !

Many thanks.

COL H COL I COL J
CLIMBERS SCORE TIME
ROW7 RUPERT WILKINSON 41.67 27.40
ROW8 LEE TOULSON 46.00 20.91
ROW9 GAVIN RODDERS 13.33 26.15
ROW10 JONTHON TURNBULL 10.00 24.01
ROW11 SAM ROBINSON 6.67 23.73

Try in K7 (?)
=rank(I7,$I$7:$I$11) and copy down to K11
Note you need to decide whether Ties are allowable. and add a tie breaker
somewhere if not
in L7 enter "Winner"
in L8 enter "Second" (note if ties are allowed you may have to play with
this one)
maybe =if(countif(I7:I11,1)1,"Winner","Second"
Do similar things for L9-L11
In M7 enter
=index(H$7:H$11,match(Row()-6,$K$7:$K$11,0))
Copy to O11
Add your labels to M6:O6 and print L6:O11

"SYBS" wrote:

This is what I have. I have arrived at this with scores taken from 5 score
sheets per competitor, the top and lowest scores (each) have been discarded
and what I am left with is the three middle scores calculated on this results
page, (calculation formulas etc are hidden columns because this page is to be
printed off and I dont want the individual judges scores seen). The 3 are
averaged to give a winner, 2nd,3rd,4th, & 5th placing.

What I would like to do is for this list to automatically sort itself into
the correct running order and place a (1st), (2nd ), 3rd) etc before or after
the placings achieved. I can manually sort it by selecting a row number,
data, sort etc but it needs to be automatic. I have fumbled around trying
large, index, if etc, and cannot get it to work. Everytime the list sorts
itself it leaves some behind or automatically changes the formula to suit
itself ! Can you please help. I need to put this to bed today !

Many thanks.

COL H COL I COL J
CLIMBERS SCORE TIME
ROW7 RUPERT WILKINSON 41.67 27.40
ROW8 LEE TOULSON 46.00 20.91
ROW9 GAVIN RODDERS 13.33 26.15
ROW10 JONTHON TURNBULL 10.00 24.01
ROW11 SAM ROBINSON 6.67 23.73

Thanks , I will have a go and see what I get.

sybs

"bj" wrote:

Try in K7 (?)
=rank(I7,$I$7:$I$11) and copy down to K11
Note you need to decide whether Ties are allowable. and add a tie breaker
somewhere if not
in L7 enter "Winner"
in L8 enter "Second" (note if ties are allowed you may have to play with
this one)
maybe =if(countif(I7:I11,1)1,"Winner","Second"
Do similar things for L9-L11
In M7 enter
=index(H$7:H$11,match(Row()-6,$K$7:$K$11,0))
Copy to O11
Add your labels to M6:O6 and print L6:O11

"SYBS" wrote:

This is what I have. I have arrived at this with scores taken from 5 score
sheets per competitor, the top and lowest scores (each) have been discarded
and what I am left with is the three middle scores calculated on this results
page, (calculation formulas etc are hidden columns because this page is to be
printed off and I dont want the individual judges scores seen). The 3 are
averaged to give a winner, 2nd,3rd,4th, & 5th placing.

What I would like to do is for this list to automatically sort itself into
the correct running order and place a (1st), (2nd ), 3rd) etc before or after
the placings achieved. I can manually sort it by selecting a row number,
data, sort etc but it needs to be automatic. I have fumbled around trying
large, index, if etc, and cannot get it to work. Everytime the list sorts
itself it leaves some behind or automatically changes the formula to suit
itself ! Can you please help. I need to put this to bed today !

Many thanks.

COL H COL I COL J
CLIMBERS SCORE TIME
ROW7 RUPERT WILKINSON 41.67 27.40
ROW8 LEE TOULSON 46.00 20.91
ROW9 GAVIN RODDERS 13.33 26.15
ROW10 JONTHON TURNBULL 10.00 24.01
ROW11 SAM ROBINSON 6.67 23.73

Brilliant - thanks a load works perfectly and now I even understand how it
works ! Thanks again. Next question, any ideas on how to make the sheet
tabs (5 of them, one for each finalist) show the name of the finalist instead
of the present ref. Each finalist would be on a different sheet and each
finalist name is put on that sheet from another sheet (phew) which is called
Set Up . The cells each contain a different name, B9,B10,B11,B12,B13
(filled in when we know who the finalist are) and as they are filled in they
transpose to the relevant "Climbers Sheet" Presentley Climber 1 (Sheet)
relates to B9, Climber 2 (Sheet) relates to B10 and so on. Would be
grateful if you could further help here. If not, thanks anyway for other
help.

"SYBS" wrote:

This is what I have. I have arrived at this with scores taken from 5 score
sheets per competitor, the top and lowest scores (each) have been discarded
and what I am left with is the three middle scores calculated on this results
page, (calculation formulas etc are hidden columns because this page is to be
printed off and I dont want the individual judges scores seen). The 3 are
averaged to give a winner, 2nd,3rd,4th, & 5th placing.

What I would like to do is for this list to automatically sort itself into
the correct running order and place a (1st), (2nd ), 3rd) etc before or after
the placings achieved. I can manually sort it by selecting a row number,
data, sort etc but it needs to be automatic. I have fumbled around trying
large, index, if etc, and cannot get it to work. Everytime the list sorts
itself it leaves some behind or automatically changes the formula to suit
itself ! Can you please help. I need to put this to bed today !

Many thanks.

COL H COL I COL J
CLIMBERS SCORE TIME
ROW7 RUPERT WILKINSON 41.67 27.40
ROW8 LEE TOULSON 46.00 20.91
ROW9 GAVIN RODDERS 13.33 26.15
ROW10 JONTHON TURNBULL 10.00 24.01
ROW11 SAM ROBINSON 6.67 23.73

A quick macro will do it
assuming that your main worksheet is the furthest to the left on the
worksheet names and that the others are in order to the right.
a quick and dirty macrop to remane the sheets is

Sub shnm()
For i = 2 To 6
Sheets(i).Name = Sheets(1).Cells(i + 7, 2)
Next i
End Sub


"SYBS" wrote:

Brilliant - thanks a load works perfectly and now I even understand how it
works ! Thanks again. Next question, any ideas on how to make the sheet
tabs (5 of them, one for each finalist) show the name of the finalist instead
of the present ref. Each finalist would be on a different sheet and each
finalist name is put on that sheet from another sheet (phew) which is called
Set Up . The cells each contain a different name, B9,B10,B11,B12,B13
(filled in when we know who the finalist are) and as they are filled in they
transpose to the relevant "Climbers Sheet" Presentley Climber 1 (Sheet)
relates to B9, Climber 2 (Sheet) relates to B10 and so on. Would be
grateful if you could further help here. If not, thanks anyway for other
help.

"SYBS" wrote:

This is what I have. I have arrived at this with scores taken from 5 score
sheets per competitor, the top and lowest scores (each) have been discarded
and what I am left with is the three middle scores calculated on this results
page, (calculation formulas etc are hidden columns because this page is to be
printed off and I dont want the individual judges scores seen). The 3 are
averaged to give a winner, 2nd,3rd,4th, & 5th placing.

What I would like to do is for this list to automatically sort itself into
the correct running order and place a (1st), (2nd ), 3rd) etc before or after
the placings achieved. I can manually sort it by selecting a row number,
data, sort etc but it needs to be automatic. I have fumbled around trying
large, index, if etc, and cannot get it to work. Everytime the list sorts
itself it leaves some behind or automatically changes the formula to suit
itself ! Can you please help. I need to put this to bed today !

Many thanks.

COL H COL I COL J
CLIMBERS SCORE TIME
ROW7 RUPERT WILKINSON 41.67 27.40
ROW8 LEE TOULSON 46.00 20.91
ROW9 GAVIN RODDERS 13.33 26.15
ROW10 JONTHON TURNBULL 10.00 24.01
ROW11 SAM ROBINSON 6.67 23.73