Thanks Donkeyote!!!!!
In my fantasy football league, can i have a formula that puts all teams in a league table in order of highest points to lowest points that automatically updates itself when scores change?
I have to manually sort each team sheet in descending order otherwise, and there are eleven teams.
Last edited by Nathaniel82; 04-23-2009 at 06:47 AM. Reason: Forgot to attach sheet
You may find you get more response if you post an unprotected workbook or at least provide the password...
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
oh my god, sorry about that. the password is bouvier
just as you see it.
thanks for that.
just going to bump this topic as i haven't much time to try things out.
thanks!!!
I think more info is needed - I can't make head nor tail of what it is you want to do... ie which table are you talking about (there are a lot on each sheet)... how are the values altered etc etc...
Well, the table i want to automatically update is in the bottom left hand corner of each sheet.
The one with 1, 2, 3 etc down the side.
I will put cell references in each cell on each sheet later but i just want to know which formula to use to make the teams and scores position in the table sort itself in descending order whenever the scores change. (this will happen weekly).
the players willl get a score each week, this goes to a total weekly score for the team. So each week, there will be a cumulative score per team that needs to go into the table.
E.G. Nat utd will score 76 points for week one.
Acid utd will score 64 points for week one.
therefore, Nat utd would be top of the table, whereas Acid utd will be second.
I hope this is enough.
thank you.
That makes sense, so in essence the table is repeated on each team sheet, correct ?
To be honest I would be inclined to insert a Summary table and generate a matrix and then refer to that from the first individual team sheet... ie you need only set up Nat Utd (1st sheet) to reference the matrix, thereafter all other sheets can simply reference the Nat Utd table given they are identical (ie reduce repetitive calcs)
See if the attached (zipped) is of interest... it's still a large file unfortunately... let me know once you have it downloaded as I'd like to remove thereafter given it uses up my allowance and I post quite a few sample files.
Last edited by DonkeyOte; 04-23-2009 at 06:18 AM. Reason: attachment removed in lieu of post 13
FYI - there's a typo that needs to be corrected on DONKEYOTE worksheet... change F2 to the below formula:
F2: =Dino!M3+F$41
copy that formula down to F39
(failure to do to correct will lead table to generate spurious result)
hello again,
i've downloaded the file now so you can get rid of it.
I wont be able to use it until i go home tonight though as i cant install things on my works computer.
will let you know how it's all gone tomorrow though.
thanks for your help with this.
Hello again donkeyote,
i extracted that file to my memory stick and it worked.
The only thing is that, howcome there are two "Dino's" at the bottom of the league?
i changed the typo, and it looks like it is done how you said to do it.
Once column F has been corrected (F2:F39) on DONKEYOTE sheet the table on the team sheet should have also corrected itself (re: Dino)
ok done that, it looks fine apart from!!!!!
sorry about this, where it says the position in the league and the score next to it, it should show the value in cell m41.
The formulas you have done are completely new to me so i dont know how to amend them so the reference cell is that one.
Cell m41 shows the teams total score to date.
OK I should have guessed that it was a cumulative... anyway given we've set up a weekly detail there's little point dispensing with it altogether, it could come in handy (ie roll back positions) so in the latest attachment I've simply added a further line which looks at the aggregate totals ... by setting the cell in yellow to 1 the tables will display per the cumulative, if you delete the 1 from the cell you should find the tables then revert to show table based on last week only... not necessarily something you need/want but some functionality you now have nonetheless.
(I will now in lieu of this upload remove the old)
Ok donkeyote,
thank you very much, you've helped me out there, in a big way.
the only thin i would have to ask you now is that, in the "active" column, and the table after it, do i have to update that area or will it be done automatically?
if i have to update it, how do i do it?
Everything will update automatically... the only thing you may wish to alter manually on occasion would be the yellow cell (N45) as previously mentioned - ie toggling that cell between 1 and blank will alter which scores are used to populate the resulting table(s) where 1 = cumulative and blank = current week only.
OK got it.
Again, thank you very much, you've been immense.
Very knowledgable.
Keep up the good work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks