conditional formatting to highlight top score

[eddy_eagle]

I'm a newbie to this list so please forgive if already covered (though a
search didn't find it).

I have a list of top scorers in the premiership who's goals I update
weekly. i want the table to show who is the top scorer this week by
highlighting his name and score (just score would do). I then want to
show the second and third top scoresrs.

Ideally I would also like other seperate cells filled with the top
scorer's name and price (also listed in orginal table) so that I
effectively automatically build the week's top scoring team beside the
original table.

I have so far tried using =LARGE(T4:T63,1) in the conditional
formatting tool and it picks up and highlights the top scorer. However,
for some reason it also highlights several others way down the list).

I haven't been able to build the seperate player list yet.

Any help much appreciated.

Ed

 

[Norman Harker]

Hi Ed!

Probably you need to make the cell reference absolute and not
relative:

Use:
=LARGE($T$4:$T$63,1)


If you look at you "false" formats you'll see that copying the
conditional format has changed the reference away from T4:T63

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob Phillips]

Eddy,

Use a formula oaf

=$T4=LARGE($T$4:$T$63,1)

otherwise the formula adjusts as you move down the list so another will meet
the criteria at some point.

For 2n and 3rd just add extra conditions of

=$T4=LARGE($T$4:$T$63,2)
=$T4=LARGE($T$4:$T$63,3)

Put this formula in the names an d scores.

To get the names, assuming they are in column S, trys something like

=INDEX($S$4:$S$63,MATCH(LARGE($T$4:$T$63,1) ,$T$4:$T$63,0))

--

HTH

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