28

This question already has an answer here:

I'm doing my club's accounting in a Google spreadsheet. I am looking for a way to put a checkbox at the beginning of a line in order to tick off the members who paid their dues and then have a function that will automatically add the amount of the "dues paid" cell to a "total dues paid" cell, elsewhere in the sheet.

Right now I'm doing it with a function of the type SUM(A1:A600)-A3-A20-A120 etc.—not very elegant…

If anyone could point me to a solution or a resource to learn how to implement this, I would appreciate it!

marked as duplicate by Rubén, serenesat, ale, jonsca Apr 16 '18 at 22:49

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

29

Google has now (2018-05) added checkboxes!

This is how you do it now (according to Google):

  1. Select the cells you want to have checkboxes.
  2. In the menu at the top, click Insert and then Checkbox.

Use this in combination with COUNTIF (or SUMIF, depending on what you want to count). Check for TRUE/FALSE.

  • @markiv What is the purpose of using an ARRAYFORMULA() in this case? :) – Punchlinern May 16 '18 at 12:55
  • You're absolutely right. Updating my comment: =COUNTIF(A1:A10, "TRUE") works just as well when I just want to count the number of checked boxes in that range. – markiv May 17 '18 at 16:01
5

To build on the accepted answer, you can tweak that a bit to get checks and x's.

Use two cells in your sheet, one to hold the formula =char(10003), and the other to hold =char(10005).

Now follow the accepted answer exactly, but instead of "List of items" select "List from a range" and choose the two cells you just created.

This will create a dropdown with a check mark and an 'x' mark in it.

  • Great idea! You don't have to use List from a range, though, if you don't prefer that. You can use the symbols directly in the Data Validation-dialog, and the sheet functions. – Punchlinern Nov 12 '14 at 16:03
4

I believe I have a better solution, please see below. I don't normally post on forums, but since I could not find an acceptable solution to this problem I made my own.

This will create an actual check box, bullet, radio button, or really whatever you want.

  1. first create create some images to toggle on and off. I used these:

enter image description here enter image description here

  1. Create a script that will toggle a 1 or 0 in the cell adjacent to your check box cell.

  2. in the check box cell enter a formula similar to this (this formuls assumes your check box is in cell B2):

    =if(A2=1,image(" IMAGE URL FOR CHECK "),image(" IMAGE URL FOR UNCHECK "))

  3. Now overlay your check box cell with a blank image.

  4. Assign your script to the blank image and you have the illusion of checking and un-checking the box.

Might sound a little complicated but it works very well.

  • It's a good idea if you have only a few cells, it gets rather time consuming when the number of rows increase. You have to assign the script to the image on each row, right? One at a time? Note that Gregwah (who asked the question) checked 600 rows. – Punchlinern Nov 12 '14 at 16:11
  • You are correct. It would be quite time consuming to add this to 600 rows. Hope this can help someone out. – Brandon Lee Nov 12 '14 at 17:01
1

I think the problem is that you're trying to use a spreadsheet like a database, or an application. Why not use something like Ragic instead? You can add different types of fields, including selection checkboxes like you want.

Disclaimer: I work at Ragic and have been designing many database applications with it.

  • 4
    I believe a spreadsheet is a good tool for this, another application is not necessary. – Punchlinern Mar 26 '14 at 9:16
  • How is access controlled? This would work for me if people could access my spreadsheet/db via a google account or some such. – drevicko Mar 15 '16 at 17:48
  • @crimsonpandaren: this looks pretty cool (but the name is terrible) – Brandon Nov 16 '17 at 20:59
1

Here are three easy steps to achieve tick/cross success:

  1. In the Google Sheets spreadsheet go to Tools -> Script Editor.
  2. Enter the following code:

    function onEdit() {
      if(SpreadsheetApp.getActiveRange().getValue() == "1") {
        SpreadsheetApp.getActiveRange().setValue('=CHAR(10004)');
        SpreadsheetApp.getActiveRange().setBackgroundRGB(0,255, 0);
      }
      if(SpreadsheetApp.getActiveRange().getValue() == "0") {
        SpreadsheetApp.getActiveRange().setValue('=CHAR(10060)');
        SpreadsheetApp.getActiveRange().setBackgroundRGB(255, 0, 0);
      }
    }
    
  3. Enter into any cell a 1 for a tick, and a zero for a cross.

  4. Be sure to save it and allow scripting on the spreadsheet.
0

Alternatively, use a combination of the posts above.

  1. Click DataValidation
  2. Enter information as needed for your columns
  3. Use "List of Items" for your criteria and use the image command to utilise the images of the check box and unchecked box.

I.e.: instead of yes,no for your list use =image("http://i.stack.imgur.com/GChKZ.jpg"), =image("http://i.stack.imgur.com/yQalm.jpg")

This will allow you to use the drop down menu to select images to insert. This does get flagged as invalid cell contents but it still works.

Not the answer you're looking for? Browse other questions tagged or ask your own question.