I'm using MS Office Prof. Edition 2003 Excel. I have a spreadsheet "BOM" with 3 columns, Parts Number, Description, and Quantity. I would like to generate a formula to automatically show a list of parts that has nonzero quantity. In orther words, either delete the parts with zero in quantity or in another sheet to show parts with quantity as none zero. I tried to in another sheet, in the same workbook, generate formula using nested if functions to copy contents from "BOM" sheet if the quantity is not zero, and skip if the quantity is zero. However, if function only gives one iteration, not like loop can continue check quantity till it reaches to the next nonzero quantity. How can I write code/formula to keep skipping zero quantity till next nonzero quantity and copy the corresponding contents from "BOM" sheet? Thanks. Best Regards, Steven Lu

An Excel formula can not loop. You need to use an array formula over a range of cells. Let's assume your Part Numbers are in A1:A19, your Descriptions are in B1:B19 and your Quantities are in C1:C19. To place a list of the Part Numbers with non-zero quantities in Column D, select D1:D19 and type in this formula: =IF(ISERROR(INDEX(A1:A19,SMALL(IF(C1:C19>0,ROW(INDIRECT("1:"&ROWS(C1:C19)))),ROW(INDIRECT("1:"&ROWS(C1:C19)))))),"",INDEX(A1:A19,SMALL(IF(C1:C19>0,ROW(INDIRECT("1:"&ROWS(C1:C19)))),ROW(INDIRECT("1:"&ROWS(C1:C19)))))) When you are done typing in the formula, use Ctrl-Shift-Enter to enter it as an array formula. If you do this correctly, brackets {} will appear around the formula. No, you cannot just type the brackets into the formula bar and expect it to work. To get the Quantities in Column F, select F1:F19 and type in this array formula, again using Ctrl-Shift-Enter when you are done. =IF(ISERROR(INDEX(C1:C19,SMALL(IF(C1:C19>0,ROW(INDIRECT("1:"&ROWS(C1:C19)))),ROW(INDIRECT("1:"&ROWS(C1:C19)))))),"",INDEX(C1:C19,SMALL(IF(C1:C19>0,ROW(INDIRECT("1:"&ROWS(C1:C19)))),ROW(INDIRECT("1:"&ROWS(C1:C19)))))) Hopefully you can see the difference between the 2 formulae and figure out how to get the Descriptions from B1:B19 into E1:E19. Good Luck!

Gosh, that's a wide post. I had trouble finding the "submit followup button!" :) Life's more painless for the brainless.

I'm not sure what you mean by "wide". I don't know how you view this website, but all posts that I see are about 5.5 inches wide in the middle of my screen, with the list of forums (1.5") on the left and ads (2") on the right. There's at least 2.5 inches of blank grey background on both sides. My post is about 11" tall, but only 5.5 wide, with the Submit button below my post. 15" wide flat screen monitor, set to 1280 x 1024

It's wide on mine also. Coming home from very lonely places, all of us go a little mad: whether from great personal success, or just an all-night drive, we are the sole survivors of a world no one else has ever seen. -