Recalculate cell #2
 

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

Recalculate cell #2
 

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

Recalculate cell #2
 

"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

Recalculate cell #2
 

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

Recalculate cell #2
 

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

Recalculate cell #2
 

"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

Recalculate cell #2
 

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

Recalculate cell #2
 

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

Recalculate cell #2
 

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

Recalculate cell #2
 

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

Recalculate cell #2
 

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

Recalculate cell #2
 

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" wrote in message ...
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

Recalculate cell #2
 

On Tue, 14 Sep 2004 21:57:13 -0400, "Dennis Allen"
wrote:

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

Recalculate cell #2
 

"Ron Rosenfeld" wrote in message ...

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?

Recalculate cell #2
 

On Wed, 15 Sep 2004 16:31:31 -0400, "Dennis Allen"
wrote:



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

Recalculate cell #2
 

"Ron Rosenfeld" wrote in message ...
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?

Recalculate cell #2
 

On Wed, 15 Sep 2004 22:02:40 -0400, "Dennis Allen"
wrote:


"Ron Rosenfeld" wrote in message ...
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?

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

Recalculate cell #2
 

"Ron Rosenfeld" wrote in message ...

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

Recalculate cell #2
 

On Thu, 16 Sep 2004 08:57:15 -0400, "Dennis Allen"
wrote:


"Ron Rosenfeld" wrote in message ...

EntireRow is read only. Why not just

rg.rowheight=0

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


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

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.


I guess I still don't understand. Will there only be one sheet out of the 400
that is marked "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

Recalculate cell #2
 

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.

Recalculate cell #2
 

On Thu, 16 Sep 2004 14:13:11 -0400, "Dennis Allen"
wrote:

what I'm doing wrong,

A FUNCTION cannot affect cell attributes.

A FUNCTION can only return a value.

So there is no way that a FUNCTION can change row height.


--ron

Recalculate cell #2
 

Ah. So how do we tie into the OutofOrder sheet? If someone adds or removes a "Y" log entry row, it needs to show up on the
OutofOrder sheet.

"Ron Rosenfeld" wrote in message ...
On Thu, 16 Sep 2004 14:13:11 -0400, "Dennis Allen"
wrote:

what I'm doing wrong,

A FUNCTION cannot affect cell attributes.

A FUNCTION can only return a value.

So there is no way that a FUNCTION can change row height.


--ron

Recalculate cell #2
 

On Thu, 16 Sep 2004 16:53:26 -0400, "Dennis Allen"
wrote:

Ah. So how do we tie into the OutofOrder sheet? If someone adds or removes a "Y" log entry row, it needs to show up on the
OutofOrder sheet.

I still don't understand how you can ensure you have only one "out of order"
entry marked "y" on your 400 sheets. How do you ensure that someone deletes a
previous out of order entry before entering a new one?

In any event, as I wrote some postings ago, and without understanding
everything, it seems you need to use a SUB procedure. Whether this should be
activated by a Button, or automatically -- perhaps with a Worksheet_Activate
event, is something you have to decide.

Again, I'm still not sure of how things should be set up. But play around with
this idea:

On your OutOfOrder worksheet, I guess you will have one line for each component
worksheet. These are the sheets you have named 10001, 10002, etc.

In addition, you have references to each worksheet that are from someplace at
the top section of each sheet, so do not depend on searching the worksheet.

By the way, I do not understand why you go through the bother of hiding the
columns that have the INDIRECT function in them, and testing the presence or
absence of data in a separate column. You could do all that in the same
column. For example, in J4 you have: =INDIRECT("'" & $B4 & "'!B5") and then in
K4 you have =IF(J40,J4,""). Why not combine them in J4?

=IF(INDIRECT("'" & $B4 & "'!B5")0,INDIRECT("'" & $B4 & "'!B5"),"")

Also, if all of your sheet names are without spaces, then you don't need the
apostrophes. So you could use =INDIRECT($B4 & "!F4"), as an example.

Looking at your sample workbook, it seems to me the simplest, and most
automated method would be to use the worksheet activate event on the OutOfOrder
worksheet.

