Hi. I have a workbook with 400 sheets. Each sheet contains an area for log entries (A11:D510). (A11:A510) is a Y/N column,
(B11:B510) is the log date column. In cell C4 I want to display the latest log entry date, whose A column is marked Y. I was
thinking =LastPM(), as in:


Public Function LastPM()
LastPM = 0
i = 11
Do While i <= 510 And Cells(i, "B") 0
If Cells(i, "A") = "Y" Then
LastPM = Cells(i, "B")
End If
i = i + 1
Loop
End Function

Problem. Suppose the user adds a new log row entry. How can I get C4 to recalulate LatestPM()? What's the best way? Any advice
would be helpful...Dennis

On Mon, 13 Sep 2004 16:44:58 -0400, "Dennis Allen"
wrote:

Hi. I have a workbook with 400 sheets. Each sheet contains an area for log entries (A11:D510). (A11:A510) is a Y/N column,
(B11:B510) is the log date column. In cell C4 I want to display the latest log entry date, whose A column is marked Y. I was
thinking =LastPM(), as in:


Public Function LastPM()
LastPM = 0
i = 11
Do While i <= 510 And Cells(i, "B") 0
If Cells(i, "A") = "Y" Then
LastPM = Cells(i, "B")
End If
i = i + 1
Loop
End Function

Problem. Suppose the user adds a new log row entry. How can I get C4 to recalulate LatestPM()? What's the best way? Any advice
would be helpful...Dennis

Does it have to be a VBA solution?

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)

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




--ron

"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? At what place do I type it (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.

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? ....Dennis

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.


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

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


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.



--ron

"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

On Mon, 13 Sep 2004 23:47:50 -0400, "Dennis Allen"
wrote:

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.

First select all the sheets.

Then copy the formula from someplace, and ctrl-v; or type it in.

Then do the <ctrl<shift<enter thing.

If that doesn't work with 400 sheets, try selecting a smaller subset. I don't
have a 400 sheet workbook to test it on, but maybe you could do 50 or 100 at a
time.

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

If I type "volatile" into the help box in the upper right hand corner of the VB
Editor window, I get HELP for the VOLATILE property. How are you trying to
find it? You should also be able to type volatile into a module window, place
the cursor in the word, and type <F1.



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

Hmmm. I get the same. I don't understand it well enough to fix it, at this
time. Part of the problem is that the Cells property without a qualifier
refers to ActiveSheet. I'd bet the solution is simple. I'll think about it,
unless someone else comes up with an answer in the meantime.


--ron

Hi again. I'm stuck. Added Public Function LastPM(). Went and highlighted all 400 sheets and added to cell B4=LastPM(). When I
go to the first sheet I see 8/4/2004. When I jump to the next sheet I still see 8/4/2004. If I add an entry 9/1/2004, B4 will
display it, but jumping to the next sheet still displays 9/1/2004.

How can I get a VBA function to behave like an excel function? As I said, I don't understand excel inline formulas very
well...Dennis

On Mon, 13 Sep 2004 23:02:41 -0400, "Dennis Allen"
wrote:

Hi again. I'm stuck. Added Public Function LastPM(). Went and highlighted all 400 sheets and added to cell B4=LastPM(). When I
go to the first sheet I see 8/4/2004. When I jump to the next sheet I still see 8/4/2004. If I add an entry 9/1/2004, B4 will
display it, but jumping to the next sheet still displays 9/1/2004.

How can I get a VBA function to behave like an excel function? As I said, I don't understand excel inline formulas very
well...Dennis

OK, I think it has to do with how cells are referenced and DIM'd in Functions,
but I don't understand the logic well enough to really explain it. In any
event, the following UDF seems to behave sensibly, and still searches from the
bottom up, which should increase speed as the data gets larger.

You will have to specify the cell range of "y" 's in the function argument, but
you can specify an entire column, if you wish.

So you would write =LastPM(A:A) for example. Or =LastPM(a11:a1000)

===========================
Function LastPM(rg As Range)
Application.Volatile
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long, FirstRow As Long

NumRows = rg.Count
FirstRow = rg.Row

