## Sunday, February 17, 2013

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 17, 2013
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