First of all, given the current structure of that worksheet, just change the
code in Q4 to read =P4. You don't need that UDF that you have in there.

Right click on the OutOfOrder tab and select View Code. In the window that
opens, paste the following code:

====================
Private Sub Worksheet_Activate()
[P3].AutoFilter field:=16, Criteria1:="y"
End Sub
===================

What that does is run the autofilter looking for a "y" in field 16 which is
column P. It does that whenever you activate that worksheet.

If you reduce the number of columns as I suggested, you will need to alter the
SUB to select the proper field.




--ron

Recalculate cell #2
 

On Thu, 16 Sep 2004 20:26:23 -0400, Ron Rosenfeld
wrote:

====================
Private Sub Worksheet_Activate()
[P3].AutoFilter field:=16, Criteria1:="y"
End Sub
===================

Dennis,

Here is another SUB that seems to work as well. It has the advantage of
perhaps looking a bit "neater" than the autofilter as you do not see the
dropdown lists (down arrows). However, it may take longer to run with 400
lines.

(It is possible to remove the drop down lists on the autofilter command, but
that seems to take a long time, so I do not suggest it).

Enter it as "worksheet code" following the same procedure as I described for
the above.

===========================
Private Sub Worksheet_Activate()
Dim c As Range

Application.ScreenUpdating = False

Cells.Rows.AutoFit
For Each c In [p4:p403]
If Not c.Text = "y" Then c.RowHeight = 0
Next c

Application.ScreenUpdating = True

End Sub
============================

You may need to change the range p4:p403 if you move around the "y"


--ron

Recalculate cell #2
 

"Ron Rosenfeld" wrote in message ...

I still don't understand how you can ensure you have only one "out of order"
entry marked "y" on your 400 sheets. How do you ensure that someone deletes a
previous out of order entry before entering a new one?

The last log entry indicates if the item is out of order. LastST() checks the date column for the latest log date, then checks for
a "y" on that row. So when a sheet is no longer out of order, the user has to either remove the "y" from that last entry or make a
new entry.

By the way, I do not understand why you go through the bother of hiding the
columns that have the INDIRECT function in them, and testing the presence or
absence of data in a separate column. You could do all that in the same
column. For example, in J4 you have: =INDIRECT("'" & $B4 & "'!B5") and then in
K4 you have =IF(J40,J4,""). Why not combine them in J4?

=IF(INDIRECT("'" & $B4 & "'!B5")0,INDIRECT("'" & $B4 & "'!B5"),"")

If you combine them, how could you access their value in other formulas? Add this to D4:

=IF(INDIRECT($B4 & "!B4")0,INDIRECT($B4 & "!B4"),"")

Noticed what happened to I4? Doesn't work anymore. That's why hidden cells like J4 have values I can use in other formulas while
display cells like K4 remove those pecky "1/0/1900" and "0" (unless you have a better way). I wish I could do that on the
individual sheets for cells like B4:B6, D4:D6, F4:F6.

Also, if all of your sheet names are without spaces, then you don't need the
apostrophes. So you could use =INDIRECT($B4 & "!F4"), as an example.

Oh. Didn't know that.

Looking at your sample workbook, it seems to me the simplest, and most
automated method would be to use the worksheet activate event on the OutOfOrder
worksheet.

First of all, given the current structure of that worksheet, just change the
code in Q4 to read =P4. You don't need that UDF that you have in there.

Well, =IF(P40,P4,"") to remove that pecky "0".

Right click on the OutOfOrder tab and select View Code. In the window that
opens, paste the following code:

====================
Private Sub Worksheet_Activate()
[P3].AutoFilter field:=16, Criteria1:="y"
End Sub
===================

Tried it. Works! Thanks. Noticed it doesn't matter if the user hits "y" or "Y". I like it.

Something very wrong. I was playing with the temp.xls. Added [P3].AutoFilter field:=16, Criteria1:="y" to the Worksheet_Activate()
of OutofOrder. After the sheet recalculated, all the UDFs everywhere went #VALUE! What happened? If you put in a new entry,
causing a UDF to run, the #VALUE! goes away for that cell. But only that cell.

