In Sheet 2 cell A1 enter the below formul and copy/drag to cell B1. Then copy
down as required...Please note that this is an array formula.You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula}"

=IF(COUNTIF(Sheet1!$B$1:$B$1000,"0")+1<ROW(Sheet1 !A1),"",
INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$10 00<0,
ROW($B$1:$B$1000)),ROW(Sheet1!A1))))

If this post helps click Yes
---------------
Jacob Skaria


" wrote:

I have a spreadsheet with the following values. Column A is the item number
and column B is the qty.

TAB1
A B
1 Item Number Qty
2 500cc 0
3 1500cc 100
4 1500sp 0
5 3000cc 100
6 16000cc 100

These figures are pulled out of the database when you enter the purchase
order. On another tab of the spreadsheet is the packing list. I need a
formula that will reference the previous worksheet and ignore the 0 values.
So I need the packing list to look like this:

TAB2
A B
1 Item Number Qty
2 1500cc 100
3 3000cc 100
4 16000cc 100
5
6

Now I have been using the following formula in cell A2 that reads:

=if(tab1!B20,tab1!A2,if(tab1!B30,tab1!A3,if(tab1 B40,tab1!A4,if(tab1!B50,tab1!A5,if(tab1!B60,tab 1!A6,"")

Now this formula works great for cell A2 on tab 2. The problem arises for
cell A3. If I do the same formula as above, I come up with the same answer as
cell A2. I need to have the same formula AND somehow exclude the result from
cell A2.

In cell A4, I need to have the same formula and exclude the result from cell
A2 and A3.

And so on and so forth.

Anybody have any ideas how I could do this?