Hi. I have a workbook with 400 sheets. Each sheet contains an area for log entries (A11:D510). (B11:B510) has the log dates. In
one cell I want to display the latest log entry date. I was thinking B4=LatestFix(), as in:


Public Function LastFix()
LastFix = 0
i = 11
Do While i <= 510 And Cells(i, "B") 0
LastFix = Cells(i, "B")
i = i + 1
Loop
End Function


Problem. Suppose the user adds a new entry. How can I get B4 to recalulate LatestFix()? What's the best way? I was thinking
Worksheet_Change(), but I have 400 sheets. Unless you can somehow select all sheets and edit all their Worksheet_Change() at once,
that's a lot of work. Any advice would be helpful...Dennis

Hi
use
=LOOKUP(2,1/(B11:B5100),B11:B510)

no need for VBA

--
Regards
Frank Kabel
Frankfurt, Germany

"Dennis Allen" schrieb im Newsbeitrag
...
Hi. I have a workbook with 400 sheets. Each sheet contains an area
for log entries (A11:D510). (B11:B510) has the log dates. In
one cell I want to display the latest log entry date. I was thinking
B4=LatestFix(), as in:


Public Function LastFix()
LastFix = 0
i = 11
Do While i <= 510 And Cells(i, "B") 0
LastFix = Cells(i, "B")
i = i + 1
Loop
End Function


Problem. Suppose the user adds a new entry. How can I get B4 to
recalulate LatestFix()? What's the best way? I was thinking
Worksheet_Change(), but I have 400 sheets. Unless you can somehow
select all sheets and edit all their Worksheet_Change() at once,
that's a lot of work. Any advice would be helpful...Dennis

Well, That's just one case. I'll need to add other functions, such as:

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

"Frank Kabel" wrote in message ...
Hi
use
=LOOKUP(2,1/(B11:B5100),B11:B510)

no need for VBA

--
Regards
Frank Kabel
Frankfurt, Germany

"Dennis Allen" schrieb im Newsbeitrag
...
Hi. I have a workbook with 400 sheets. Each sheet contains an area
for log entries (A11:D510). (B11:B510) has the log dates. In
one cell I want to display the latest log entry date. I was thinking
B4=LatestFix(), as in:


Public Function LastFix()
LastFix = 0
i = 11
Do While i <= 510 And Cells(i, "B") 0
LastFix = Cells(i, "B")
i = i + 1
Loop
End Function


Problem. Suppose the user adds a new entry. How can I get B4 to
recalulate LatestFix()? What's the best way? I was thinking
Worksheet_Change(), but I have 400 sheets. Unless you can somehow
select all sheets and edit all their Worksheet_Change() at once,
that's a lot of work. Any advice would be helpful...Dennis

Hi
use the range for which you want to calculate as parameter (Though I
still think you're better with using worksheet formulas)

--
Regards
Frank Kabel
Frankfurt, Germany

"Dennis Allen" schrieb im Newsbeitrag
...
Well, That's just one case. I'll need to add other functions, such
as:

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

"Frank Kabel" wrote in message
...
Hi
use
=LOOKUP(2,1/(B11:B5100),B11:B510)

no need for VBA

--
Regards
Frank Kabel
Frankfurt, Germany

"Dennis Allen" schrieb im Newsbeitrag
...
Hi. I have a workbook with 400 sheets. Each sheet contains an
area
for log entries (A11:D510). (B11:B510) has the log dates. In
one cell I want to display the latest log entry date. I was
thinking
B4=LatestFix(), as in:


Public Function LastFix()
LastFix = 0
i = 11
Do While i <= 510 And Cells(i, "B") 0
LastFix = Cells(i, "B")
i = i + 1
Loop
End Function


Problem. Suppose the user adds a new entry. How can I get B4 to
recalulate LatestFix()? What's the best way? I was thinking
Worksheet_Change(), but I have 400 sheets. Unless you can somehow
select all sheets and edit all their Worksheet_Change() at once,
that's a lot of work. Any advice would be helpful...Dennis

??? I don't understand.

If I stick =LastPM() in cell C4, if any change occurs in either cells A11:A510 or B11:B510 then C4 needs to be refreshed. How do I
do that for 400 sheets?

"Frank Kabel" wrote in message ...
Hi
use the range for which you want to calculate as parameter (Though I
still think you're better with using worksheet formulas)

--
Regards
Frank Kabel
Frankfurt, Germany

"Dennis Allen" schrieb im Newsbeitrag
...
Well, That's just one case. I'll need to add other functions, such
as:

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

"Frank Kabel" wrote in message
...
Hi
use
=LOOKUP(2,1/(B11:B5100),B11:B510)

no need for VBA

--
Regards
Frank Kabel
Frankfurt, Germany

"Dennis Allen" schrieb im Newsbeitrag
...
Hi. I have a workbook with 400 sheets. Each sheet contains an
area
for log entries (A11:D510). (B11:B510) has the log dates. In
one cell I want to display the latest log entry date. I was
thinking
B4=LatestFix(), as in:


Public Function LastFix()
LastFix = 0
i = 11
Do While i <= 510 And Cells(i, "B") 0
LastFix = Cells(i, "B")
i = i + 1
Loop
End Function


Problem. Suppose the user adds a new entry. How can I get B4 to
recalulate LatestFix()? What's the best way? I was thinking
Worksheet_Change(), but I have 400 sheets. Unless you can somehow
select all sheets and edit all their Worksheet_Change() at once,
that's a lot of work. Any advice would be helpful...Dennis

Oh, question. Hoes does =LOOKUP(2,1/(B11:B5100),B11:B510) work? I don't understand what 1/(B11:B5100) does...Dennis

Hi
as said: I would use a worksheet function for this. Esp. if you need
400 of these functions. UDFs are quite slow. What do you want to
calculate exactly on these 400 sheets

--
Regards
Frank Kabel
Frankfurt, Germany

"Dennis Allen" schrieb im Newsbeitrag
...
??? I don't understand.

If I stick =LastPM() in cell C4, if any change occurs in either cells
A11:A510 or B11:B510 then C4 needs to be refreshed. How do I
do that for 400 sheets?

"Frank Kabel" wrote in message
...
Hi
use the range for which you want to calculate as parameter (Though
I
still think you're better with using worksheet formulas)

--
Regards
Frank Kabel
Frankfurt, Germany

"Dennis Allen" schrieb im Newsbeitrag
...
Well, That's just one case. I'll need to add other functions,
such
as:

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

"Frank Kabel" wrote in message
...
Hi
use
=LOOKUP(2,1/(B11:B5100),B11:B510)

no need for VBA

--
Regards
Frank Kabel
Frankfurt, Germany

"Dennis Allen" schrieb im Newsbeitrag
...
Hi. I have a workbook with 400 sheets. Each sheet contains an
area
for log entries (A11:D510). (B11:B510) has the log dates. In
one cell I want to display the latest log entry date. I was
thinking
B4=LatestFix(), as in:


Public Function LastFix()
LastFix = 0
i = 11
Do While i <= 510 And Cells(i, "B") 0
LastFix = Cells(i, "B")
i = i + 1
Loop
End Function


Problem. Suppose the user adds a new entry. How can I get B4
to
recalulate LatestFix()? What's the best way? I was thinking
Worksheet_Change(), but I have 400 sheets. Unless you can
somehow
select all sheets and edit all their Worksheet_Change() at once,
that's a lot of work. Any advice would be helpful...Dennis