-1

With google sheets if you add data validation, you can select a single value from a predefined list of values (or from another specified range of values). This makes data entry a lot faster and more precise.

However I would like to do the same but without being constrained to a single value. So suppose I got this list of values

healthy
cheap
expensive
trendy
mexican
fast food

And I would like the ability to quickly select from the above list _multiple values to the same cell, so that it can end up having for example: expensive, mexican

1
1

Try a script that runs on a simple trigger, like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.');
  }
  multiSelectDropDownList_(e);
}
 

/**
* Enables clumsy but workable multi-select drop-down lists.
*
* When a value is selected in a drop-down list, it is appended to the end of the cell.
* If the new value already appears in the list in the cell, the list remains as is.
* Caveat: does not properly handle cases where one of the drop-down list options is
* a substring of another.
*
* @param {Object} e The onEdit() event object.
*/
function multiSelectDropDownList_(e) {
  // version 1.1, written by --Hyde, 28 September 2021
  //  - see https://webapps.stackexchange.com/a/159321/269219
  //  - see https://support.google.com/docs/thread/128300449?msgid=128303409
  if (e.value === undefined || e.oldValue === undefined
    || e.range.columnStart !== 5
    || !e.range.getSheet().getName().match(/^(Sheet1|Sheet2|Sheet3)$/i)) {
    return;
  }
  if (e.oldValue.indexOf(e.value) === -1) {
    e.range.setValue(e.oldValue + ', ' + e.value);
  } else {
    e.range.setValue(e.oldValue);
  }
}

To install the script, paste the code in Tools > Script editor, completely replacing the myFunction() placeholder there. Configure it to the column where you have the drop-down lists by modifying the column number 5 and the sheet names regex Sheet1|Sheet2|Sheet3.

2
  • Hi thanks for your effort. It's not clear from your answer how to specify which cell this added dropdown will be applied against?
    – abbood
    Oct 4 at 14:07
  • The function as shown will do its thing in any cell in column 5, i.e., column E in the sheets Sheet1, Sheet2 and Sheet3. Edit the code to set another column and a different set of sheets as necessary. Oct 4 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.