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     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