CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Kyle D
Kyle D

Reputation: 3

Sheets IF Function setup and/or formatting errors

Cell C5 on the Trip Assignment Form tab is a dropdown selection list of destinations. I am trying to make sheets automatically fill in the C6 cell with the matching address (coming from a tab called Schools). When I put the below formula in I get an error or FALSE but I cannot figure out why.

=if(C5=Schools!A1, C6=Schools!B1, "Other Error")

I need to make the formula work for any destination selected so that the appropriate address is inserted into the cell. Can I do this like the way I am thinking or would Apps Script be better and if so how would I go about that?

"Trip Assignment Form" enter image description here

"Schools" data sheet

enter image description here

Link to copy of my sheet:

https://docs.google.com/spreadsheets/d/1Xkwxjtf6syEd-zGaPUVsyE2_6Kfczu8c-8W3RuRg9CY/edit?usp=sharing

Upvotes: 0

Views: 54

Answers (2)

TheMaster
TheMaster

Reputation: 50472

=VLOOKUP(C5, Schools!A:B, 2, 0)
  • Lookup C5 in Schools A:B and retrieve the second column

Upvotes: 0

Cooper
Cooper

Reputation: 64082

It is possible to do with Google Apps Script but it's probably not the easiest way to go

You can use the onEdit trigger and this sort of code:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()=='Sheet12' && e.range.columnStart==1 && e.range.rowStart==1 & e.value!='') {
    var ss=SpreadsheetApp.getActive();
    var sh1=ss.getSheetByName('Sheet12');
    var v1=sh1.getRange(1,1).getValue();
    var sh2=ss.getSheetByName('Sheet13');
    var v2=sh2.getRange(1,1,sh2.getLastRow(),2).getValues();
    for(var i=0;i<v2.length;i++) {
      if(v2[i][0]==v1) {
        sh1.getRange(2,1).setValue(v2[i][1]);
      }
    }
  }
}

Animation:

enter image description here

Your setup:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()=='assignment sheet name' && e.range.columnStart==3 && e.range.rowStart==5 & e.value!='') {
    var ss=SpreadsheetApp.getActive();
    var sh1=ss.getSheetByName('assignment sheet name');//assignment sheet name
    var v1=sh1.getRange(e.range.rowStart,e.range.columnStart).getValue();
    var sh2=ss.getSheetByName('schools sheet name');//schools sheet name
    var v2=sh2.getRange(1,1,sh2.getLastRow(),2).getValues();
    for(var i=0;i<v2.length;i++) {
      if(v2[i][0]==v1) {
        sh1.getRange(6,3).setValue(v2[i][1]);
      }
    }
  }
}

Upvotes: 1

Related Questions