I have a large list of stock and description and qty on sheet1, and i would
like to extract just stock with qty value 0 to sheet2, which i managed with
=IF(Sheet1!$D$1:$D$200,Sheet1!$A$1:$A$24,""), however the distracted info is
scatered across the sheet2 according to fields they been stored in sheet1.
Is there a formula, which would help me to transfer necesary info to sheet2,
as soon qty0 anywere on sheet1, but so the transfered info would go nicly
one by one in column on sheet2 with no gaps.

I hope you gus understand what i ma trying to say here :)
thanks for help

Assume source data in Sheet1, cols A to D,
data from row2 down where the key col is col D (qty)

In Sheet2,
In A2: =IF(Sheet1!D20,ROW(),"")
Leave A1 empty. This is your criteria col.

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1))))
Copy B2 to E2. Select A2:E2, copy down to cover the max expected extent of
source data, say down to E100. Minimize/hide col A. Cols B to E will return
the required results, with all lines neatly packed at the top as desired (no
gaps).

voila? punch the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Getting right info" wrote:
I have a large list of stock and description and qty on sheet1, and i would
like to extract just stock with qty value 0 to sheet2, which i managed with
=IF(Sheet1!$D$1:$D$200,Sheet1!$A$1:$A$24,""), however the distracted info is
scatered across the sheet2 according to fields they been stored in sheet1.
Is there a formula, which would help me to transfer necesary info to sheet2,
as soon qty0 anywere on sheet1, but so the transfered info would go nicly
one by one in column on sheet2 with no gaps.

Thanks,
it worked :)

"Max" wrote:

Assume source data in Sheet1, cols A to D,
data from row2 down where the key col is col D (qty)

In Sheet2,
In A2: =IF(Sheet1!D20,ROW(),"")
Leave A1 empty. This is your criteria col.

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1))))
Copy B2 to E2. Select A2:E2, copy down to cover the max expected extent of
source data, say down to E100. Minimize/hide col A. Cols B to E will return
the required results, with all lines neatly packed at the top as desired (no
gaps).

voila? punch the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Getting right info" wrote:
I have a large list of stock and description and qty on sheet1, and i would
like to extract just stock with qty value 0 to sheet2, which i managed with
=IF(Sheet1!$D$1:$D$200,Sheet1!$A$1:$A$24,""), however the distracted info is
scatered across the sheet2 according to fields they been stored in sheet1.
Is there a formula, which would help me to transfer necesary info to sheet2,
as soon qty0 anywere on sheet1, but so the transfered info would go nicly
one by one in column on sheet2 with no gaps.

Welcome, glad to hear
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Getting right info" wrote in
message ...
Thanks,
it worked :)