Radio button in Google Sheets formula

user_5670969 0
Viewed 10969

So I've created sort of radio button chooser table with check boxes so I can pick which line I want and that brings the value from this line out to the result cell...

this is the basic principle:

this way which ever checkbox I click, the value of that cell becomes 1 (TRUE) rather than 0 (FALSE), and using SUMPRODUCT formula, every line is multiplied by the boolean checkbox so if the checkbox is not checked, the production is zeroed, and if the checkbox is checked, the value remains.

In this example on B8 I get:

B8 = A2*B2 + A3*B3 + A4*B4 = 0*3 + 1*8 + 0*12 = 8

That works well as long as the values are numbers..

Now I'm trying to do the same but with string values..

something like this:

Any idea?

google-sheets-formula google-sheets
3 Answers

Solution:

  • Use IF to get corresponding Col B
  • JOIN the resulting array

Sample:

=ARRAYFORMULA(TEXTJOIN(",",1,IF(A2:A4,B2:B4,)))
user_84044530

I used the checkbox and Google Apps Script, where column A (1) and column B (2) contain the checkboxes. For example:

Column A and Column B checkboxes

function onEdit(evt) {

    var range = evt.range;
    var val = range.getValue();
    var row = range.getRow();
    var col = range.getColumn();

    // -------------------------------------
    // --- Cannot be both add and delete ---
    // -------------------------------------
    if (col == 1) {
        if (val) {
            SpreadsheetApp.getActiveSheet().getRange(row,2).setValue('FALSE'); 
        }
    }
    else if (col == 2) {
        if (val) {
            SpreadsheetApp.getActiveSheet().getRange(row,1).setValue('FALSE'); 
        }
    }
}
user_111446280

I would like to share my codes. Please run onStart() for setting up radio boxes as well.

function onEdit(e) {

  // radio button
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var gsheetrange = gsheet.getDataRange().getValues();  
  var rowcount = gsheetrange.length; // find end of row

  for(var i = 0; i <= rowcount; i++) { // start from 0. 
      if (i==gedited){
      gsheet.getRange(gedited, 2).setValue("true"); 
   }
   else
   {
    gsheet.getRange(i, 2).setValue("false"); 
   }
  }
}

user_56930210
Related