How can I auto-populate a range from another tab based on a Data Validation selection in Google Sheets?

Question

I have been looking for info about this for sometime, but nothing seems to work. I hope I'm going to explain this right, but here goes... I have a data validation setup (cell G31 on 'Fellowship Tracking' tab.) Based on whatever option is selected, I want it to pull data from a certain range on another tab in the same sheet ('ALL Stages+Pit'.) I put a link to the sheet at the bottom.

For an example, on the 'Fellowship Tracking' tab, cell G31, let's say I select the option Stage 1. Then starting in cell C33, I would like it to auto-populate the data from cells B3:G21 from the 'ALL Stages+Pit' tab. In addition, repeat the same for Stage 2 option pull from 'Fellowship Tracking', cells H3:M21 and Stage 3 option pull from 'Fellowship Tracking', cells N3-S21 and auto-populate starting in cell C33 like Stage 1 data. The 'Pit' option, I would like to pull data from the 'PIT Sheet' tab from cells A2-F17 and auto-populate starting in cell C33. So basically whatever option is chosen, it pulls the data from the ranges mentioned above and auto-populates the respective data starting in cell C33.

I hope I explained all that enough. I would to get info on how to do all that or is this not possible because of the way the data is setup?

Sheet Link: https://docs.google.com/spreadsheets/d/1KYSiVggIm7KIKxpJMnUhldrzsIaETCqyjOWJ1a9k1cI/edit?usp=sharing

Images:

'Fellowship Tracking' tab

'ALL Stages+Pit' tab: Stages data

'ALL Stages+Pit' tab: Pit data

If you will provide me with images of the tabs involved I'd be glad to assist you. Yes Images because I will not follow links to spreadsheets off of this site. — Cooper, Feb 20 '20 at 20:52
@Cooper - I added the image files. I hope those are what you were asking to see? — pixxidust, Feb 20 '20 at 21:04
Do you have any other onEdit() functions either simple or installable? — Cooper, Feb 20 '20 at 21:19
@Cooper - I don't know what those are, so I'm going to say probably not. — pixxidust, Feb 20 '20 at 21:24
Is okay if I show you how to move one and then you have to do the rest of them. Is that okay? — Cooper, Feb 20 '20 at 21:26
@Cooper - Yea that's okay. That's what I planned on doing ^_^ — pixxidust, Feb 20 '20 at 21:27
So I'm going to build an onEdit() function that monitors G31 of 'Fellowship Track' and moves 'ALL Stages+Pit'B3:G21 to C33 of 'Fellowship Tracking' is that correct? — Cooper, Feb 20 '20 at 21:31

score 1 · Answer 1 · answered Feb 20 '20 at 21:55

1

Copying a range from one sheet to another sheet within the same spreadsheet

Install the script into the script editor.
You cannot run this script from the script editor because it requires the onEdit trigger event object.
I've added a script to make this an installable trigger. If you run the function named 'creatOnMyEditTrigger()' just once to create only one trigger then the function should start responding whenever a user edits G31 and the value is equal to 'Stage 1';

The code:

function onMyEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!='Fellowship Tracking')return;
  if(e.range.columnStart==7 && e.range.rowStart==31 && e.value=='Stage 1') {
    var srcrg=e.source.getSheetByName('ALL Stages+Pit').getRange('B3:G2');
    var desrg=sh.getRange('C33');
    srcrg.copyTo(desrg);
  }
}

function createOnMyEditTrigger() {
  var ss=SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onMyEdit').forSpreadsheet(ss.getId()).onEdit().create();
}

answered Feb 20 '20 at 21:55

Cooper

59,616
6
23
54

1. I found out the sheet does have an onEdit script for a date/timestamp when installing the one you gave. I don't know if that's why I cant get it to work. If not, then, 2. I installed the script above and input the function 'createOnMyEditTrigger(G31)' into a cell. The cell says it loads, but when I change cell G31, nothing happens. – pixxidust Feb 20 '20 at 22:23
None of the functions I have create can be used for cell functions. In addition G31 is a validation cell isn't it? – Cooper Feb 20 '20 at 22:26
Oh, my bad. I misunderstood when you said run the function then. Yea, G31 a validation cell. So when you say run the function, what did you mean by that? – pixxidust Feb 20 '20 at 22:30
Run it from the script editor. Or if you wish you can go create the trigger manually – Cooper Feb 20 '20 at 22:31
Okay, I figured it out. However, when the range populates upon being triggered, the formulas in those cells no longer work. Any recommendation on how to get them to still work? – pixxidust Feb 20 '20 at 22:53
You didn't say anything about formulas in those cells – Cooper Feb 20 '20 at 22:55
Can you explain further? – Cooper Feb 20 '20 at 22:55
Sorry, didn't think about that. Still learning Sheets. The formula is 'hlookup($F$3,'Stage 1'!$E$34:$H$50,2,false)'. I changed the formula to 'hlookup('ALL Stages+Pit'!$F$3,'Stage 1'!$E$34:$H$50,2,false) then it came back. So my guess is to change each formula to include the tab name? – pixxidust Feb 20 '20 at 23:01
Yes @pixxidust that is right, you need to specify the sheet name in all your formulas to solve the issue. – Mateo Randwolf Feb 24 '20 at 11:39

score 0 · Accepted Answer · answered Feb 24 '20 at 11:50

Solution

Here is the full clarified code for solving this issue. This solution is brought to you by @Cooper who set up the path to follow for completing the rest of the required features.

function onMyEdit(e) {
  // gets the sheet changes have been made
  var sh=e.range.getSheet();
  // only look for changes on Fellowship Tracking as there is where we will be changing our "stages" cell
  if(sh.getName()!='Fellowship Tracking')return;
  
  // If Stage 1 is selected
  if(e.range.columnStart==7 && e.range.rowStart==31 && e.value=='Stage 1') {
  // get the right range from the right sheet for stage1 
    var srcrg=e.source.getSheetByName('ALL Stages+Pit').getRange('B3:G21');
  // get range where we want to populate the information
    var desrg=sh.getRange('C33');
 // populate the information
    srcrg.copyTo(desrg);
  }
  
    // If Stage 2 is selected
  if(e.range.columnStart==7 && e.range.rowStart==31 && e.value=='Stage 2') {
    var srcrg=e.source.getSheetByName('ALL Stages+Pit').getRange('H3:M21');
    var desrg=sh.getRange('C33');
    srcrg.copyTo(desrg);
  }  
  
   // If Stage 3 is selected
  if(e.range.columnStart==7 && e.range.rowStart==31 && e.value=='Stage 3') {
    var srcrg=e.source.getSheetByName('ALL Stages+Pit').getRange('N3:S21');
    var desrg=sh.getRange('C33');
    srcrg.copyTo(desrg);
  }  
  
   // If PIT is selected
  if(e.range.columnStart==7 && e.range.rowStart==31 && e.value=='PIT') {
    var srcrg=e.source.getSheetByName('PIT Sheet').getRange('A2:F17');
    var desrg=sh.getRange('C33');
    srcrg.copyTo(desrg);
  }    
  
}

// run this function to start the auto-complete programme
function createOnMyEditTrigger() {
  var ss=SpreadsheetApp.getActive();
// runs the function above as an onEdit function on the specfic spreadhseet
  ScriptApp.newTrigger('onMyEdit').forSpreadsheet(ss.getId()).onEdit().create();
}

You can check the reference for further information in how to catch changes in sheets with onEdit() here

How can I auto-populate a range from another tab based on a Data Validation selection in Google Sheets?

2 Answers2

Solution