I tried it with the full copy. After adding the OutofOrder sheet, and the SUB to the OutofOrder sheet, after recalc I again get
#VALUE! everywhere. I didn't get it before, why now? I don't want to send this off to the client until I know what's causing it
and how to fix...Dennis

Recalculate cell #2
 

"Ron Rosenfeld" wrote in message ...
On Thu, 16 Sep 2004 20:26:23 -0400, Ron Rosenfeld
wrote:

====================
Private Sub Worksheet_Activate()
[P3].AutoFilter field:=16, Criteria1:="y"
End Sub
===================

Dennis,

Here is another SUB that seems to work as well. It has the advantage of
perhaps looking a bit "neater" than the autofilter as you do not see the
dropdown lists (down arrows). However, it may take longer to run with 400
lines.

(It is possible to remove the drop down lists on the autofilter command, but
that seems to take a long time, so I do not suggest it).

Enter it as "worksheet code" following the same procedure as I described for
the above.

===========================
Private Sub Worksheet_Activate()
Dim c As Range

Application.ScreenUpdating = False

Cells.Rows.AutoFit
For Each c In [p4:p403]
If Not c.Text = "y" Then c.RowHeight = 0
Next c

Application.ScreenUpdating = True

End Sub
============================

You may need to change the range p4:p403 if you move around the "y"

I like it! Avoids the autofilter problem altogether. I still can't figure out how we got the autofilter SUB to work the first time
or why I can't get it to work this time.

Recalculate cell #2
 

On Thu, 16 Sep 2004 23:44:15 -0400, "Dennis Allen"
wrote:

If you combine them, how could you access their value in other formulas? Add this to D4:

=IF(INDIRECT($B4 & "!B4")0,INDIRECT($B4 & "!B4"),"")

Noticed what happened to I4? Doesn't work anymore. That's why hidden cells like J4 have values I can use in other formulas while
display cells like K4 remove those pecky "1/0/1900" and "0" (unless you have a better way). I wish I could do that on the
individual sheets for cells like B4:B6, D4:D6, F4:F6.

You can remove the 'pecky "1/0/1900" and "0"' by formatting the cells to not
display 0's. That way you don't need the extra columns. So instead of
combining the formulas, in D4, leave the formula as you have it:

=INDIRECT($B4 & "!B4")

and change the format
Format/Cells/Number Custom Type: m/d/yyyy;;

Note the two semicolons at the end. That tells Excel if the value is 0 (or
negative) don't display anything. But the 0 is still available to use for
calculations.

I'm glad this is working for you now. Although I may check in tomorrow
morning, I'll be gone until Sunday PM.

So if you have more questions, there'll be some delay in my response.

No time to look into the issue with autofilter, but since hiding the rows is
working, there's no need.

Best,

--ron

Recalculate cell #2
 

"Ron Rosenfeld" wrote in message ...
On Thu, 16 Sep 2004 23:44:15 -0400, "Dennis Allen"
wrote:

If you combine them, how could you access their value in other formulas? Add this to D4:

=IF(INDIRECT($B4 & "!B4")0,INDIRECT($B4 & "!B4"),"")

Noticed what happened to I4? Doesn't work anymore. That's why hidden cells like J4 have values I can use in other formulas while
display cells like K4 remove those pecky "1/0/1900" and "0" (unless you have a better way). I wish I could do that on the
individual sheets for cells like B4:B6, D4:D6, F4:F6.

You can remove the 'pecky "1/0/1900" and "0"' by formatting the cells to not
display 0's. That way you don't need the extra columns. So instead of
combining the formulas, in D4, leave the formula as you have it:

=INDIRECT($B4 & "!B4")

and change the format
Format/Cells/Number Custom Type: m/d/yyyy;;

Note the two semicolons at the end. That tells Excel if the value is 0 (or
negative) don't display anything. But the 0 is still available to use for
calculations.

Ah. I'll try it. Thanks for all your help...Dennis