Google Sheets – Multiple Cells with Same Drop-Down List, Disallowing Duplicate Selections

google sheets

I'm looking to create a few drop downs in my spreadsheet where they share the same list of choices, but you can't select the same option twice. This is for choosing first, second, and third choices from a list.

Best Answer

This appears to require a script (Tools > Script editor) because the validation rules available through the interface don't seem to support "in this list but not in that list". Here's a script that assumes the options are in cells A1:F1 and the dropdowns are in cells A2:C2. (It is a bit easier to code this logic with row-oriented data.)

Depending on your use case, you may decide to hardcode the options into the script; this is what I pointed out in a comment with the line var options = ...

The logic of the script is:

  1. get options available
  2. get options chosen ("rankings" in the script)
  3. To compute the validation rule for cell I,
    • remove the value of this cell from rankings (.splice does this)
    • remove the remaining rankings from the list of available options (.filter does this)
  4. Set the validation rule, prohibiting invalid values and showing some help text.
  5. The above steps are executed when the document is first open, and then after each edit. 

function onOpen() {
  onEdit();
}

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var options = sheet.getRange("A1:F1").getValues()[0];  // or var options = ['q','w','e','r','t','y'];
  var ranking = sheet.getRange("A2:C2");  
  var n = ranking.getWidth();
  var current, remaining, rule; 
  for (var i=0; i<n; i++) {
    current = ranking.getValues()[0]; 
    current.splice(i,1);
    remaining = options.filter(function(j) {return current.indexOf(j) < 0});
    rule = SpreadsheetApp.newDataValidation()
                         .requireValueInList(remaining)
                         .setAllowInvalid(false)
                         .setHelpText('Choose distinct options')
                         .build();
    ranking.getCell(1,i+1).setDataValidation(rule);    
  }  
}
Related Topic