Custom league table in Excel

slipd · 17 Mar 2011 at 08:45

Hi guys,

I'm trying to make a custom (in terms of points) league table in excel…

I need it to work thus:

If bob wins his match at venue1 he scores 20 points for coming first, 19 if he’s 2nd, 18 if he's 3rd and so on. The total would display in the totals column… I'm okay with that part, what I'm struggling is the formula needed get to that point

I'm pretty sure I need to do some kind of lookup function but it's been ages since I've used excel (properly) and I can’t for the life of me remember how you do it!

Cheers

Participant · 17 Mar 2011 at 09:26

=VLOOKUP(C4,$E$13:$F$100,2,false)+VLOOKUP(D4,$E$13:$F$100,2,false)...

fullmetaltony · 17 Mar 2011 at 09:59

Robbie G said:
=VLOOKUP(C4,$E$13:$F$100,2,false)+VLOOKUP(D4,$E$13:$F$100,2,false)...

This

You only need do it for the first cell in the totals column too, as the way Robbies presented it you can cut paste it to the rest.

slipd · 17 Mar 2011 at 13:04

Thanks guys

Tomsk · 17 Mar 2011 at 17:01

You could try:

Code:

COUNT(B2:D2)*21-SUM(B2:D2)

slipd · 18 Mar 2011 at 16:30

Right, now I'm stuck on a position/place update :rolleyes:

Ideally, I'd like to be able to enter the position entrants came in the venue column, their points to be automatically calculated (which they are - thanks!) in the total column and then another column (position maybe?) to display their current position... won't I need a macro for that; the table would then automatically update when you add new results etc?

Cheers

Participant · 18 Mar 2011 at 16:36

=RANK(F4,$F$4:$F$10)

fullmetaltony · 18 Mar 2011 at 16:41

Robbie G said:
=RANK(F4,$F$4:$F$10)

Nice, learnt a new function today

slipd · 18 Mar 2011 at 16:52

Robbie G said:
=RANK(F4,$F$4:$F$10)

Diamond!

Thanks mate

slipd · 18 Mar 2011 at 17:37

Is there any way off being able to update the table so the person in first place would be at the top, the person in last, the bottom etc?

Tomsk · 18 Mar 2011 at 18:40

The simplest way is to record a macro and assign a keyboard shortcut.

slipd · 26 Mar 2011 at 11:21

My league table's looking pretty good but I want to add a bit of security; I don't want the user to be able to access the formulae so I've locked the 2 formula columns off - thing is this is causing a problem for the macro (button) I've created, it just won't run when I protect the sheet

Any ideas or workrounds?

Cheers

Tomsk · 26 Mar 2011 at 13:10

Add these lines at the start end of of your macro.

worksheets("name_of_worksheet").unprotect("password")
It seems the forum automatically inserts a space after 50 characters if there's been none before!

worksheets("name_of_worksheet").protect("password")

slipd · 26 Mar 2011 at 13:20

Tomsk said:
Add these lines at the start end of of your macro.

worksheets("name_of_worksheet").unprotect("password")
It seems the forum automatically inserts a space after 50 characters if there's been none before!

worksheets("name_of_worksheet").protect("password")

At the start of the macro, yeah? Thanks mate!

Tomsk · 26 Mar 2011 at 13:21

One line at the start and the other at the end of the macro. I think it's fairly obvious what they do.