+ Reply to Thread
Results 1 to 5 of 5

2nd Drop Down List Dependent on 1st Drop Down List Creation

  1. #1
    Registered User
    Join Date
    06-19-2012
    Location
    Panama City, Florida
    MS-Off Ver
    Excel 2010
    Posts
    7

    2nd Drop Down List Dependent on 1st Drop Down List Creation

    I apologize for this being so long, BUT…it is the only way to explain the problem I am having with creating dependent drop down lists. Any help in solving this issue would be GREATLY APPRECIATED since bowling season is about to begin.

    I have a spreadsheet which I use each night of bowling to record what I actual bowl during each frame of each game. All of the formulas work correctly in calculating the score of games bowled.

    To prevent and/or eliminate errors in the scoring, I would like to eliminate manually entering the Pin Count for each frame. I would like to have 2 drop down lists in each frame (except for 10th frame – see below). The first drop down list would contain “ X,9,8,7,6,5,4,3,2,1,0”. The second drop down list which is the 2nd ball rolled (if not a strike) would be dependent on the 1st drop down list. The result should be that the total for the frame NOT to exceed 10 pins. For example, if I strike on the first ball then the drop down list would not have any selection for the 2nd Ball for that frame. (This being that the "X" represents a count of 10 pins.) If I have a 9 Count on the 1st Ball, then the 2nd Ball selection will be two choices. You would select "0" for no pin or select "/" for a spare. The same would occur if you have an 8 Count on the 1st Ball, the 2nd Ball selection will be three choices. You would select "1" for one pin, select "0" for no pin or select "/" for a spare. This would be similar for the other pin counts. The formulas in the spreadsheet work as to calculating the correct scores. In Game 1 on Line 11 (see attached) you notice the drop down list in the 1st box of each Frame bowled, however, I cannot figure out the drop down list formula for the 2nd Ball rolled. All of the frames 1 through 9 would be the same, in Frame 10 there is an exception.
    This exception is due to the following:
    In the 10th Frame a bowler can have up to 3 Balls thrown in this frame. If the bowler Strikes on the first ball he is allowed Two more balls for the game. If he Strikes on the second ball of the 10th Frame he is allowed the third ball. Also, if the bowler throws a ball and makes a spare, then he is allowed an additional ball for a total of 3 balls thrown in the 10the Frame. If the bowler does NOT make a spare in the 10th Frame then he is allowed only two balls thrown in the 10th Frame. There would then be no third drop down list for the frame. So the bowler could have these possibilities:
    1) Strike – Strike – Strike
    2) Strike – Pin Count – Spare
    3) Strike – Pin Count – No Spare
    4) Pin Count – Spare – Strike
    5) Pin Count – Spare – Pin Count
    and/or
    6) Pin Count – No spare.

    **There should NOT be a X selection in the 2nd box except for the 10th Frame as explained for the exception**

    I have attached a snapshot of the spreadsheet (with notes) for your use.

    Thanks...
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: 2nd Drop Down List Dependent on 1st Drop Down List Creation

    Hi, seems easy enough to do. Any chance of a copy of the workbook in order to solve it for you?
    If I've been of help, please hit the star

  3. #3
    Registered User
    Join Date
    06-19-2012
    Location
    Panama City, Florida
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: 2nd Drop Down List Dependent on 1st Drop Down List Creation

    Absolutely

    Thank you for prompt reply to my post.

    BTW - Do you Bowl?

    You may need my email do to the size of the file being at 976kb now, please let me know.

    Larry

  4. #4
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: 2nd Drop Down List Dependent on 1st Drop Down List Creation

    Hi Larry,

    I've just pm'd you with my email address. Drop me a quick line there and I'll send your workbook in reply.

    Yes, I bowl occasionally. Not that great at it though

  5. #5
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: 2nd Drop Down List Dependent on 1st Drop Down List Creation

    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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