Moving Information

[Anupkumar]

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!

 

[Max]

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 A22, 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)

 

[Anupkumar]

What would be the formula to rather than referencing a cell but make i
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 g
directly under the Chairs.

Thanks once again

 

[Max]

.. 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 ..

 

[Andy]

The above won't happen if the suggested construct was implemented correctly.
Try it again ..

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!!!

 

[Max]

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)

 

[Andy]

Firstly I would like to thank you Max for your assistance! We are now trying
to take this one step further. I have numerous departments which will be
completing the workbook, also the workbook has numerous worksheets such as
Furniture, Equipment, Computers etc. Once they have completed all the
different worksheets and the information is processed using the formula
provided above, and I have all the items ordered on a seperate worksheet, I
need to group the information. An example would be:
Administration completing the "shopping list" and Marketing completing their
"shopping list". I need to place all the Furniture ordered by the two
departments on one Final worksheet and all the Equipment ordered on to one
worksheet.

(I hope you guys get the idea)
Basically I have tried copying and pasting the information but it does not
work. I have tried to tweak the formula by changing the A1 to A80 and its not
working because the cells are not matching up I guess. (A80 is where the
information for the second department would start.)

Thank you guys once again!

 

[Max]

Firstly I would like to thank you Max for your assistance!

Welcome, Andy !

We are now trying to take this one step further....

You could hang around awhile for responses from others. But I would suggest
you put your new query in as a new post, for better radar/exposure to all the
responders out there. This thread is quite dated & deep ..