Pull over only rows that have info in a certain column

I have 2 tabs. The first tab has all of my inventory items listed. The second tab is my order tab. What i need is on the second tab I need it to look at the inventory tab at the "D" column and if there is a number in a row then it needs to copy the whole row over. It needs to continue doing this for all 50 rows while skipping blank rows. So that on the second tab I have only the items I need to order but with no blank lines.

Any and all help is appreciated.

Hi

Can you put up a small sample file showing your structures so we can work towards a solution that suits your situation.


rylo

Here is a simple form of the file. As you can see I can use if statements to pull over the lines but it leaves blank lines in between the items I am ordering. I need it to skip over those lines so there is not any.

Let me know if you all have any suggestions.

If there is any more info I can give let me know.

Hi

Try this.

rylo

You Rock! That is exactly what I needed now I can adjust it to fit my whole sheet. Thanks

OK I know it's something I am doing but when I attempt to copy formula over to my other sheet it comes up with #VALUE!

What am I doing wrong?

Here is what I have right now

=IF(ROW()-3>COUNT(Products!$J$3:$J$50),"",INDEX(Products!L:L,SMALL(IF(ISNUMBER(Products!$J$3:$J$50),ROW(Products!$J$3:$J$50),""),ROW()-3)))

Ok I'm still learning excel here is what i noticed when I click on the cell with the formula in it it has {} around the formula. But if I try to edit it the {} removes and the formula no longer works. How do I edit the formula without losing the {}

Hi

The formula needs to be array entered.

When you edit the formula, hold down the CTRL and SHIFT keys, then press enter. You will find that the {} will be added to the formula.

See how that goes.

rylo

Excellent Thanks a million.
Works like a charm.

Rylo,
Excellent solution. I just joined the forum today. Came across this excellent solution to a very similar problem. Almost working for me. My difference is that instead of having a number as a qualifier (as in order quantity from Johnny's example) I am searching in a field that is text ( Cat 1 or Cat 2).
I'm trying to get this to work by changing ISNUMBER to ISTEXT... I may need a 'FIND' function in there to specify between Cat 1 or 2... not sure but I'm still working on it. Suggestions greatly appreciated.

gw