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