Hi Whizz
Not quite the way I have set up a Fantasy League table for my son (Arsenal
supporter), as you are having to enter the reference cells for each player
rather than using a Vlookup function, and then you are using cell references
for the results, so sorting is not an option.
Without re-writing your whole sheet, you could do the following.
In cell M212 enter
=L212*100000+(100-G212) and copy down through L213:L234
This multiplies the points ofr that team manger by 100,000 and adds 100 -
his number in the table to the result. The reason for this is to resolve
problems when 2 managers have the same number of points, in which case they
will be listed in their original order within the table, Shawn Gates comes
above Doreen Goodyear, merely because he comes before her in the original
list.
Then in cell N21 enter
=L212*100000+(100-G212)and copy down through N213:N234
This sorts the range of values in L212:234 descending by points scored,
using the additional values as decribed above to resolve ties.
Choose Insert>Name>Define and in the first pane type Points2 and in the
refers to pane type =$N$212:$N$234
Choose Insert>Name>Define and in the first pane type Teams and in the refers
to pane type =$H212:$N234
Choose Insert>Name>Define and in the first pane type Mangers and in the
refers to pane type =$H212:$H234
Copy your headings from H211:L211 to cell H237
In cell G238 enter =ROW()-237
In cell H238 enter =INDEX(Managers,MATCH(N212,points2,0))
In cell J238 enter =VLOOKUP($H238,Teams,3,0)
in cell L238 enter =VLOOKUP($H238,Teams,5,0)
Copy G238:L238 and paste through G239:G260
Copy range H211:L234 and go to cell H237 >Paste Special>Formats.
You can now hide columns M and N if you wish and the new table in G237:L260
will give your sorted list of results for each team.
Good luck and will Del Horno continue to score as well as the season
progresses???? My wife has him selected in her team, but I don't.
