I am using checkbox on the sheet to trigger the script such that data in the dropdown get saved to the neighboring sheet. Requirement is if the entry is done of the same SRF number, then it needs to be saved with the suffix. For E.g. if SRF No 'AS/SRF-004' is submitted again, it should save as 'AS/SRF-004/Rev' and if repeats again then 'AS/SRF-004/Rev-2' and so on.

Don't know whether it is possible or silly for me to ask this question.

CodePudding user response：

When I saw your provided Spreadsheet, I found your current script. Although this is hidded in your question, I thought that you might want to use this script. So in this answer, I would like to propose to modify your current script.

From your question and your reply, I understood your goal is as follows.

• When the values of "C6" and "C8" are set and the checkbox is checked in "SRF Allotment" sheet, you want to put the values to the columns "A" and "B" of "Helper(Assign Status-from down)" sheet. At that time, you want to check the number of value of "C6" in the column "A" and put the value of /REV-# as the suffix.
• You want to achieve this by modifying your current script.

If my understanding of your goal is correct, how about the following modification?

Modified script:

function onEdit(e) {
  var sh1 = e.source.getActiveSheet();
  var r = e.range;
  if (sh1.getName() == 'SRF Allotment' && r.getA1Notation() == 'C9' && r.getValue() === true) {
    var sh2 = e.source.getSheetByName('Helper(Assign Status-from down)');
    var v = r.offset(-3, 0).getValue();
    var n = sh2.getRange("A2:A"   sh2.getLastRow()).createTextFinder(v).matchEntireCell(true).findAll().length;
    var values = [v, r.offset(-1, 0).getValue(), null, null, `${v}/REV-${n   1}`];
    sh2.appendRow(values);
    r.uncheck();
    e.source.getSheetByName('SRF Allotment').getRangeList(["C6", "C8"]).clearContent();
  }
}

• In this modification, the value of "C6" of "SRF Allotment" sheet is searched from the column "A" of Helper(Assign Status-from down). Using this result, the suffix is added like ${v}/REV-${n 1}. I used TextFinder for searching the value.

Note:

• In your question, it seems that you want to use AS/SRF-004/Rev-2. On the other hand, in your reply, it seems that you want to use AS/SRF-004/Rev and AS/SRF-001/REV-01. From this situation, I couldn't understand your actual suffix. So I used ${v}/REV-${n 1}. If this was not your expected result, please modify it to your expected one.

Reference:

• createTextFinder(findText) of Class Range