Linking items GREATER THAN O on another worksheet in the same Work
 

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

Hi!

Assume the items on Sheet 1 are in column A starting in A2=20
and the quantity is in the corresponding cell in column B.

For this example the total range is A2:B21.

In Sheet 4 in whatever cell, enter this array formula with=20
the key combo of CTRL,SHIFT,ENTER and copy down until you=20
get #NUM! errors which means there is no more data that=20
meets the condition:

=3DINDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$B$2:$B $210,ROW
($A$1:$A$20)),ROW(1:1)))

Biff

-----Original Message-----
I have a workbook that has about four worksheets =E2?=A6

I want to create a list of items that will automatically=20
be included on=20
worksheet tab 4 from Worksheet tab 1 if the quantity for=20
the item listed on=20
the first worksheet has been changed from 0 to any other=20
number (i.e. 1 or 10=20
etc).

I have a list of equipment with pricing on worksheet tab=20
1 and the list is=20
long. Many of the items will have quantities of zero and=20
will not increase as=20
they will not be selected for the project being created.=20
We have additional=20
columns for cost, extended cost, labor hours etc. If the=20
quantity changes for=20
a part on any particular row, the extended cost changes=20
from 0 to x and the=20
labor hours change from 0 to x. Once the project is=20
approved, there is an=20
individual who must order all equipment that has a=20
quantity greater than 0.=20
It would be ideal for that list to automatically be=20
created on the worksheet=20
tab #4 so that this individual does not have to sort=20
through the entire list=20
of equipment that could have been selected for the=20
project (but was not). How=20
does one go about automatically creating this new=20
worksheet (tab or sheet #4).

Thanks!!!

Eddie


.

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

Oops, typo correction:

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?

Last line in above should read as
in the table, say down to X2000?
--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---

... and a note from the OP ..
--
Date: Wed, 01 Dec 2004 08:51:50 -0500
Subject: Linking Cells in Excel
From: "Eddie Shapiro"
To:

Hi Max .... Thanks for the great help with my excel question !!!!!

Eddie Shapiro
--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---