How to use Google Sheets checkboxes with logic?

Question

As of April 2018, checkboxes have been added to Google Sheets. They are utilized by going to Insert->Checkbox, which converts the cell(s) to checkbox form. When unchecked, by default the value becomes FALSE and when checked, TRUE.

The criteria for checked/unchecked can be changed via Data Validation.

As far as I can tell, the only way to retain the checkbox form is by manually entering the data precisely or checking/unchecking it. If the data isn't exactly TRUE or FALSE, then the checkbox goes away and instead the values are shown.

There is no information found in Google's Documentation, that I can tell.

---

As an example of the problem, the data validation rules and checkbox formatting go away if you use "=TRUE" in the formula bar instead of just "TRUE".

How can I set the cell's contents using a formula while still being able to use the new checkbox feature?

Answer 1

Short answer

Instead of using the Insert > Checkbox use the Unicode characters ballot box / checked ballot box:

• ☐
• ☑

or another similar character / emoji

Example

=IF(ISTEXT(A1),"☑","☐")

Explanation

At this time there isn't an operand, function or format for an Insert > Checkbox the same as there isn't any of this for an Insert > Chart, Insert > Image, Insert > Note, Insert > Comment

NOTES:

• SPARKLINE function works completely different than an Insert > Chart
• IMAGE function works completely different than an Insert > Image

The exception could be:

• HYPERLINK function similar to an Insert > Link

Answer 2

Checkboxes now integrate with formula evaluations, so what you tried above (using =TRUE in a cell containing a checkbox) should now work as you'd expect.

Note that the ability to toggle the checkbox with the mouse and spacebar is disabled when the cell contains a formula since the checkbox is meant to reflect the formula output.

Answer 3

Use a script to set the value of the cell (range). Thus changing the state of the tick box.

range.setValue(true);

Example Objective: I want the checkbox (in cell A1) to reflect the BOOLEAN value of the cell next to it (cell A2).

Create the script with the onEdit function (this triggers every time a cell is edited):

function onEdit (e) {
  var rangeA2 = e.range;
  if (range.getA1Notation() == "A2"){
    var valueA2 = range.getValue();
    var tickbox = SpreadsheetApp.getActiveSheet().getRange("A1");
    tickbox.setValue(valueA2);
  }
}

Answer 4

I find the checkbox works with logic operations but not as simple as expected.

To use the logic operators you have to compare, not just use the value. Here is an example to determine if 3 Checkboxes in columns C,D & E in row 2 are all checked.

=AND(C2=TRUE(),D2=TRUE(),E2=TRUE())

What doesn't work is

=AND(C2,D2,E2)

Answer 5

I thought this was pretty cool: use a tickbox to return text into another cell using "IF"

IF(logical_test, [value_if_true], [value_if_false])

for example: tickbox in C6 and this in D6

=IF(C6=TRUE,"Completed"," ")

Answer 6

1. Insert two checkboxes.
2. In one of them provide the following function:

=IF(your_checkbox_cell=TRUE,TRUE(),FALSE())

3. When you check your_checkbox_cell, the cell with the above formula will be also checked.

Answer 7

more esthetic solution involves usage of:

• IMAGE("https://i.imgur.com/DgTwvYi.png") checked
• IMAGE("https://i.imgur.com/8AxCgKZ.png") unchecked

the result is pretty convincing:

cell E24 contains:

=IF(E23=1;   
    IMAGE("https://i.imgur.com/DgTwvYi.png");
    IMAGE("https://i.imgur.com/8AxCgKZ.png"))

Answer 8

Maybe google has since changed their capabilities with this, but I am able to do this with ease. If you click on the cell itself, not the box directly to check or uncheck, you will see int he text bar it holds TRUE or FALSE. if you erase the TRUE or FALSE in the text bar, and replace with a formula, the checkbox inserted with the easy tool will now take role to your formula. Simply check (or uncheck, if you set true values first) to apply a rule for the opposite value as well.

For instance, I have a master to do list that coincides with employees' personal to do list. I set a function stating =IF(DAVES TODO!A6=TRUE) and the boxes "true" value now takes shape with this command, and checks itself if my employees personal to do list also has a check on his tab.

Furthermore, another problem I ended up solving was importing a range from a different spreadsheet but wanting the checkboxes to remain. I =importrange the data, Place check boxes 1 or 2 columns to the right of the raw true/false data, hide the raw true/false data, and then apply the same method above to have my new checkboxes mimic what the origin checkboxes are doing elsewhere. After that, simply format and color to match the rest of the imported data and no one is the wiser. This allows me to share project to do lists with clients, without revealing my employees personalized to do lists, but still give that uniform sheen we love about check boxes.

Answer 9

The image solution is more aesthetic than the unicode characters, but further logic cannot be used to discern between those image values. For example on my spreadsheet checklist, I can't then use Filter to hide completed tasks if they are designated with an image, rather than the unicode character. Basically, I have two columns of checkboxes indicating certain tasks have been completed for each row entry. I then wanted a third column of checkboxes to indicate where all of the stages are completed, and once I had that I would be able to filter those based on their true value. However, you can't enter a formula to make a true or false checkbox in the same cell. The unicode seems to be the best work around for me.

Answer 10

I have the same issue with using checkboxes in a function like IF (BOX is CHECKED then (do this calculation) else (do this calculation)) in abstract formula could be like this:

=If((A8),(A7*5),(0))

Explanation of this function:
if the cell A8 is checked, we will multiply the cell A7 by 5 and show the result, else show 0.