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

1. ## 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...

2. ## 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?

3. ## 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. ## 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. ## Re: 2nd Drop Down List Dependent on 1st Drop Down List Creation

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