Google-sheets – Dynamic offset from last value found

google sheets

I'm trying to reference a cell five rows up, after finding the last value of the column like so:

=FILTER(quarterly2!B:B , ROW(quarterly2!B:B ) =MAX( FILTER( ROW(quarterly2!B:B ) , (ISNUMBER(quarterly2!B:B )))))

How do I return the value that is fifth last from the bottom of this column B?

Best Answer

OP has revisited but not responded to a request for clarification, so I'm guessing inserting -5 immediately after FILTER( ROW(quarterly2!B:B ) was deemed adequate.

Code

function autoAddRows() {
  var sh = SpreadsheetApp.getActiveSheet();
  var cRow = sh.getActiveRange().getRowIndex(), mRow = sh.getMaxRows();

  if((mRow-cRow) < 1) {
    var lCol = sh.getLastColumn(), key = sh.getRange(mRow,lCol).getValue();
    sh.insertRowAfter(mRow), sh.getRange(mRow+1,lCol).setValue(key+1);
  }
}

Remarks

var ss isn't necessary, since you don't use it in the script.
you need the row of the active cell, compared to the last row (getMaxRows)
add all the goodies into the IF statement that need to be executed, when mRow-cRow=0

Example

I've prepared a file for you to experiment with: Inserting Rows + Reference

Best Answer

Related Solutions

Google Sheets – How to Return an Offset Cell Value for Last Occurrence

Google Sheets – Inserting Rows and Adding Cell References

Code

Remarks

Example

Related Topic