View Single Post
  #5   Report Post  
Brian
 
Posts: n/a
Default

Thank you, I will try this option as well. I appreciate the help.

"Myrna Larson" wrote:

Let's say you put this formula in Sheet2!B1

=Sheet1!B1

and copy it across and down to fill cells B1:G20. That replicates the values
from Sheet1 on Sheet2.

Those formulas can only return a value to the cell containing the formula.
They can't change the row height or hide the row. With Conditional Formatting,
you could change the font color to white, but you can't change the row height.
IOW, there's no way to eliminate the appearance of blank rows via a formula.

You could accomplish what you want with an event macro that is triggered by
changes in cells D1:D20, that looks at the new value and hides or unhides the
corresponding row on Sheet2. Sheet2 has the formulas that I describe above.

The following code belongs in the module named Sheet1 that you see in the
project pane for your workbook.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim C As Long
Dim R As Long
Dim HideIt As Boolean

C = Target.Column
If C = 4 Then
R = Target.Row
If R <= 20 Then
HideIt = (Target.Value < 20 Or Target.Value 29)
Worksheets("Sheet2").Rows(R).Hidden = HideIt
End If
End If
End Sub



On Thu, 28 Oct 2004 19:46:01 -0700, "Brian"
wrote:

Worksheet 1 has values in cells B1:G1 and down to B20:G20. I would like
worksheet 2 to display the values of each row of worksheet 1 only if the
value of cell D1,D2,D3 etc. of worksheet 1 is between 20 and 29. I would also
prefer not to have spaces between the rows in worksheet 2 when the selected
rows are displayed. I would appreciate assistance with a formula to do this.
Thanks in advance.