Display selected rows from one worksheet to another

[Brian]

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.

 

[Myrna Larson]

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 D120, 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

 

[Max]

Just an option using formulas for you to play around ..

In Sheet1
------------
Use one empty col to the right, say, col K

Put in K1: =IF(AND(D1>=20,D1<=29),ROW(),"")
Copy down to K20

In Sheet2
------------

Put in B1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0)-1,COLUMN(A1)))

Copy across to G1, fill down to G20

This'll extract only the specified rows
from Sheet1's B1:G20,
and w/o any spaces / blank rows in-between

 

[Brian]

Thanks very much, I wll try this.

Just an option using formulas for you to play around ..

In Sheet1
------------
Use one empty col to the right, say, col K

Put in K1: =IF(AND(D1>=20,D1<=29),ROW(),"")
Copy down to K20

In Sheet2
------------

Put in B1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0)-1,COLUMN(A1)))

Copy across to G1, fill down to G20

This'll extract only the specified rows
from Sheet1's B1:G20,
and w/o any spaces / blank rows in-between

 

[Brian]

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

 

[Max]

You're welcome !
Trust it'll work for you ..