Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Brian
 
Posts: n/a
Default 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   Report Post  
Myrna Larson
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
Brian
 
Posts: n/a
Default

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   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.





  #6   Report Post  
Max
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display specific rows from table on other worksheet Bruno G. Excel Discussion (Misc queries) 2 January 21st 05 12:22 AM
empty rows at bottom of worksheet michael g Excel Discussion (Misc queries) 2 January 18th 05 10:17 PM
How to use outline data (grouped rows) in a protected worksheet? biometris Excel Discussion (Misc queries) 1 January 17th 05 11:51 PM
How to use outline data (grouped rows) in a protected worksheet? biometris Excel Discussion (Misc queries) 0 January 17th 05 10:47 AM
Deselect one of many non-adjacent rows selected Nicolle K. Excel Discussion (Misc queries) 1 January 11th 05 07:24 PM


All times are GMT +1. The time now is 11:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"