Google Sheets – How to Automatically Update a Cell with the Most Recent Entry in the Same Row

google sheetsgoogle-apps-script

I am trying to have one column that will show the most recent thing i entered in the row. Example: I want B2 to be the same as D2 but once I enter something in E2 I want B2 to display that. And then if I entered something in F3 it would then show in B2 and so on and so fourth. I figured out how to do it with one cell doing a "=index(d2)" and tried something like "=index(D2:L2) for the sequence but it only shows d2 or gets upset and causes errors. I hope that makes sense. It seems simple enough but after researching it I've been unable to find anything. It is mainly text that I am trying to display and not numbers.

Best Answer

The following little script will add each change you make, in the active row, to column B:

function onEdit() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = sh.getActiveCell().getRowIndex();
  var value = sh.getActiveCell().getValue();
  sh.getRange(row, 2).setValue(value);
}

See example file I created, you you to play around with: onEdit change

Goto Tools, Script Editor, from the menu and add the script. Make sure to press the two buttons:
enter image description here

Related Solutions

Google Sheets Timestamp – Automatically Set ‘Last Updated’ Cell

You can try adding a Google Apps Script to capture when a cell is edited and add a timestamp to a different cell. Here's a previous answer that is similar: Google Spreadsheet Timestamp?

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 13 ) { //checks the column
     var nextCell = r.offset(0, 1);
     if( nextCell.getValue() === '' ) //is empty?
       var time = new Date();
       time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
       nextCell.setValue(time);
   };
 };
}

Google Sheets – How to Get Average of Last Four Values in a Row

Try using OFFSET to get the range and AVERAGE function to average, i.e. like this for row 2:

=iferror(average(offset(C2,0,max(0,count(C2:Z2)-4),1,4)),0)

If there are fewer than 4 values, that will average them all, otherwise just the last 4—if there are no values you get 0. You can change that to a blank by replacing 0 at the end of the formula with "".

This formula works in both Excel and Google Spreadsheets.

Best Answer

Related Solutions

Google Sheets Timestamp – Automatically Set ‘Last Updated’ Cell

Google Sheets – How to Get Average of Last Four Values in a Row

Related Topic