title: How to automatically add the ordinal number of an entry based on timstamp to a row when non-contiguous new data is added to it in Google Sheets?
body: <p>How to make Google sheets automatically add the ordinal number of the current row based on the following criteria 'new entry on this particular row?</p>
  
  <p>How to make it work even when the data is not entered on a row contiguous basis (for example 1st entry on row 1 (say cell B1), 2nd entry on row 2 (in B2), 3rd entry on row 3 (B3), but then 4th entry on row 9 (B9), 5th on row 11 (B11), 6th on row (B7))?</p>
  
  <p>And not adding ordinals to intermediary empty rows (for example when  B5/row5, B6/row6, B8/row8, B10/row10 etc. are blanks, don't add ordinals to A5/row5, A6/row6, A8/row8, A10/row10, etc.)?</p>
  
  <p>Here's the expected result:</p>
  
  <p><a href="https://i.stack.imgur.com/GFnF0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GFnF0.png" alt="Ordinals to non-contiguous rows based on data entry timestamps"></a></p>
  
  <p>The Sheet:
  <a href="https://docs.google.com/spreadsheets/d/1whicrq53eeP2eQBGrkSZRMObztlvqAbpO7C2NjQ_OTs/edit?usp=sharing" rel="nofollow noreferrer">Ordinals to non-contiguous rows based on data entry timestamps</a></p>
  
  <p>So far I've come up with this pull down formula (to be pre-set in A2 and dragged down to A12):</p>
  
  <pre><code>=IF(B2&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A2]
  =IF(B3&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A3]
  =IF(B4&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A4]
  =IF(B5&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A5]
  =IF(B6&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A6]
  =IF(B7&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A7]
  =IF(B8&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A8]
  =IF(B9&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A9]
  =IF(B10&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A10]
  =IF(B11&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A11]
  =IF(B12&lt;&gt;"",MAX($A$2:$A)+1,"")  [in A12]
  </code></pre>
  
  <p>But the result I get when I enter then data succesively  one by one in B2, B3, B4, B7, B9, and B12, is that it updates all previous cells with the new max value so that the succesion isn't achieved.</p>
  
  <p>Here's a gif that shows the live result:</p>
  
  <p><a href="https://i.imgur.com/1I1Dk8f.gifv" rel="nofollow noreferrer">https://i.imgur.com/1I1Dk8f.gifv</a></p>
  
  <p>How to lock the previous results so that the ordinal count can proceed to the next entry when it occurs consecutively?</p>
  
  <p>I've consulted those post that are close to what I'm looking for:</p>
  
  <p><a href="https://webapps.stackexchange.com/questions/70270/how-do-you-replace-a-formula-with-its-result">How do you replace a formula with its result?</a></p>
  
  <p>using this script:</p>
  
  <pre><code>function freezeOutput(){
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SummarySheet");
    var range = sheet.getRange("A1:A10");     
    range.copyTo(range, {contentsOnly:true});
  }
  </code></pre>
  
  <p><a href="https://stackoverflow.com/questions/31013853/stop-excel-from-updating-formula-after-a-criteria-is-matched">Stop Excel from updating formula after a criteria is matched</a></p>
  
  <p>using this formula:</p>
  
  <pre><code>=IF(NOW() &lt; C1,2*A1,A2)
  </code></pre>
  
  <p>Alternatively this VBA code:</p>
  
  <pre><code>Function EvaluateIf(expression As String, condition As Boolean) As Variant
      Application.Volatile
      Dim myText As String
      Dim myVal As Variant
      If condition Then
          myVal = Application.Evaluate(expression)
      Else
          myText = Application.Caller.Text
          If IsNumeric(myText) Then
              myVal = Val(myText)
          Else
              myVal = myText
          End If
      End If
      EvaluateIf = myVal
  End Function
  
  
  Function FreezeAfter(expression As String, deadline As Date) As Variant
      Application.Volatile
      Dim myText As String
      Dim myVal As Variant
      If Now &gt; deadline Then
          myText = Application.Caller.Text
          If IsNumeric(myText) Then
              myVal = Val(myText)
          Else
              myVal = myText
          End If
      Else
          myVal = Application.Evaluate(expression)
      End If
      FreezeAfter = myVal
  End Function
  </code></pre>
  
  <p><a href="https://webapps.stackexchange.com/questions/72539/how-do-i-copy-cell-values-programatically/72542#72542?newreg=e15bf4e542fc4267b2a25f2dc82c5f47">How do I copy cell values programatically?</a></p>
  
  <p>using this script:</p>
  
  <pre><code>function getRangeValues() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getRange("A2:B4");
    var values = range.getValues();
    return values;
  };
  </code></pre>
  
  <p>With Sheet here:
  <a href="https://docs.google.com/spreadsheets/d/1o_HHaGHiR9Ci8NQwwjnC955Y_4BXzHxhlZPWruslmXw/edit?usp=sharing" rel="nofollow noreferrer">Copy cell values programmatically (72539)</a></p>
  
  <p>Lastly:</p>
  
  <p><a href="https://yagisanatode.com/2018/02/21/add-the-current-date-to-a-sheet-when-data-is-added-so-that-the-date-does-not-changestatic-google-sheets/" rel="nofollow noreferrer">Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets</a></p>
  
  <p>using this script:</p>
  
  <pre><code>/**
  * Creates a Date Stamp if a column is edited.
  * https://yagisanatode.com/2018/02/21/add-the-current-date-to-a-sheet-when-data-is-added-so-that-the-date-does-not-changestatic-google-sheets/
  */
  
  //CORE VARIABLES
  // The column you want to check if something is entered.
  var COLUMNTOCHECK = 6;
  // Where you want the date time stamp offset from the input location. [row, column]
  var DATETIMELOCATION = [0,-5];
  // Sheet you are working on
  var SHEETNAME = 'Sheet3'
  
  function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we're on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) { 
      var selectedCell = ss.getActiveCell();
      //checks the column to ensure it is on the one we want to cause the date to appear.
      if( selectedCell.getColumn() == COLUMNTOCHECK) { 
        var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[8]);
        dateTimeCell.setValue(new Date());
        }
    }
  }
  </code></pre>
  
  <p>But I don't see how to make it work.</p>
  
  <p>The expected result shoud be simply add ordinal number to corresponding cell in column A upon entering data on corresponding cell in column B.</p>
  
  <p>How do we do that?</p>