League table

[Victorthesecond]

Hi all. I wonder if someone can advise me?

I'm setting up a football prediction league for a small number of friends - nothing major and all for fun!

So, the idea is that "members" will predict the outcome of matches involving a single team that we all follow. Before each game, they'll be asked to predict:

- Final score
- First goalscorer (if any!)
- Crowd attendance

Each correct answer will be worth 1 point, with annother 1 point if the crowd attendance is within 1,000 of actual and 2 points for the person who is nearest to the actual attendance.

In the league we play in (Championship), there are 24 teams, so there will be 46 games for each (ie 23 home and 23 away).

I'm intending to set up 46 worksheets in a single workbook - this will allow people's predictions to be entered each week. In each sheet, I propose to enter each person's points score for that week. That's the easy part!

I then intend to add another worksheet that will automatically "grab" the points, per person, from each of the 46 worksheets, and add those up to create an ongoing total. I think I can use "paste link" to do this and then add a simple formula to keep the totals.

Where I hope you can help is to advise me on how I can then use that combined data to create a league table, which will constantly change as and when peoples points are added. In other words, it becomes a dynamic table that reveals everyone's placing at the end of each game.

Hope you can advise, and look forward to hearing from you!

[Bernie Deitrick]

First, set up your game template, so that all your information will be in the same place on each sheet, which will allow you to extract the data relatively easily. Name the 46 worksheets in a predictable manner, also to allow data extraction - Week1 Week2 etc.

Then create a data consolidation sheet - named "Points". Put descriptive headers in Row 2, the sheet names down column A starting in A3, and the cell addresses of the values you want into row 1, starting in Column B. For example, say that column B will be for Victor's points for Final Score, which is in cell H8 on each sheet. So put H8 into cell B1.

Then, once you have your headers, addresses, and sheet names set up, us this formula in cell B3

=INDIRECT("'" & $A3 & "'!" & B$1)

and copy that down to match your list of sheet names, then across to match your headers in row 2.

At the bottom of each column - in B49 - use a SUM formula:

=SUM(B3:B48)

Then use another SUM formula to get Victor's overall score - say, B50 - but add a small tie-breaking value like this:

=SUM(B49:D49) + COLUMN(B49)/1000000

Finally, create a column of all the player's names - say, on another sheet in column A, starting in A2 - and create links to their scores starting in B2-

Victor =Points!B50
Fred =Points!E50

etc.

Then, create your dynamic table with this formulas to get the names - say, in D2:

=INDEX(A:A,MATCH(LARGE(B:B,ROW(A1)),B:B,FALSE))

and this formula to get the points in E2:

=LARGE(B:B,ROW(A1))

Then copy D2:E2 down for as many players as you have. And you're done.

[Victorthesecond]

HI Bernie, thanks for that. I was hoping I might be able to use Sort function in an automated way....But. I'll work through your suggestion as best I can (I am OK with Excel but rarely go beyond basic functions!)

Really appreciate your reply, thank you.

Mike