If rg.Cells(NumRows) = "" Then
LastRow = rg.Cells(NumRows).End(xlUp).Row - FirstRow + 1
Else
LastRow = NumRows
End If

For i = LastRow To 1 Step -1
If rg.Cells(i) = "y" Then
LastPM = rg.Cells(i, 2)
Exit Function
End If
Next i

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


--ron

That worked. Does passing the range also pass which worksheet the function is executing on? I was thinking we might have to pass
some sort of javascript "this"...Dennis

"Ron Rosenfeld" wrote in message ...
On Mon, 13 Sep 2004 23:02:41 -0400, "Dennis Allen"
wrote:

Hi again. I'm stuck. Added Public Function LastPM(). Went and highlighted all 400 sheets and added to cell B4=LastPM(). When I
go to the first sheet I see 8/4/2004. When I jump to the next sheet I still see 8/4/2004. If I add an entry 9/1/2004, B4 will
display it, but jumping to the next sheet still displays 9/1/2004.

How can I get a VBA function to behave like an excel function? As I said, I don't understand excel inline formulas very
well...Dennis

OK, I think it has to do with how cells are referenced and DIM'd in Functions,
but I don't understand the logic well enough to really explain it. In any
event, the following UDF seems to behave sensibly, and still searches from the
bottom up, which should increase speed as the data gets larger.

You will have to specify the cell range of "y" 's in the function argument, but
you can specify an entire column, if you wish.

So you would write =LastPM(A:A) for example. Or =LastPM(a11:a1000)

===========================
Function LastPM(rg As Range)
Application.Volatile
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long, FirstRow As Long

NumRows = rg.Count
FirstRow = rg.Row

If rg.Cells(NumRows) = "" Then
LastRow = rg.Cells(NumRows).End(xlUp).Row - FirstRow + 1
Else
LastRow = NumRows
End If

For i = LastRow To 1 Step -1
If rg.Cells(i) = "y" Then
LastPM = rg.Cells(i, 2)
Exit Function
End If
Next i

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


--ron

set rng = Application.Caller

will identify the cell containing a UDF used in a worksheet.

--
Regards,
Tom Ogilvy

"Dennis Allen" wrote in message
...
That worked. Does passing the range also pass which worksheet the
function is executing on? I was thinking we might have to pass
some sort of javascript "this"...Dennis

"Ron Rosenfeld" wrote in message
...
On Mon, 13 Sep 2004 23:02:41 -0400, "Dennis Allen"

wrote:

Hi again. I'm stuck. Added Public Function LastPM(). Went and
highlighted all 400 sheets and added to cell B4=LastPM(). When I
go to the first sheet I see 8/4/2004. When I jump to the next sheet I
still see 8/4/2004. If I add an entry 9/1/2004, B4 will
display it, but jumping to the next sheet still displays 9/1/2004.

How can I get a VBA function to behave like an excel function? As I
said, I don't understand excel inline formulas very
well...Dennis

OK, I think it has to do with how cells are referenced and DIM'd in
Functions,
but I don't understand the logic well enough to really explain it. In
any
event, the following UDF seems to behave sensibly, and still searches
from the
bottom up, which should increase speed as the data gets larger.

You will have to specify the cell range of "y" 's in the function
argument, but
you can specify an entire column, if you wish.

So you would write =LastPM(A:A) for example. Or =LastPM(a11:a1000)

===========================
Function LastPM(rg As Range)
Application.Volatile
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long, FirstRow As Long

NumRows = rg.Count
FirstRow = rg.Row

If rg.Cells(NumRows) = "" Then
LastRow = rg.Cells(NumRows).End(xlUp).Row - FirstRow + 1
Else
LastRow = NumRows
End If

For i = LastRow To 1 Step -1
If rg.Cells(i) = "y" Then
LastPM = rg.Cells(i, 2)
Exit Function
End If
Next i

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


--ron

On Tue, 14 Sep 2004 16:10:47 -0400, "Dennis Allen"
wrote:

That worked. Does passing the range also pass which worksheet the function is executing on? I was thinking we might have to pass
some sort of javascript "this"...Dennis

I'm glad it worked.

Are there any other issues?


--ron