CoachMac3
Level 1 (8 points)
iWork

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

 

 

Screen Shot 2016-09-18 at 2.23.50 PM.png

MacBook Pro (Retina, Mid 2012)

Posted on Sep 18, 2016 1:38 PM

by t quinn,Apple recommended
t quinn t quinn
Level 5 (5,063 points)
Mac OS X
A:

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.

Screen Shot 2016-09-20 at 9.19.26 AM.png

You need a row for each play.

Your original table will be a report table.

Screen Shot 2016-09-20 at 9.21.12 AM.png

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.

IMG_0712.PNG

 

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

Posted on Sep 20, 2016 8:39 AM

Close
CoachMac3

Q: Add a click counter to Numbers

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Sep 19, 2016 8:12 PM in response to CoachMac3
    Level 5 (5,063 points)
    Mac OS X
    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

  • by Barry,

    Barry Barry Sep 20, 2016 1:10 AM in response to CoachMac3
    Level 7 (32,714 points)
    iWork
    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

  • by CoachMac3,

    CoachMac3 CoachMac3 Sep 20, 2016 7:18 AM in response to Barry
    Level 1 (8 points)
    iWork
    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.

  • by t quinn,Apple recommended

    t quinn t quinn Sep 20, 2016 8:39 AM in response to CoachMac3
    Level 5 (5,063 points)
    Mac OS X
    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.

    Screen Shot 2016-09-20 at 9.19.26 AM.png

    You need a row for each play.

    Your original table will be a report table.

    Screen Shot 2016-09-20 at 9.21.12 AM.png

    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.

    IMG_0712.PNG

     

    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

  • by CoachMac3,

    CoachMac3 CoachMac3 Sep 20, 2016 8:57 AM in response to t quinn
    Level 1 (8 points)
    iWork
    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.

  • by t quinn,Apple recommended

    t quinn t quinn Sep 20, 2016 9:30 AM in response to CoachMac3
    Level 5 (5,063 points)
    Mac OS X
    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.

    Screen Shot 2016-09-20 at 10.19.45 AM.png

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

    L2= IF(B2,1,0)

    Screen Shot 2016-09-20 at 10.23.27 AM.png

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

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

     

    quinn

  • by CoachMac3,

    CoachMac3 CoachMac3 Sep 20, 2016 10:10 AM in response to t quinn
    Level 1 (8 points)
    iWork
    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.

  • by t quinn,

    t quinn t quinn Sep 20, 2016 7:20 PM in response to CoachMac3
    Level 5 (5,063 points)
    Mac OS X
    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

  • by CoachMac3,

    CoachMac3 CoachMac3 Sep 22, 2016 8:11 AM in response to t quinn
    Level 1 (8 points)
    iWork
    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.