Force Dropdowns to Display in B2:B When Adjacent Cell in A2:A Has Data

Question

In this sample Google Sheet that you can edit, I'm trying to configure the data validation dropdown menus in B2:B to only display when the adjacent cells in A2:A have data in them. As you can see in my spreadsheet, I have the data validation configured alright, but it inserts dropdown menus all the way down column B, even if there's no data next to it in Column A, and I don't want that.

How can I configure this to where Data Validation dropdown menus only display when there is data in adjacent cells in column A?

Thanks for your help!

Answer 1

You need a script

function onEdit(event){
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if (r.getColumn() == 1 && s.getName() == 'Dropdowns'){
    r.offset(0,1).clearContent()
    if (r.getValue() == ''){
      r.offset(0,1).clearDataValidations();
    }
    else{
      var lists = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Colors');
      r.offset(0,1).setDataValidation(SpreadsheetApp.newDataValidation()
        .setAllowInvalid(true)
        .requireValueInRange(lists.getRange('A2:A'+lists.getLastRow()), true)
        .build()); 
    } 
  }
}