Hello Max thank you for your reply. I tried your formula out but have to
tinker with it a bit as my rows and columns start a bit lower down than row1.
If i have a problem with it i will be sure to re-post!!!!! Thanks for
your help.
Regards.
Julia
"Max" wrote:
Perhaps you'd also like to play around
with this formula approach as well ?
Assume you have the table below in Sheet1,
col headers in row1, data in row2 down
where the key "TotalQty" col is in col B
Item TotalQty <etc
1111 1
1112 0
1113 1
1114 0
1115 1
etc
In an empty col to the right, say col K?
Put in K2: =IF(B2<0,ROW(),"")
Copy K2 down by as many rows as data is expected
in the table, say, down to K1000?
(can copy down ahead of expected data input)
In Sheet2
----
With the same col headers in row1:
Item TotalQty <etc
Put in A2:
=IF(ISERROR(SMALL(Sheet1!$K:$K,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$K:$K,ROWS($A$1:A1)),Sheet1!$K:$K,0)))
Copy A2 across by as many cols as there is in the table in Sheet1, then fill
down by as many rows as was done in col K in Sheet1, i.e. down by 1000 rows
Sheet2 will return only the rows from the table in Sheet1 where the
"TotalQty" col is < 0, and doing so without any "blank" rows in-between
For the sample data given, you'll get:
Item TotalQty <etc
1111 1
1113 1
1115 1
etc
(Rows for Items 1112 and 1114 will not show)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
jules wrote in message
...
Good morning. I just have one other thing to ask you - what do i do with
the
formula you wrote? It looks like ancient greek!!
"jules" wrote:
Thank you very much for your prompt reply.
Best wishes
Jules
"Ron de Bruin" wrote:
Hi Jules
http://www.rondebruin.nl/copy5.htm
For a Add-in solution see
http://www.rondebruin.nl/easyfilter.htm
--
Regards Ron de Bruin
http://www.rondebruin.nl
"jules" wrote in message
...
I have created a price list and would like to generate an order form
from
that liston a separate worksheet. Is there a way of copying an
entire row if
the column has a figure in the total column? The rows with a 0
total would
not need to be copied