Is there a way to create a data vaildation script for multiple rows

0
votes

I am setting up a spreadsheet in Google Sheets and i need Data Validation to make a list of items in Column E to pull data in Columns K:N.

I have tried the Data Validation to pull the cell down but it copies the information in the previous row.

https://docs.google.com/spreadsheets/d/1q6laBJgtsZ8famEV9tbQI0MsRmwE8Lbs0HB-2AuTt7I/edit?usp=sharing

Thank You

1
validationgoogle-apps-scriptrange
Reproduce the script you tried to use here. No script and don't want a script solution? Edit your questions to choose the proper tags – tehhowch
I set it up manually for you. If you want a script let us know. There does seem to be a copying issue. Not sure what the problem is. I usually use vertical ranges for the data validation, perhaps that's why I never ran into the problem. If you can use vertical ranges that might be another solution. You could just transpose your data and setup them up again. – Cooper
Thanks for the help. Got it sorted using the script from this page stackoverflow.com/questions/38463473/… – Robert Hall

1 Answers

0
votes

I played around with this idea this morning and you're welcome to use this simple script. Just before you run it you first pick the range to validate and then the range that has the validation values. Play with it before you use it on a working spreadsheet.

function makeValidation() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rgA=sh.getActiveRangeList().getRanges();
  if(rgA.length==2) {
    rgA[0].setDataValidation(SpreadsheetApp.newDataValidation().requireValueInRange(rgA[1]));
  }else{
    SpreadsheetApp.getUi().alert('Invalid RangeList. Only two ranges at a time please. The range to validate first and the range of values second.')
  }
}