Get the Value of the Drop-Down-CodePudding

This is my first time to ask here in StackOverflow. Here's my problem:

enter image description here

I have a drop down list created using data validation. I want to get the value of the drop down so it will change the value of a cell.

Here's my code so far.

function onEdit()
{
  var s = SpreadsheetApp.getActiveSheet();

  if(s.getName() == "TEST")
  {
    var r = s.getActiveCell();

    if(r.getColumn() == 2)
    {
      var prevCell = r.offset(0, -1);
      var total = r.getRange(1,5);

      if(r.getValue() == "Add")
      {
        total = Number(total   prevCell);
      }

      if(r.getValue() == "Sub")
      {
        total = Number(total - prevCell);
      }
    }
  }
}

Any help would be much appreciated. Thank you.

I tried to check of for solutions online but didn't see anything.

CodePudding user response：

If I am understanding your goal correctly, you don't need a script to do this. A formula can accomplish this goal.

In E1, place this formula:

=SUM(FILTER(A2:A,B2:B="Add"))-SUM(FILTER(A2:A,B2:B="Sub")

If that doesn't work, you may be in an international locale that uses semicolons where the USA uses commas. In that case, try this version:

=SUM(FILTER(A2:A;B2:B="Add"))-SUM(FILTER(A2:A;B2:B="Sub")