How can I avoid a VLOOKUP Error that occurs when I delete a name?

Question

The pictures used are only from an example sheet! My basic problem is that I have a list called Assignment in which names appear (dropdown list). For Location (in the assignment sheet) I use the following formula: =IF(C2<>"",VLOOKUP(C2,'Input Data'!C$3:D$7,2,FALSE),"")

These names are assigned certain values, they are in the same line. The names are defined in a worksheet called Input Data!

If I now delete a name like Green, John from the Input Data worksheet, then I get the following error in another worksheet (Evaluation). (More than 40 people have access to this worksheet and randomly delete names)In this evaluation worksheet the values are evaluated by the following formula:

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF((IF($B$2="dontcare",1,REGEXMATCH(Assignment!$E$3:$E$577,$B$2 &"*")))*(IF($B$3="dontcare",1,(Assignment!$E$3:$E$577=$B$3)))*(IF($B$4="dontcare",1,(Assignment!$D$3:$D$577=$B$4)))*(IF($B$5="dontcare",1,(Assignment!$F$3:$F$577=$B$5)))*(IF($B$6="dontcare",1,(Assignment!$B$3:$B$577=$B$6))),(Assignment!S$3:S$577)))), 1, 1)

The following error appears in the evaluation sheet: Error: During the evaluation of VLOOKUP the value "Green, John" was not found.

How can I avoid this error? Is it possible to avoid this error with a macro that deletes Names from assignment sheet that are not in the Input data sheet? Do you have any ideas for a code?Maybe a Formula or perhaps a Macro? example sheet with explanation: https://docs.google.com/spreadsheets/d/1OU_95Lhf6p0ju2TLlz8xmTegHpzTYu4DW0_X57mObBc/edit#gid=1763280488

I found very difficult to understand the situation with so many tables and formulas. Could you please provide a sample Sheet so there is a clearer of example of what you are trying to achieve. — Raserhin, Dec 05 '19 at 16:59
The only was for poeple to test this is to actually see the error in action but nobody is going to waste the time building a sheet just to test this, so provide a sample sheet that people can use to test. "but the value is not permanent, it is variable" how is this variable set / selected? — James D, Dec 05 '19 at 19:18
I am not really able to reconstruct a sample sheet, the basic problem is just, that when a name gets deleted from the Input Data sheet, a VLOOKUP Error occurs like I described it — greenster10, Dec 06 '19 at 09:30
example sheet with explanation: https://docs.google.com/spreadsheets/d/1OU_95Lhf6p0ju2TLlz8xmTegHpzTYu4DW0_X57mObBc/edit#gid=1763280488 — greenster10, Dec 06 '19 at 10:12
So my only question is, how to write a script that deletes specific lines in the assignment list, if a line occurs with a name like Green, John, that is not in the input data list. short version: Example! Assignment sheet: line 7 Green, John -> check if Green, John is in sheet Input Data in row C! If Green, John is not in Input Data row C -> delete line 7 Green, John in Assignment! .... That is basically my question! – — greenster10, Dec 06 '19 at 10:38

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

If what you want to do is make sure that rows are deleted in a sheet when there are incorrect values you could try something like this in Apps Script:

function onEdit(e) {
  var spreadsheet = e.source;
  
  var assignment = spreadsheet.getSheetByName("Assignment");
  var assignmentRange = assignment.getDataRange();
  var assignmentNames = assignment.getRange(3, 2, assignmentRange.getNumRows());
  
  var inputData = spreadsheet.getSheetByName("Input Data");
  var inputDataRange = inputData.getDataRange();
  
  var i = 1;
  while(assignmentNames.getNumRows() > i){
    var currentCell = assignmentNames.getCell(i, 1);
    var txtFinder = inputDataRange.createTextFinder(currentCell.getValue());
    txtFinder.matchEntireCell(true);
    if(!txtFinder.findNext()){
      assignment.deleteRow(currentCell.getRow())
    }else{
      // We are only steping when no elements have been deleted
      // Otherwise we would skip rows due to shifting in row deletion
      i++;
    }
  }
}

Explanation

onEdit is a special function name in Apps Script that would execute every time it's parent sheet is modified.

After that we retrieve the spreadsheet from the event object

  var spreadsheet = e.source;

Now we get the relevant range in the Assignment sheet. Look at the usage of getDataRange documentation to avoid retrieving unnecessary cell values. And from that range we actually get the specific column we are interested on.

  var assignment = spreadsheet.getSheetByName("Assignment");
  var assignmentRange = assignment.getDataRange();
  var assignmentNames = assignment.getRange(3, 2, assignmentRange.getNumRows());

Now we do the same for the other sheet(Input Data):

  var inputData = spreadsheet.getSheetByName("Input Data");
  var inputDataRange = inputData.getDataRange();

Note: Here I'm not getting a specified column because I assume that the full name will not repeat in any other column. But if you want you could get the specified range as I have done at Assignment.

After that we want to look for specific values in the Assignment range that don't exist in the Input Data sheet, you should try the TextFinder.

For every name in Assignment you should create a TextFinder. I have also forced to make a whole cell match.

  var i = 1;
  while(assignmentNames.getNumRows() > i){
    var currentCell = assignmentNames.getCell(i, 1);
    var txtFinder = inputDataRange.createTextFinder(currentCell.getValue());
    txtFinder.matchEntireCell(true);

If txtFinder finds a value the findNext() will evaluate to true. In the other hand when the txtFinder does not find a value it will be null and evaluated to false.

    if(!txtFinder.findNext()){
      assignment.deleteRow(currentCell.getRow())
    }else{
      // We are only stepping forward when no elements have been deleted
      // Otherwise we would skip rows due to shifting in row deletion
      i++;
    }
  }
}

the name can be John too, it can be any name... but if the name is not in the sheet Input Data, the whole row with the not occuring name has to be deleted in Input Data! THis is a possible solution: — greenster10, Dec 06 '19 at 15:36
```function checkName() { var s1 = SpreadsheetApp.getActive().getSheetByName("Input Data"); var col1 = s1.getRange("C3:C").getValues(); var s2 = SpreadsheetApp.getActive().getSheetByName("Assignment"); var col2 = s2.getRange("C4:C").getValues(); for (var i=col2.length-1; i>=0; i--) { var found = false; for (var j=0; j — greenster10, Dec 06 '19 at 15:37
I changed my answer to solve your problem. Sorry for the confusion. — Raserhin, Dec 09 '19 at 09:37
It works fine! The only problem I have now is that the macro recognizes no upper and lower case letters. in my list are Full Names like "Michael, Ballack" in "Assignment" and for example "Michael, BALLACK" in "Input Data" (which is the same name) and in this case it deletes the content, but it shouldn't! Can you help me with that "upper and lower case letters" problem? — greenster10, Dec 09 '19 at 11:16
If you are using the solution I provided you can just add [`matchCase()`](https://developers.google.com/apps-script/reference/spreadsheet/text-finder.html#matchCase(Boolean)) right below the `matchEntireCell()` method. If you are comparing strings directly you could just [make them lowercase](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/toLowerCase) to make sure are working. — Raserhin, Dec 09 '19 at 11:21
https://stackoverflow.com/questions/59900660/creating-a-data-history-with-excel-vba-using-lastrow-time-stamp-and-workbook-sh could you please help me here? — greenster10, Jan 25 '20 at 09:39

How can I avoid a VLOOKUP Error that occurs when I delete a name?

1 Answers1

Explanation