Moving selective data from one sheet to anouther

  • Thread starter Thread starter handymanmd
  • Start date Start date
H

handymanmd

Hi, I would like to move a range of data within a row from one sheet to
anouther sheet automatically when the data in a cell within the range
that would be moved, is greater than zero. Is there a formula that will
do this?
 
One non-array formulas play which "yields" the desired output ..

Assume source table is in Sheet1, cols A to D, data from row2 down, where
the key col is col D (the " greater than zero " criteria)

Use an empty col to the right, say col E
Put in E2: =IF(D2="","",IF(D2>0,ROW(),""))
Copy E2 down to say, E100 to cover the max expected data range in the source
table

In Sheet2
--------
Paste the same headers into A1:D1

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 D2, fill down to D100
(cover the same range as done in col E in Sheet1)

Sheet2 will return only the lines from Sheet1
which satisfies the criteria, all neatly bunched at the top
 
Back
Top