I am part of a bass club and have been keeping track of the clubs fishing stats for a while now. I have attached a couple of spread sheets which I will detail.
This first spreadsheet/workbook is 2020 Completed Season. This spread sheet represents what I have been doing as away to present my clubs standing through out the years. While this spread sheet gets the job done, I know I have done many things wrong that can make maintaining this year after year complicated. It leaves a lot of room for error and clerical mistakes. Given the knowledge level of the majority of my club members, I am very doubtful that any of them could continue to maintain and update this year after year should I leave the club or have another member take over my role.
The second spreadsheet/workbook RawClubDate-11-23-2020 more or less represents my goal and my fork in the road so to speak. I am by no means an excel pro, but I take direction well and can follow a well written/documented tutorials or YouTube video so long as they speak loud and clearly. I have watched a lot of Youtube tutorials and read several online guides, but I am struggling either because of to many choices or lack of understanding certain functions within excel.
Main goal
I want to turn RawClubData into nothing more than a data entry sheet. From there I'd like excel to almost build the queries for me that will populate the sheet templates I have created. So for instance, on the SEASON TOTALS sheet. In the top right were it says Season Selection, I want to select 2020 Season from a drop list and as a result, it fills in connected columns E1 through H1 and changes there year for SEASON RESULTS and then changes the field below it to show the month and day the season began and the month and day the season ended. Our seasons are always by year. Then populate from first to last, in rows and columns E8 to e27 through G8 through G27. And H8 through H27 showing the Anglers largest fish for that year.
This would at least cover what we do now. The TOURNAMENT RESULTS SHEET is essentially the same thing except its individual tournaments by month and day versus and entire entire season.
The second part of this is were its starting to get complicated which is why I'm having to recreated/create a new setup. We maybe moving to a point system next year. Everything would stay the same as far as how tournaments are decided, but the SEASON TOTALS which reveals how the ANGLER OF THE YEAR is determine will change.
The proposed point system is a 20 point system.
Points are awarded from 1st to 20th. 20 Points for 1st counting backwards by 1 all the way to 20th place with is 1 point, but there some exceptions.
For ties, the person who catches the most fish is the winner or finishes higher. If this results in a 2nd tie, the person with largest fish is the tie breaker.
The second exception is no fish weighed in, but the person did fish. If a person doesn't weigh a fish, equaling a value of 0.00, then they get 1 point for participating. Basically everybody who fishes but doesn't weigh a fish in is tired for 20th and gets 1 point.
These points will be added up and display first to last on SEASON TOTALS A2 through C27.
Now I know excel can do this, but I am struggling to come up with the correct/best way to do this. I am looking for guidance, directions, and/or links to tutorials or guides that will get me to my goal. If you can provide the coding to use I would appreciate that as well.
Thank you for your time in reviewing my spread sheets and guidance.
Bookmarks