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?
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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"handymanmd" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks