+ Reply to Thread
Results 1 to 7 of 7

Need Guidance/Suggestion/Help with Excel Spreadsheet Design/Setup

  1. #1
    Registered User
    Join Date
    11-23-2020
    Location
    United States
    MS-Off Ver
    OFFICE 2016
    Posts
    8

    Need Guidance/Suggestion/Help with Excel Spreadsheet Design/Setup

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Need Guidance/Suggestion/Help with Excel Spreadsheet Design/Setup

    I decided to bark up this tree. It looks like a fun project. Attached is a prototype with some instructions. I figure that I will give you a chance to play with it to see if it is heading in the right direction.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    11-23-2020
    Location
    United States
    MS-Off Ver
    OFFICE 2016
    Posts
    8

    Re: Need Guidance/Suggestion/Help with Excel Spreadsheet Design/Setup

    dflak

    Thanks for your reply and hard work. I would say you are right on track. I have attacked a word document to try and give more detail on your questions. Hit me up with any questions you have.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Need Guidance/Suggestion/Help with Excel Spreadsheet Design/Setup

    I believe that most of the Angler and Lake Stats could be displayed using Pivot Tables which reduces the need for writing formulas.
    I have put a few examples each on the Angler Stats and Lake Stats sheets.
    Some of what you requested required the addition of another column (Distinct) on the Club Data sheet. That column is populated using: =COUNTIFS(A$6:A6,A6)
    The result of that column is used to filter (1) the count of location pivot table as well as in the calculated field formula and filter for the average fish by location pivot table.
    If these are useful then we can work on producing more.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Need Guidance/Suggestion/Help with Excel Spreadsheet Design/Setup

    It looks like JeteMc jumped in here. I have been delayed. I have not had the time to look at your document in detail yet. It looks like it has the information I need.

    JeteMC is correct, since your data is normalized, pivot tables are the way to go.

    My plan is to take this a step further and give you a macro where you tell it what contest you want to evaluate and it will run that pivot table for you and then "publish" it to a file that you can distribute.

  6. #6
    Registered User
    Join Date
    11-23-2020
    Location
    United States
    MS-Off Ver
    OFFICE 2016
    Posts
    8

    Re: Need Guidance/Suggestion/Help with Excel Spreadsheet Design/Setup

    JeteMc made pretty much the same thing I was already able to make. Though showing the largest fish for each lake was a nice touch. I hadn't thought of that one. The main issue I've had with Pivot Tables is graphing it out nice and neat on a dashboard. What ever the first value is, it shows it very nice, but no matter what chart I use, setting multiple axis, or chart types. The second, third, or 4th values just show a flat line or flat 0 value or just the opposite, 100% value. For individual Angler Stats, it's not a big deal. They were so impressed with just seeing the data, they didn't care how it looked. The Lake Stats is the most important one.

    I am still very impressed with what you've both done thus far. Thank you both for all the hard work. I like you have been swamped as well. Dealing with work, boat, car troubles, and a new puppy. Feel like a one legged man in an a-- kicking contest.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Need Guidance/Suggestion/Help with Excel Spreadsheet Design/Setup

    From the description of the charts in post #6 I take it that line charts are used. I suggest using clustered column charts instead as the data is discrete opposed to being continuous and because column charts are somewhat self explanatory.
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 03-18-2015, 01:00 AM
  2. Excel Spreadsheet Design Help - Golf Tournament
    By MJM64 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-21-2014, 02:52 PM
  3. Copy data from many sheets into a new sheet in different design setup
    By christopherL in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2013, 10:36 AM
  4. Replies: 0
    Last Post: 06-13-2011, 10:20 AM
  5. Replies: 9
    Last Post: 01-31-2009, 08:52 AM
  6. Crystal Xcelsius - Design suggestion
    By olasa in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-10-2007, 04:54 PM
  7. Need Some Guidance for New Spread Sheet Design
    By Ufdah in forum Excel General
    Replies: 2
    Last Post: 12-01-2005, 04:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1