Sunday, February 17, 2013

yogi_Compute The Latest Populated Row In Another Sheet And The Corresponding Date For A Specified Entry

                                          Google Spreadsheet   Post  #1032
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Feb 17, 2013
user Brad Matushewski said:(!category-topic/docs/spreadsheets/xRMGlS8PbKM)
Need help with 2 formulae 

Formula 1 (Which works), but want to improve...
=index('182847'!E:E, max(row('182847'!E:E)*('182847'!E:E<>ʺʺ)))
This formula returns the last value in a column (numeric only).  This formula resides in a "summary" sheet and is pulling this last value from another sheet.  The sheet name is 182847.  I in fact have several lines.  The value "182847" is stored in a column on the summary sheet as as such, I would like to be able to have the formula get the sheet name from a cell.  That way, when I copy the formula down to the next row, it will reference the next sheet in sequence.  In other words, cell A2 has "182847" and in A3 I have the formula listed above.  I want it to pull the sheet name (182847) from cell A2.
Formula 2 (I don't have anything yet)...but I will describe the problem.
Lets say column A as a list of sequential dates.
The cells in column B are mostly blank but every once in a while, it will have the work "OPEN" in it (let's say in B15, B30, B48, etc).  The remainder of the cells in B are blank.  What I want it to do (much like above where I have a summary sheet), is to have a formula that will go out to other sheets (182847, for example) and look at column B and return the LAST date (Column A) with which the corresponding row holds the value "OPEN".
A                  B
18-feb-13    OPEN
21-feb-13   OPEN
The formual should return "21-feb-13".
Any help would be appreciated...

following is a solution to a bit more generalized problem