names

[Harold]

Hi I am doing a spreadsheet which covers a one week "snapshot of a
bowling leage" all the data is imported into a data sheet which
includes scratch games (totalling to series), Handicap games (totalling
to series)

the first colum is the bowlers name followed by fixed (non formlated)
stats such as starting average, no of games, etc which are used for
reference.

the next colums are the scratch scores followed by the hadicap scores

I then have another worksheet which is a presentation sheet this is the
sheet I print. it contains the top 5 male and female scores, the
bottom 5 male and female scores and other data

the problem I have is that I want to list the top 5 male ( I am pretty
sure I can do that) but I want the bowlers's name to transfer to the
sheet automatically.

I have tried to use Vlookup but it's only use is to read a fixed table.
the same bowler is not going to win the event ever week nor can I a-z
the data sheet as it would kill every other use of it.

is there a function that allows the data (name) to transfer with the
top score. ie if peter gets the highest score, large function will put
his score in the right place on the report and his name in the name
field next to it

thankyou in advance

 

[Bob Phillips]

Assuming that the scores are in column E on sheet1 say, and names on column
A, try using

B1: =LARGE(Sheet1!$E$2:$E$50,1)
A1: =INDEX(Sheet1!$A$2:$A$50,MATCH(B1,Sheet1!$E$2:$E$50))

to get the name and score of top player, and in row 2, use LARGE(..., 2)

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
in addition to Bob's answer you may also take a look at:
http://www.cpearson.com/excel/rank.htm

 

[Harold]

Hi
Thanks for your help so far I have no idea where you learn this stuff.


Bobs soultion has two bugs

if you use it on one row of the three rows of data it comes up with th
last name on the list regardless of the score gained

if you use it with all three rows of data it comes up with na#

I have enclosed a very cut down version of the sheet to explain




thankyou for your help

Pau

Attachment filename: book2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=56566