2

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.

1 Answer 1

2

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);    
  }  
}
1
  • I haven't tested this yet, but it looks like it will give the desired effect. Jul 7, 2015 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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