Highest Eleven specific values and names returned.

**Nathaniel82** · 07-10-2009, 10:02 AM

Good afternoon,

How can i make a best XI team from a list of weekly scores in my Fantasy Football league?

Players are as follows:-

One goalkeeper - Top scoring goalkeeper per week
Four defenders - Top four scorers per week
Three midfielders - Top three scorers per week
Three forwards - Top three scorers per week

I need it to find the top scoring players in the above categories and then display them in a specified area.

I also need this to happen for each individual week, NOT cumulative. literally, the highest scoring XI players, as listed above, for the present week.

password for the workbook is - bouvier

any help would be fantastic.

thank you.

**NBVC** · 07-10-2009, 02:21 PM

See attached, Master sheet, Row 321 and below...

In column E, I listed your required teammembers... column F lists top scores per your requirements, and column G lists top players coincident with those scores (in order they appear, if duplicate scores).

These columns are based on scores in Week1 (column F) and you can repeat and adjust necessary ranges for other weeks...

If you really want current week then use the table in columns I:J coincident with Column E list... These show N/A, since TODAY() is July and therefore not on your sheet.. but once August hits, and you begin entering scores in the top table, then it will use the column that coincides with the week today is in... that week number is pulled in J321.

Note that all the formulas in row 323 are array formulas and must be confirmed with CTRL+SHIFT+ENTER not just ENTER to get the { } brackets... then they are copied down to 332.

**Nathaniel82** · 07-11-2009, 09:45 AM

Hello NBVC,

So, if you look at the sheet i have attached, i think i now have what i want.

I confirmed the formulas in row 323 and copied them down to row 332.

I deleted the first table because i really only want the current week.

As of now, will it update to the current week when i start putting scores in, in August?

I.E. - when it's week one, it will find the highest scores per those positions? And then automatically change to week two when i put those scores in?

Also, i had to add an extra Def position in as there needs to be Four of those. After i have copied the formulas down, is there any more editing i need to do for this??

and the final thing i want to ask is, if i then wanted to put that table onto each team sheet, do i just put formulas in like =Master!H323, then =Master!I323, Then =Master!J323 on each team sheet?

**NBVC** · 07-12-2009, 12:57 PM

I fixed up your formulas to reference your new locations....

See attached.

Answer is Yes to all your questions..

To test.. try typing "Week 1" over the formula in J321... the table should populate with the data in Week 1...

If happy, then undo to get back to the formula (or copy the formula from K321 back to J321 and remove the leading apostrophe.

**Nathaniel82** · 07-13-2009, 06:06 AM

Hello again,

i have just changed the formula in J321 to say "week 1" and the scores have been pulled over to the table.

the name column hasn't changed though.

What do i need to do to fix this or is it because i'm not in august yet??

Please see attached.