Dynamically generated Dropdown in Google sheets

0
votes

How do we create a dropdown in google sheets based upon data in the next column -:

I search about it did not found a way that makes dropdown based upon a formula, I know about named ranges with which I can create dependent dropdown, but the problem is my list dynamically generated so dropdown should be.

Like in screenshot enter image description here

Link to the sheet: https://docs.google.com/spreadsheets/d/1BF0I3TACr1GJXmn1vse-tAE4b1h-yKp9SzGMfstVqbs/edit?usp=sharing

1
google-apps-scriptgoogle-sheetsscripting
you need a script for that – player0
Yes,I can do that with the script but I am trying to find a way to do with Functions. – Zamir
this is just not possible to solve with formulas – player0
Okay ,Thanks for letting me know. – Zamir

1 Answers

0
votes

Create a DataValidation with the column to the right of active cell

function createValidationWithNextColumn() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  sh.getActiveCell().clearDataValidations();//remove any current validations
  var rule=SpreadsheetApp.newDataValidation()
  .requireValueInRange(sh.getRange(sh.getActiveCell().getRow(),sh.getActiveCell().getColumn()+1,sh.getLastRow(),1))
  .build();
  sh.getActiveCell().setDataValidation(rule);
}