One way ..
In Sheet1
------------
Assume the sample table below
is in cols A to D, data in row2 down
with the quantity col in col C (Qty)
Part# Price Qty Cost
Item1 $100.00 1 $100.00
Item2 $200.00 0 $0.00
Item3 $300.00 2 $600.00
Item4 $200.00 0 $0.00
Item5 $100.00 3 $300.00
etc
In an empty col to the right, say col X
Put in X2: =IF(OR(C2="",C2=0),"",ROW())
Copy down as many rows as data is expected
in the table, say down to C2000?
(can copy down ahead of expected data input)
This will set it up nicely for extract in Sheet4
In Sheet4
-------------
Paste the same headers into A1:D1, viz.:
Part# Price Qty Cost
Put in A2:
=IF(ISERROR(MATCH(SMALL(Sheet1!$X:$X,ROW(A1)),Shee t1!$X:$X,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$X:$X,ROW(A1)),Sheet1! $X:$X,0)-1,COLUMN(A1)-1))
Copy across to D2
(or across as many cols as there is in the table in Sheet1)
then fill down by as many rows as was done in col X
in Sheet1, viz. fill down to say D2000
Copy Format the table as in Sheet1
Cols A to D will auto-extract only those rows
with Qty 0 from Sheet1,
viz., for the sample data in Sheet1, you'll get:
Part# Price Qty Cost
Item1 $100.00 1 $100.00
Item3 $300.00 2 $600.00
Item5 $100.00 3 $300.00
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Eddie Shapiro" <Eddie
wrote in message
...
I have a workbook that has about four worksheets .
I want to create a list of items that will automatically be included on
worksheet tab 4 from Worksheet tab 1 if the quantity for the item listed
on
the first worksheet has been changed from 0 to any other number (i.e. 1 or
10
etc).
I have a list of equipment with pricing on worksheet tab 1 and the list is
long. Many of the items will have quantities of zero and will not increase
as
they will not be selected for the project being created. We have
additional
columns for cost, extended cost, labor hours etc. If the quantity changes
for
a part on any particular row, the extended cost changes from 0 to x and
the
labor hours change from 0 to x. Once the project is approved, there is an
individual who must order all equipment that has a quantity greater than
0.
It would be ideal for that list to automatically be created on the
worksheet
tab #4 so that this individual does not have to sort through the entire
list
of equipment that could have been selected for the project (but was not).
How
does one go about automatically creating this new worksheet (tab or sheet
#4).
Thanks!!!
Eddie