The interactive tick boxes/checkboxes are part of Google Sheets. Here is an interesting topic regarding checkboxes, i.e., how to check-uncheck a Tick Box based on the value of another cell in Google Sheets. I am going to talk about dependent tick boxes.
I have a checkbox in one cell or a range of cells. Can I get this/these checkboxes dynamically checked/unchecked? I mean toggle the checkbox automatically when a value in another cell changes?
Yes! It’s possible. First, see how the tick boxes got automatically toggled when I change the values in other cells.
Auto Toggling of Checkboxes in Google Sheets
In this live screenshot as you can see, when I type “paid” in column B the corresponding checkboxes in column A get checked automatically. Because the checkboxes contain formulas instead of the Boolean TRUE/FALSE.
This is very simple to do. Let’s go to that cool tip.
How to Check-Uncheck a Tick Box based on Value of Another Cell
First, insert a few tick boxes. Here I am inserting the tick boxes in the range A2:A10.
Steps:
- Select the cell A2:A10 and from the menu Insert, insert the tick boxes.
So you have a bunch of unchecked tickboxes in the range A2:A10. The default values now in these cells are FALSE (the value of unchecked checkbox). I am assigning formulas to these checkboxes.
Now see how to dynamically check/uncheck a tick box in Google Sheets.
Here what I am doing is controlling the checkbox toggling from a different column. Here I am using Column B for this purpose.
-
In cell A2, yes I mean cell A2 which already contain a tick box, double click and enter this formula.
=if(B2="Paid",TRUE,FALSE)Then drag this formula down (copy down) that up to the cell A10. You can’t use an ArrayFormula here.
-
Now type “Paid” in B2 or any cell in the range B2:B10. You can see that the checkbox automatically got toggled.
You can use this dynamic behavior of tick boxes in some real-life examples. For example, toggle tick boxes automatically when you input amount in a payment receipt column.
So the checkbox got checked when you receive payments. If the checkbox is in cell A2 and the value (amount) is in B2, enter this formula in A2.
=if(AND(B2>0,ISBLANK(B2)=FALSE),TRUE,FALSE)Here is one more cool tip in line with the above checkbox tip. This time I am using a drop-down to uncheck or check all checkboxes in Google Sheets dynamically. First, see it in action.
Dynamically Check/Uncheck Checkboxes in Google Sheets Based on Drop-down Value
In cell B2 I have set a drop-down menu. If you select “Paid” all the tick boxes in the range A2:A got checked. The selection of “Unpaid” make the checkboxes, unchecked.
I guess you already know how to set a drop-down as above using data validation in Google Doc sheets.
Data validation is a Data menu option. If you are not familiar, refer to this setting.
Now you can use the earlier formula in cell B2. But this time make the cell reference absolute. See the modified formula below.
=if($B$2="Paid",TRUE,FALSE)Drag this formula down that up to the cell A10.
That’s all about how to check-uncheck a tick box based on the value of another cell in Google Sheets.
Hope you have liked this tutorial on dynamically check checkboxes in Google Sheets. Thanks for the stay. Enjoy!
More Resources:
- 10 Best Tick Box Tips and Tricks in Google Sheets.
- Change the Tick Box Color While Toggling in Google Sheets.
- Assign Values to Tick Box and Total It in Google Sheets.
Check out Crawlan.com for more useful Google Sheets tips and tricks!
