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     www.energyefficientbuild.com.    Feb 17, 2013
user Brad Matushewski said:(http://productforums.google.com/forum/?zx=vcocodfjctot#!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".

 

e.g.

 

A                  B

 

17-feb-13  

18-feb-13    OPEN

19-feb-13

20-feb-13

21-feb-13   OPEN

22-feb-13

 

The formual should return "21-feb-13".

 

Any help would be appreciated...

--------------------------------------------------------------------------------------------------------

following is a solution to a bit more generalized problem