I have a sheet where column A is "current ETA" and is updated periodically. I want column B to be "initial ETA" so that B takes the first value entered in A, then never changes.

Using standard IF statements I obviously run into circular reference issues because I can't do B1=IF(ISBLANK(B1),A1,"")

How can I do this?


You could use the Properties Service to store the initial ETA globally and then compare against it afterwards.
Write your function using the google scripts and use that function in your B1 cell.


Using Tools -> Script Editor you can write the function:

function storeETA(a) {
  if(a != 0) {
    if(!PropertiesService.getScriptProperties().getProperty('initialETA')) {
      PropertiesService.getScriptProperties().setProperty('initialETA', a);
  return PropertiesService.getScriptProperties().getProperty('initialETA');

This way you'll always have that initial data stored.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.