Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Display selected rows from one worksheet to another
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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)),Shee t1!$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 -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "Brian" wrote in message ... 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. |
#4
|
|||
|
|||
Thanks very much, I wll try this.
"Max" wrote: 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)),Shee t1!$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 -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "Brian" wrote in message ... 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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
You're welcome !
Trust it'll work for you .. -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "Brian" wrote in message ... Thanks very much, I wll try this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display specific rows from table on other worksheet | Excel Discussion (Misc queries) | |||
empty rows at bottom of worksheet | Excel Discussion (Misc queries) | |||
How to use outline data (grouped rows) in a protected worksheet? | Excel Discussion (Misc queries) | |||
How to use outline data (grouped rows) in a protected worksheet? | Excel Discussion (Misc queries) | |||
Deselect one of many non-adjacent rows selected | Excel Discussion (Misc queries) |