I am running a league online and want to try and make a spreed sheet that will sort it all out for me i have got as far as it will add it all up for me but i want it to sort the table out aswell ans i am stuck I have attached at file to this post and what i am looking for is for column 0 to be storted from most points to least points but it has to move all the other data on that line as well to keep it in order is there a way this can be done?
Last edited by gazza uk; 02-11-2011 at 06:28 PM.
Hi gazza uk
This code in in the attached.See it it works as you require. Let me know of issues.
Please Login or Register to view this content.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Hi John your message is a bit vague "Hi gazza uk This code in in the attached. " when I open the attached file nothing works not even adding up there score when i input a new score example: c4+e4 should =o4 and when i input a new score in say g4 o4 should change then but its not. so I don't understand what you have done
gazza
Hi gazza uk
Some how the formulas got lost in the workbook I posted. Try the attached.
Ok John I still don't see what you have done lets say for week 3 I give happyman123uk 10 points and onamission 1 point that would make happyman123uk the leader there for i want his name to move to the top and onamission to move down a place.
Gazza
Hi gazza uk
Sorry, but I gotta ask...did you press the button? I did the sameandfor week 3 I give happyman123uk 10 points and onamission 1 pointSee picture attached.that would make happyman123uk the leader
Press the button to do the sort. If your still having trouble, get back to me.
where is this button i goto the office button in top left hand corner then to excel options then avanced but can't find the prefix at all it's excel 2007 i have
Hi gazza uk
I've moved the button to Cell A1. It's labeled "Sort". Enter your data then click on the "Sort" button. See attached.
That works a treat thankyou very much can I add weeks to this without messing it up and increase the number of players to 20per division
I have inclosed the final drafted spreed sheet could you please work your magic on it.Is it also possible to put a minus score into the sheet.
again many many thanks
Gazza
Hi gazza uk
The code in the attached has been modified to do the following:
With this version, you can add as many weeks as you like; the Total column must always be the last column.add weeks to this without messing it up
You can have as many players as you like in any number of Leagues; white space must be preserved between the Leagues.increase the number of players
There's no reason why you can'tBear in mind, if a player has a minus in the Total column, the minus Total will appear after all Positive and Zero Totals.put a minus score into the sheet
Last edited by jaslake; 02-09-2011 at 01:44 PM.
Thats great thankyou very much John just 1 small problem the number of players (column A) Changes with there position when there should stay where they are other than that i think you have cracked it again thank you very much for you help
Gazza
Hi John i just noticed that when I add points to the bottom 3 or 4 in both divisions its not added then up at all. I have inclosed spreed sheet with problem in
thanks Gazza
Hi gazza uk
When you added players "anyname", you didn't put formulas in Column T. Include the formulas and this problem should go away.This issue is a rather simple fixwhen I add points to the bottom 3 or 4 in both divisions its not added then up at allAre you comfortable either inserting the code or changing the code? I can show you what lines to change (2 or 3) or I can post a new workbook...what do you wish?the number of players (column A) Changes with there position
PS: Workbook attached with above modifications
Last edited by jaslake; 02-09-2011 at 01:47 PM. Reason: Decided to add file; removed previous attachment
John thank you so much I have been able to add the last few sums to it and all works great now. But every time I look at it I think what if they could be done but for now I have taken up too much of your time so I will leave alone for the time being
So once again a big thank you
Gazza
Hi Gazza
My pleasure. Need further help, let me know. Further changes should not be difficult now that the basics are complete.
Hi John You did ask so i will list them for you
I have put in red the par score for that week and what i would like it to do is award them points based on there score.This is where it gets that wee bit harder to work out:
The first 12 get points starting with 12 points for first place and 11 points for 2nd place and so on down to 1 point for 12th place.But if two or three members have the same score they both get the same amount of points this meaning that the 13th and 14th place would get 2 and 1 points.
So it could end up with all players getting some points each week.
The par score will have to be able to change from a par 30 to a par ?? depending what course we are on and how many holes we do.
The other thing is if they hit under par they get a bonus point for every shot under par so if someone shot a 34 on a par 36 they would get 12 points for coming first and 2 bonus points so that would make it 14 they would score that week
And the last thing i would like it to do is if I put WD (withdrawn) in there score i would like it to take a point away.NP (not played would be 0 score.
You did ask John i know I am asking alot but if you fancy sorting it out that would be great
but if not its ok also what you have done already is a great help to me
many thanks
Gazza
Hi Gazza
I may or may not be able to assist with this...just don't know yet. But, I'll look at it.
First issue, I'm color blind and don't "see" this anywhere on Sheet1If it's there, change it to a bright color...like yellow...or, change the cell fill color to a bright color.I have put in red the par score for that week
Second issue, fill in a column, perhaps Week 3 that demonstrates what you're describing (for each League)...I'm a kinetic learner...if I can see what you want, I may be able to figure it out.
No promises...this may be a subject for a new Thread.
Hi John I habe hightlighted the par in yellow and they are as follows c2,e2,g2,and i2 and so on Have a look at see what you think and once again a big thankyou for what you have done so far.
Cheers
Gazza
Hi John just a quick not to say what I will do is use this sheet for season 3 and whenn i am finished using it which will be 4 weeks from saturday I will send you it with full of the data and explane it in more detail
Again thanks for all your help
Gazza
Hi John is there any chance you can make it upto 21 per division please
Gazza
Hi Gazza
You don't really need me to do that, although I'll be happy to do so if you wish. You can add as many players as you like to each division (League). The only thing you need to be aware of is there needs to be at least one blank row above and one blank row beneath the word League.
So to add a new player to League 1, insert a new row at Row 26 and type in the players name after player 20; in the case of League 2, add player 21 to the bottom of your list. Don't forget the formulas in the last column.
Pretty straight forward...if you break it, let me know.
I've looked at your point distribution matrix and I've 'gotta admit it's well beyond my skill level to write that formula. There are many on this Forum that have such skill. I'd recommend you start a new thread for the point distribution issue. I'll look for your new thread because I'd like to see how it gets solved.
John yes thankyou i did try to add it at the bottom but it would not work but added it into the middle and it works great i will fill this sheet out over the next few weeks and post a new thread but thankyou for all your help.
Gazza
Hi Gazza
What I suggested worked for me but I'll work on a "fail safe" approach for you. Keep me posted on your progress. I'll do the same.
Hi John Have finished the spreed sheet with a lot more help from this site but for some reason the sort is not working now i have inclosed the work sheet from you to see.When i hit sort it removes all the bonus points formulas and reverts there score back to what they where. it is also not sorting them into order 1st 2nd 3rd and so on.Now i am guessing that i have added to many columns and messed up your program in some way could you have a look at for me please
Cheers gary
Hi Gazza
As it's late here, I'll look at this tomorrow. Get back to you.
Hi Gary
I can't open the code file...says it's locked. Have you password protected it?
Sorry john don't know how or why i did that try this one
Cheers Gary
Gary, please do not ask the same question over multiple threads.
MarvinP made the point that given lack of absolute referencing in the Bonus Points column if you sort the data those formulae will go awry.
from here on in please stick to either/or thread on this matter and modify the remaining thread accordinglyPlease Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi Gary
Still unable to view the code. See this locked project.jpg
I'm not sure what this is aboutCan you fill me in? Never mind, I looked at your other thread...can't view the code in that attachment either...same issue.Gary, please do not ask the same question over multiple threads.
MarvinP made the point that given lack of absolute referencing in the Bonus Points column if you sort the data those formulae will go awry.
Hi john how did you make that button you put into my sheet called sort ? i have been reading up on how macros work but can/t work out how to make the sort button?
Hi Gary
To do thisyou need to have the "Developer" tab showing. If it's not see this http://office.microsoft.com/en-us/ex...010173052.aspxhow did you make that button you put into my sheet called sort
Now, your worksheet is protected, so, first you'll need to unprotect it. Then, on the Developer tab, click on Design Mode. Then click on Insert. What I've inserted is an Active X Control Command Button. See this for how to do the actual button http://excel.tips.net/Pages/T002479_...Worksheet.html
See if you can work it out from here...if not, get back to me. By the way, I'd really like to see your finished product...still can't access the code in your recent posts.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks