One way ..
Assume the source table is in Sheet1, cols A to C, data from row2 down, with
the key column being col C
Use an empty col to the right, say col E
Put in E2: =IF(C2="","",IF(C2<0,ROW(),""))
Copy E2 down to say, E100, to cover the max expected data range in the
source table
(Leave E1 empty)
In Sheet2
-------
Put in A2:
=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))
Copy A2 across to C2, fill down to C100
(cover the same range size as in col E in Sheet1)
Sheet2 will return the desired results from Sheet1, all neatly bunched at
the top
Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Marco" wrote in message
...
Hi,
I have a worksheet with several columns and in 1 column there is a number
from 0 to 10. I would like to copy "automatic" all rows from this
worksheet
to another worksheet but only if the number is different from 0. This is
not
a one-time operation but the numbers can dynamically change, they are
formula
based. I have not found a solution yet. Has anyone experience with this ?
Marco.
|