Use checkboxes as radio buttons

I know the title may read like someone who’s lost it, but believe you me - I haven’t :grin:

Problem

If you’re someone who likes creating not just some cool/complex logic inside a cell with a ton of formulas, but also a great user experience for whoever uses your tool, you might’ve at some point in time tried googling “how to insert a radio button in google sheets” - or so I did and here are some references that I’d stumbled upon -

  1. Is radio button option available in GoogleSheet. If I select one & second, it should switch to other
  2. Radio buttons in Sheets (or hacked checkboxes… script even)?
  3. Radio button in Google Sheets formula
    etc.

I bet there are a few more out there who may’ve tried doing something similar so here’s my hack/pro-tip on how you could achieve something like that, using dashdash :rocket:

What’s what

In case you’ve just been introduced to these fancy terms (which they’re not :stuck_out_tongue:) here’s an illustration I created that should help differentiate a checkbox (generally square with a tick/check mark :ballot_box_with_check:) from a radio button (which are usually round :radio_button:)

For simplicity, i’ll only be focussing on a specific feature difference when using checkboxes vs. radio buttons in a data table -

  • there could be multiple selections when it comes to checkboxes
  • you should only be able to select 1 option when using radio buttons

Both - checkboxes and radio buttons - determine if a cell is in a TRUE (1) or FALSE (0) boolean state

Now that we have that clarified, let’s move on to how to get something like that implemented over a dashdash spreadsheet :point_down:t4:

Implementation

  1. Let’s start by creating a demo data table that looks like this -

  1. Then, let’s introduce another column, that would actually determine whether or not when an option is chosen, deserves to be marked as TRUE and if so, what ought to be done for the older options chosen on the rest of the data set.

Here’s the formula that you’d need in cell C2, that you could drag down all the way through to row 18 (per the data set from my example) -

C2: =IF(COUNTIF($A$2:$A$18,TRUE)=1,IF($A2=TRUE,"TRUE","FALSE"),"FALSE")

  1. This step is what I’d like to consider as our secret sauce :smirk: because here’s when we use the UPDATE_DATA automation function that would take Columns B & C as input data and “update” columns A & B as output data - where, as you may’ve guessed, Column B would play the role of a “key” or the unique identifier based on which the corresponding row values would get updated

I’ve added this formula in cell F2 so here’s what you could use for your reference -

F2: =IF(COUNTIF(A2:A18,FALSE),UPDATE_DATA(RANGE2JSON($B$1:$C$1,$B$2:$C$18),$B$1,$A$1:$B$1),"")

What happens here is based on a change with the IF condition, the UPDATE_DATA function gets triggered that converts the ‘source’ data table into a JSON (i.e. columns B & C) and based on the ‘key’ (Column B), updates the destination table (i.e. Column A & B).

  1. The hard part is all set and done as this step simply picks up the value / option against the row that is set to be TRUE using the plain old INDEX & MATCH(s)

I’ve set that up in the cell F2 so here’s what you’d need -

F2: =IFERROR(INDEX($A$1:$C$18,MATCH(TRUE,$A$1:$A$18,0),MATCH($B$1,$A$1:$C$1,0)),"-")

Demo

I’ve also published it as a web app in case you’d like a first-hand experience on that setup :tada:

Keep hacking :champagne:
Sourabh | No-Code Builder

3 Likes