## Tuesday, October 8, 2013

### yogi_Compute Latest (and Earliest) Non-Blank Score For Each Of Categories 1 To 5 From Maximum Of 20 Tests

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 08, 2013

'Hello Yogi,

Thanks so much for the blog post.  I had a little trouble following it (and it might have been my fault as I've been altering the spreadsheets since I posted this yesterday).

I do have a new question though that I'm hoping you might be able to answer.

I've got an ordered series of sheets containing mixed scores (some numeric, some text).  I'd like a function that can pull the most recent score (whatever that may be) from the sheets.  The problem is that some sheets will have blanks and others will have data, so I can't just reference one sheet.  I was trying to write my own function to do this, but I'm not sure if that's the best approach.  This is the code that I'm working on so far using Google API.

function PullRecent() {  // Pull most recent score from the sheets

var average = spreadsheet.getSheets()[0]; // get the average page
var cell = average.getActiveCell(); // get the active cell that I want the data written to
var cellR = average.getRow(cell); // get the row of this cell
var cellC = average.getColumn(cell); // get the column of this cell

// pull all of the rest of the sheets in the spreadsheet

// what I hope this next formula would do would be to go through each sheet until it finds a non-blank value
for(var r=1; assignmentdata!="" ;r++){
var assignmentdata = assignmentsheet.getRange(cellR,cellC,1,1).getValue();
if(assignmentdata !=""){
return assignmentdata;
break;
}

}
}

Any suggestions and help would be most welcome.

Thanks,
Phil
-------------------------------------------------------------------------------------------------------------------------------------------------------
in the following I present a formula based solution to a bit more generalized problem