Create in-cell dropdown with filtered range

Question

I'm using Google Spreadsheet.

To illustrate my problem, I use the range A2:A8 for the data validation of D2 and E2.

But because in cell D2, you are supposed to select an animal only, I'd like to filter the range with B2:B8.

What I've tried, is using my own formula which is :

=FILTER(A2:A8;IS("B2:B8";"ANIMAL"))

but this won't work and I cannot pick the "dropdown" option if I use custom formula.

I've also tried my formula in my Range selection, but it's not valid. What is the right formula to use to have a dropdown with filtered data?

Any thoughts?

Answer 1

As it stands, in Google Sheets, the only way to natively (that is, without resorting to Google Apps Script) populate drop-down lists is to use a comma-separated list, or reference a range. So in your case you would need to reproduce your filtered list somewhere in the spreadsheet (could be on a hidden sheet):

=FILTER(A2:A8;B2:B8="ANIMAL")

and then reference the range of that output in Data validation.

The ability to use a formula to generate the drop-down list directly would be a powerful feature, and has been submitted as a feature request by many (you might like to do the same: Help menu, Report an issue).

Answer 2

There is a solution using Google Apps Scripts.

Neat video explaining all the mechanisms involved:

• Part 1
• Part 2

Basically, by editing any cell on which your drop-down depends (e.g. Country for City list), for the related "City" cell it will automatically recalculate the range for validation data (list of possible Cities).

Copy/pasting the script here just in case it becomes unavailable (that example used makes & models of cars for dependent drop-downs):

function onEdit() {
  var tabLists = "lists";
  var tabValidation = "Main";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);

  var activeCell = ss.getActiveCell();

  if (activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){

    activeCell.offset(0, 1).clearContent().clearDataValidations();

    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();

    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;

    if (makeIndex != 0){
        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
     }  
  }
}

Answer 3

Adding to the answer above to provide couple more ways to achieve this.

• Create a pivot table on the range A:B, add "Some Values" to the pivot rows (remember to uncheck "Show Totals" option), then add "Animal" to the filter and then edit the filter to select only Animals. This would produce a list of animal names. Finally use this column as the range for your data validation rule.

• Another option is to create column using the query formula. For example:

  = QUERY(A:B, "SELECT A WHERE B = 'ANIMAL'", 0)
  

Answer 4

=QUERY(A:B, "SELECT A WHERE B = 'ANIMAL'", 0)  

May prove to be more extensible than previously given credit.

=QUERY(A:B, CONCATENATE("SELECT A WHERE B = '" , G3   "'", 0) )   

might prove to be extensible enough for some uses.