Recalculate cell #2

[Dennis Allen]

Hi. I have a workbook with 400 sheets. Each sheet contains an area for log entries (A11510). (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

 

[Ron Rosenfeld]

Hi. I have a workbook with 400 sheets. Each sheet contains an area for log entries (A11510). (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(INDIRECT("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

 

[Dennis Allen]

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(INDIRECT("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

 

[Ron Rosenfeld]

No, but I can understand VB code.


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

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

 

[Dennis Allen]

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

 

[Dennis Allen]

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.



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.

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

 

[Ron Rosenfeld]

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.

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

Documentation is in HELP for VBA.

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

 

[Ron Rosenfeld]

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

 

[Dennis Allen]

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

 

[Tom Ogilvy]

set rng = Application.Caller

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

--
Regards,
Tom Ogilvy

That worked. Does passing the range also pass which worksheet the

function is executing on? I was thinking we might have to pass

 

[Ron Rosenfeld]

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

 

[Dennis Allen]

I managed to get the formulas in for all 400 sheets. I need to beat on it, but seems to be working. Thanks.

I'm a tad worried about the refresh rate. Everytime I add a log entry row, I have the hourglass for a few moments. And that's with
a 2.8Gig Pentium4. On an older machine or network, will it be unbearably slow? Is it recalculating all 400 worksheets?

 

[Ron Rosenfeld]

I managed to get the formulas in for all 400 sheets. I need to beat on it, but seems to be working. Thanks.

I'm a tad worried about the refresh rate. Everytime I add a log entry row, I have the hourglass for a few moments. And that's with
a 2.8Gig Pentium4. On an older machine or network, will it be unbearably slow? Is it recalculating all 400 worksheets?

Are you using the worksheet function approach or the VBA approach?

If the latter, you could try removing the "application.volatile" line. The
disadvantage is that, as written, the formula will NOT update with changes in
column B. So if, for example, the user first enters a Y in column A, the
formula will update to '0'. If he then enters a date in ColB, the formula will
not change until something is entered in column A.

That is because without application.volatile, the formula will only update if
one of it's INPUTS changes. Since column B is not an input, it doesn't know to
change.

However, I think that behavior can be altered by merely rewriting the FUNCTION
statement to include Column B:

For example:

Function LastPM(rg As Range, rg2 As Range)

And then your worksheet formula would read something like:

=LastPM(A11:A60000,B11:B60000)

There is no need to refer to rg2 within the function. The fact of it being
listed as an argument will trigger the recalc. And hopefully, only on the one
sheet.

If you are using the array-formula, you could try changing it to refer to a
smaller number of lines. As written it refers to almost 60,000 lines; perhaps
10,000 would be enough?


--ron

 

[Dennis Allen]

Are you using the worksheet function approach or the VBA approach?

If the latter, you could try removing the "application.volatile" line. The
disadvantage is that, as written, the formula will NOT update with changes in
column B. So if, for example, the user first enters a Y in column A, the
formula will update to '0'. If he then enters a date in ColB, the formula will
not change until something is entered in column A.

Hi. Removed the violatile lines. Seems the sheets are working without any overhead. Thanks. If you want to see a stripped down
version, download http://www.dennisallen.com/temp.xls

I still have a couple of things to R&D: The client wants another summary sheet, but only displaying rows marked 'y' out of order.
Now we could use autofilter, but isn't there something more dynamic? If someone adds a new log entry marked out of order, don't you
have to go back to the summary sheet and rerun autofilter to see it?

I also need to look into the possibility of posting the whole xls file on the client website. Is it possible to allow users to add
but not edit/delete log entries? Or edit/delete column formats? Or edit/delete the summary sheet?

 

[Ron Rosenfeld]

Hi. Removed the violatile lines. Seems the sheets are working without any overhead. Thanks.

You're welcome. Glad it's working

I still have a couple of things to R&D: The client wants another summary sheet, but only displaying rows marked 'y' out of order.
Now we could use autofilter, but isn't there something more dynamic? If someone adds a new log entry marked out of order, don't you
have to go back to the summary sheet and rerun autofilter to see it?

Unless entries are frozen after they are made, I think you will have to
regenerate this summary by looking at every active line in each sheet in order
to get this data. Write a SUB that goes through every worksheet, and looks for
'y' in the 'out of order' column, then copy that line, with the info about
which worksheet, to that summary page. Have the SUB triggered by a button on
the Summary of out of orders sheet.

Since you want to get all of them, you could start at the top line (Row 11) and
work down to the last one.

I also need to look into the possibility of posting the whole xls file on the client website. Is it possible to allow users to add
but not edit/delete log entries? Or edit/delete column formats? Or edit/delete the summary sheet?

I would think so, but I've not done any work with web-based sheets.

--ron

 

[Dennis Allen]

Unless entries are frozen after they are made, I think you will have to
regenerate this summary by looking at every active line in each sheet in order
to get this data. Write a SUB that goes through every worksheet, and looks for
'y' in the 'out of order' column, then copy that line, with the info about
which worksheet, to that summary page. Have the SUB triggered by a button on
the Summary of out of orders sheet.

??? A VBA SUB that generates a summary sheet with a button? How? And why wouldn't you just create a macro button that
enable/disable the autofilter?

I've created a column with a function that checks for the "Y" and does rg.EntireRow.RowHeight = 0. Doesn't work. Is RowHeight a
read-only property? Is there another way for a VBA function to hide a row?

 

[Ron Rosenfeld]

??? A VBA SUB that generates a summary sheet with a button? How? And why wouldn't you just create a macro button that
enable/disable the autofilter?

I've created a column with a function that checks for the "Y" and does rg.EntireRow.RowHeight = 0. Doesn't work. Is RowHeight a
read-only property? Is there another way for a VBA function to hide a row?

EntireRow is read only. Why not just

rg.rowheight=0

???

I thought you wanted all of the "out of orders" onto one summary page, even if
the out of orders came from multiple sheets.

You could write a macro that would look at each data sheet, do the auto-filter
with the criteria of 'y' in the "out of order" column, then copy the visible
rows to your summary sheet along with the sheet name.


--ron

 

[Dennis Allen]

EntireRow is read only. Why not just

rg.rowheight=0

Still seems to be read only. Also tried rg.EntireRow.Hidden = True, no luck.

I thought you wanted all of the "out of orders" onto one summary page, even if
the out of orders came from multiple sheets.

Just the sheet currently out of order.

You could write a macro that would look at each data sheet, do the auto-filter
with the criteria of 'y' in the "out of order" column, then copy the visible
rows to your summary sheet along with the sheet name.

If we can't find a why, we'll have to create a one button macro to do autofilter...Dennis

 

[Ron Rosenfeld]

Still seems to be read only. Also tried rg.EntireRow.Hidden = True, no luck.

It works here, and also works on your sample sheet.

Just the sheet currently out of order.

I guess I still don't understand. Will there only be one sheet out of the 400
that is marked "out of order"?

If we can't find a why, we'll have to create a one button macro to do autofilter...Dennis

--ron

 

[Dennis Allen]

I don't what I'm doing wrong, so I uploaded another copy of http://www.dennisallen.com/temp.xls

In the "Outof Order" sheet, in the visible out of order column I call OutofOrder(). There should be only one row showing.