"Ron Rosenfeld" wrote in message ...
On Mon, 13 Sep 2004 22:30:09 -0400, "Dennis Allen"
wrote:

"Ron Rosenfeld" wrote in message ...
On Mon, 13 Sep 2004 16:44:58 -0400, "Dennis Allen"
wrote:


Does it have to be a VBA solution?

No, but I can understand VB code.

If the log entries are entered in order, and if there is no entry in A without
a corresponding entry in B, then the array formula (entered with
<ctrl<shift<enter) would give you that result:

=INDEX(B11:B60000,MAX((A11:A60000="y")*ROW(INDIREC T("11:60000")))-10)

This is what I mean. I don't understand any of it. What is <ctrl<shift<enter?

You should read about Array formulas in HELP for Excel. But it means that
after you type or paste in the formula, you simultaneously hold down the <ctrl
key and the <shift key while you are depressing the <enter key. Unlike a
non-array formula where you only depress the <enter key.


At what place do I type it.

You type it when you would normally just hit <enter after you have entered a
formula.

Oh, on the formula line. I was <ctrl-<v when the cursro needed to be at the end of the line. Now, how can I add the formula to
all 400 sheets. If I highlight all sheets and try a <ctrl-<v I get "cannot empty clipboard" 400 times.



(I've tried on my sheet and nothing happens). I tried putting the =INDEX() formula.where I want the latest date and I get
#VALUE! I'm not sure how the formula works.


A11:A60000="y" produces an array that compares the contents of each cell in the
range to "y", and returns TRUE if present, FALSE if not.

Row(Indirect("11:60000") produces an array consisting of the numbers 11 to
60000 inclusive.

These two arrays are multiplied together. When TRUE is used in a
multiplication, Excel coerces it to a one (1). So we wind up with an array of
0's and 1's being multiplied by numbers 11 to 60000. This will result in an
array that might look something like {0,0,13,14,0,0,0,18...} depending on which
rows have y's.

The MAX function picks out the highest number in that array. That will
correspond to the highest numbered row that contains a 'y'.

That number is then used as the Row argument in the INDEX function.

I'm beginning to understand.

======================


In your function, again assuming you have nothing below the data entry area,
instead of going from the top cell down, I would go from the bottom cell up
looking for the first entry (from the bottom) that has a "y". That would be
quicker than going down from the top, and would take care of the problem of
adding rows.

For example:

===================================
Function LastPM()
Application.Volatile
Dim i As Long
Dim LastPMcell As Range

Set LastPMcell = [A65535].End(xlUp)

i = LastPMcell.Row

Do While Cells(i, 1) < "y"
i = i - 1
Loop

LastPM = Cells(i, 2)

End Function
===========================

Application.Volatile. I assume it executes the function anytime anything changes in the sheet, correct? Where would I find find
documentation to it?

Documentation is in HELP for VBA.

Haven't found it yet. But now I'm having troubles running LastPM() on all 400 sheets. If I jump from sheet to sheet, B4 which has
=LastPM(), displays the last sheet's value. Unless I add a log, then that value stays up...Dennis