Moving Information

I have a "shopping list" which a user fills out. I need that information to move to a seperate sheet only if the quantity is changed from a 0 (zero) to a 1 or higher.

For example:

Item Qty Total
Chair 0 0
Table 1 50

I need the information with regards to the table to move to a seperate sheet. (It needs to include the name(table) quantity (1) and Total (50))

As the chair quantity remains as 0 I do not want that information on the seperate sheet.

Any assistance will be appreciated!

One play which might suffice ..

Assume source table below is in sheet: X, cols A to C,
data from row2 to a max expected row100 (say)

> Item Qty Total
> Chair 0 0
> Table 1 50

In another sheet: Y (say)
with the same col headers in A1:C1, viz.: Item, Qty, Total

Put in A2:
=IF(ISERROR(SMALL($D:$D,ROW(A1))),"",INDEX(X!A:A,MATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))
Copy A2 to C2

Put in D2: =IF(X!B2>0,ROW(),"")
(Leave D1 empty)

Then select A2:D2, fill down to D100
(cover the max expected extent of data in X)

Cols A to C will return the required results from X,
ie only the lines with Qty > 0,
with all results neatly bunched at the top

(Hide away the criteria col D, if desired)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Anupkumar" wrote:
>
> I have a "shopping list" which a user fills out. I need that information
> to move to a seperate sheet only if the quantity is changed from a 0
> (zero) to a 1 or higher.
>
> For example:
>
> Item Qty Total
> Chair 0 0
> Table 1 50
>
> I need the information with regards to the table to move to a seperate
> sheet. (It needs to include the name(table) quantity (1) and Total
> (50))
>
> As the chair quantity remains as 0 I do not want that information on
> the seperate sheet.
>
> Any assistance will be appreciated!
>
>
> --
> Anupkumar
> ------------------------------------------------------------------------
> Anupkumar's Profile: http://www.excelforum.com/member.php...o&userid=35152
> View this thread: http://www.excelforum.com/showthread...hreadid=549173
>
>

What would be the formula to rather than referencing a cell but make it go on the line below. For example:

The list below is the items available to order:
Chair 1 $100
Table 0 $0
PC 2 $500

Below is the final out come as it currently stands:

Chair 1 $100

PC 2 $500

I do not want the empty line between the two items. I need the PC to go directly under the Chairs.

Thanks once again!

"Anupkumar" wrote:
> .. I do not want the empty line between the two items.

The above won't happen if the suggested construct was implemented correctly.
Try it again ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Max" wrote:

> "Anupkumar" wrote:
> > .. I do not want the empty line between the two items.
>
> The above won't happen if the suggested construct was implemented correctly.
> Try it again ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

Yes this is true. It does not happen when I input the formula above.
Although it still includes the items with the 0 quantity, along with placing
it in order. I unfortunatly do not want it in chronological order. The items
are listed in a specific order on the "shopping list" and need to maintain
that order, if that makes sense. I am just trying to change the above formula
to not place the items in order, along with making it not pick up the items
with 0 quantity.

Thank you for all of your help!!!

"Andy" wrote:
> Yes this is true. It does not happen when I input the formula above.
> Although it still includes the items with the 0 quantity, along with placing
> it in order. I unfortunately do not want it in chronological order. The items
> are listed in a specific order on the "shopping list" and need to maintain
> that order, if that makes sense. I am just trying to change the above formula
> to not place the items in order, along with making it not pick up the items
> with 0 quantity.

Not sure what's happening over there <g>,
but here's a sample construct to illustrate:
http://www.savefile.com/files/6089699
Extract Lines with Qty more than zero in other sht

> Although it still includes the items with the 0 quantity, along with placing
> it in order. I unfortunately do not want it in chronological order...

The results extracted in Y do not include items with zero quantity (this is
the criteria !), and extracted items are in the same relative order that they
appear originally in X (there is no chronological ordering)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---