G-Sheets: Populate multiple cells from another sheet based on dropdown (multiple rows and columns)

Question

I am trying to copy or populate (not sure on the correct terminology) data from multiple cells to the same sized multiple cells on another sheet based on the dropdown.

https://docs.google.com/spreadsheets/d/1ELxYcMG_kvzt6KLZoY8WyTBb3evv4dOmpYWvTlzvEVM/edit?usp=sharing

So in this spreadsheet, I'm trying to select A2 & A8 in "Results" and populate B2:I6 & B8:I12 based on the relevant rows/columns in "Data"

Is this possible?

hi @Matt did you get the chance to see my answer, did it helped? — Kresimir Pendic
@oserk I've had a go. It looks like it works on the first drop down. But the 2nd doesn't respond. The goal is to set up multiple dropdowns on 1 spreadsheet. So I'll have say 6 dropdowns in column A, then 6 in N and 6 in Z. — Matt Ashbolt
I made example for you to see how my code works, specifically on line if( range.getValue() == "AAA" ){.. => that's where we define what data goes where,, you have to expand that if-else to your needs and do custom code ,, but as you see it's possible. mark this as answer if you feel like, and if you need more specific details ask new question then — Kresimir Pendic
@oserk I've got it working - I understand it now. Say I've got 6 dropdowns in column A. And for each dropdown I have 20 different options. And then I have the same options in 6 dropdowns in column F and M. Do I have to write the script effectively 6 (dropdowns) x 20 (options) x 3 (columns) times? — Matt Ashbolt

Kresimir Pendic Kresimir Pendic · Accepted Answer · 2017-04-20T07:52:36

I've done example for you to get you going, you need to implement how to ref triggering cell and get it's ref row index to do copyTo to that row index, not hardcode it like I did ..

This is the relevante code:

function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
  var range = e.range;  

  if(  range.getA1Notation() == "A2" ){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[1];
    var orgsheet = ss.getSheets()[0];
    if( range.getValue() == "AAA" ){
      sheet.getRange("B2:H6").copyTo(orgsheet.getRange("B2"), {contentsOnly:true});
    } else if ( range.getValue() == "BBB" ) {
      sheet.getRange("B8:H12").copyTo(orgsheet.getRange("B2"), {contentsOnly:true});
    }
  }

}

also, I think that you need to copy my sheet to your drive to be able to run the script without permission errors,

this is link to my sheet: https://docs.google.com/spreadsheets/d/1YVuQMBLEqgwGMPdjlV9cKRCML0vqi3UkMsZ66DR5Fls/edit#gid=0

cheers, k

G-Sheets: Populate multiple cells from another sheet based on dropdown (multiple rows and columns)

1 Answers