Add a click counter to Numbers

Question

Level 1

8 points

Add a click counter to Numbers

I am trying to create a numbers spreadsheet to track the numbers of plays my football players are in the game. I would like the button to count it as a play for all players who have the checkbox next to their name checked and keep a running total for that player in a column next to their name. Ideally I would like the button to reset a plays in a row column for each player who did not have their checkbox checked when the button is pushed. A picture of the basic spreadsheet is below. Any help would be appreciated. Thank you.

MacBook Pro (Retina, Mid 2012)

Posted on Sep 18, 2016 1:38 PM

Reply

Answer 1

t quinn

Level 5

7,063 points

Sep 20, 2016 8:39 AM in response to CoachMac3

Hi CoachMac,

Here is a Numbers approach that will work well with data entry with a form in iOS. So if you have an iPhone or iPad charge it up.

You need a row for each play.

Your original table will be a report table.

D2= OFFSET(Data::A$12,0,ROW(cell)−1,rows,columns)

OFFSET() can be a little tricky so know if you change the number of columns before player 1 in the data table or add a row before player 1 in your report you will need to modify the formula.

Here is the Form on an iPod.

This will be as easy as your choose the players and click a button. You can use the > to go to the next row and the + to add rows. So you will go to the next play and choose your players. repeat.

I am not sure what you want from the "in a row". Do you want the latest run or all time?

quinn

Reply

Answer 2

t quinn

Level 5

7,063 points

Sep 20, 2016 9:30 AM in response to CoachMac3

Hi CoachMac,

Maybe an(other) assistant.

Here we have your "in a row". Added another column for each player to the data table. These will not be editable in a form.

L3= IF(B3,IF(B3,L2+1,1),0)

L2= IF(B2,1,0)

B13= INDEX(L,ROW(cell)−2,column-index,area-index)

This redundant with the formula in L13. More convienient here.

quinn

Reply

Answer 3

Barry

Level 9

60,064 points

Sep 20, 2016 1:10 AM in response to CoachMac3

Checkboxes do not produce a 'blip' or a 'pulse' each tine they are clicked. Clicking them simply changes their state from 'off" to 'on' or from 'on' to 'off'. At any instant, you can count the number that are on and the number that are off, but you cannon count the number of times the state has changed.

A cell formatted as a 'stepper' will increment a value by one (or a different value if you set it for a different increment) each time its control is clicked. But using a 'stepper' is not a simple process of clicking on the cell.

The cell must be selected (by clicking it) to show the control. Then the control may be clicked to increment the value in the cell. The the next cell must be clicked to display its control.

Regards,

Barry

Reply

Answer 4

CoachMac3 Author

Level 1

8 points

Sep 20, 2016 7:18 AM in response to Barry

Quinn- Thank you I may have to check into Applescript.

Barry- I don"t think I need a blip each time the checkbox is checked. I am wanting the checkbox to be counted for each player every time time I click the "click" button. The checkbox is just there for me to check who is in the game and designate who gets a play added to their total. Is that possible? I don't think the stepper function would work if I would have to "step" each player for every snap. Thanks.

Reply

Answer 5

CoachMac3 Author

Level 1

8 points

Sep 20, 2016 8:57 AM in response to t quinn

I was thinking about something similar to that if a click button wouldn't work. The potential issue I forsee is I would constantly need to be checking a player in for every play instead of once at the beginning of a drive and only change when we make a substitution. I feel I would be staring at the ipad all game and not able to fulfill my other coaching responsibilities. The Issue the team is having is we have many players who are on both offense and defense so late in the game those players are out of gas, the in a row column is to track how many consecutive plays they've been on the field, I have conditional formatting set up in that column so the color changes when we should start thinking about getting them a rest.

Reply

Answer 6

CoachMac3 Author

Level 1

8 points

Sep 20, 2016 10:10 AM in response to t quinn

Thank you Quinn, I like the secondary sheet you put together but still hope to find a way to only have to check the box one time per drive. Is it even possible to create a simple clicker in numbers?

Was thinking about something like a "report sheet" much like the one I put together but using your sheet to actually create the data. Example I push the simple clicker on the report sheet it looks at the checkbox on the report sheet and on the data sheet column 1 would have a function for row one like (=countifs(table1::C3) if b12(hypothetically where the click counter would be)1,0)) I don't know if the function I wrote is correct but something similar to that. Is that possible?

Thanks again for your help.

Reply

Answer 7

t quinn

Level 5

7,063 points

Sep 20, 2016 7:20 PM in response to CoachMac3

Hi CoachMac,

The situation is that there is no way for Numbers to remember what a check box was at a time before.

The best I can come up with for the recurring lineup is that you could copy/paste the entire row. Then you could click or unclick any substitutions. That would work with my data table above for entire rows after the first play. If you wanted to copy paste the first play, you could only copy/paste the first set of player columns without screwing up the "in a row" calculations.

quinn

Reply

Answer 8

t quinn

Level 5

7,063 points

Sep 19, 2016 8:12 PM in response to CoachMac3

Hi CoachMac,

This is not something that can be done natively in Numbers. There maybe an Applescript solution but it is beyond me at the moment. Sorry I can't be more help.

quinn

Reply

Answer 9

CoachMac3 Author

Level 1

8 points

Sep 22, 2016 8:11 AM in response to t quinn

I was worried that would be the issue. Thats why I was hoping a button could scan the checkbox and add a number to a column and not have to remember anything. Oh well. Thanks for your help.

Reply