index/large..help please

[Terry]

Hi Group

Xp Pro
M/S Office 2003

I have a single worksheet where I keep records of a bowls league.

Headings for the columns(row 3) are:
Team, Played, Won, Drawn, Lost, +/-, Shot diff', Total points.

A4:A10 are the full team names.
H4:H10 are the Total points.

From above info' is it possible to "automate" this table, when I enter one
or more results?

I have tried the functions "large" and "index", but unable to get the TEAM
NAMES(resulting in "0"), yet I was able to do the Total points column ok!!

Currently I manually enter my results as they are available, then I use
DATA/SORT to give me the desired table.(highest & lowest scores).

TIA

Terry

 

[MartinW]

Hi Terry,

Try this, you may need to change the ranges.

Put this in I4 and drag down to I10
=H4-ROW()/10^10

Put this in K4 and drag down to K10
=INDEX($A$4:$A$10,(MATCH(LARGE($I$4:$I$10,ROW()-3),$I$4:$I$10,0)))

Put this in L4 and drag down to L10
=LARGE($H$4:$H$10,ROW()-3)

The formula in column I is a helper column and can be hidden.

The other formulae can be moved but you will need to adjust
the 3 to be one less than the row it resides in.

HTH
Martin

 

[Terry]

Thank you kindly Martin.

I am trying to work it into my spreadsheet.
May I ask what should be in blank "J" column, as you have no mention of it,
but include: "I", "K" and "L" columns.

Regards
Terry

 

[MartinW]

Hi Terry,

Your welcome Terry,
The blank column is just a habit of mine. I like keeping
things separated for the visual look, it makes for less
clutter.

Regards
Martin

 

[Terry]

OK Martin, but I am only a novice senior citizen!!...hence the questions.

I will report to group as I complete my spreadsheet to my desire.

Terry

 

[Terry]

Hi
I may have misslead here, because I would like the corresponding columns(in
my first post), to move with the Total points, and Team names, if possible?

Terry

 

[MartinW]

OK, we must be at cross purposes here. Probably the
easiest way to sort it out is for you to send me a
copy of your sheet, then I can see what's going on.
Send it to (e-mail address removed)
Please add an explanation of precisely what you want

Also please leave a post here when you have sent it.
I get a notification when watched messages are responded
to here whereas I would need to keep checking Hotmail
on the off chance.

Regards
Martin

 

[TERRY]

Thank you Martin.....an email sent to you with the table as requested.
..
Terry

 

[MartinW]

Hi Terry,

Responded to your Hotmail addy

Regards
Martin

 

[Terry]

Cheers Martin.
Terry

Hi Terry,

Responded to your Hotmail addy

Regards
Martin