Google-sheets – How to use Google Sheets checkboxes with logic

google sheets

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?

Best Answer

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

Related Solutions

Google-sheets – How to use ARRAYFORMULA with QUERY in Google Sheets

The arrayformula(query(...)) combination is not supported; there is no concept of a query processing an array of arrays or executing an array of query strings.

You have two options: (a) repeat query on every row; (b) use vlookup to retrieve columns of data, as explained below. For example:

=arrayformula(vlookup(E3:E, {A3:A, J3:J}, 2, false))

takes one element of E3:E at a time, finds this element in column A, and retrieves the corresponding element of column J. With this approach you would need six separate vlookups to get the columns J,I,H,G,F,E, but you won't need a separate command for each row.

A complication is that vlookup accepts only one search key, and you want to search by 4 parameters,

A='"&E3&"' and 
B='"&B3&"' and 
C="&C3&" and 
D="&D3&

This can be worked around by concatenating these into one search key: you can have a column like Z,

=arrayformula(E3:E & "|" & B3:B & "|" & C3:C & "|" & D3:D)

which concatenates four search parameters into one pipe-delimited search key. Do the same for the table to be searched, and then compare these keys using vlookup.

Google-sheets – How to convert existing boolean cells into checkboxes in Google Sheets

right-click on the cell and select Data validation where: