create an order form from an input sheet

Hi
I'm sure this is a very simple fix.
I would like to create an order form on a separate sheet from inputted data from another sheet.
Every time I try, I get annoying gaps where there are no quantities.
(hopefully) I have attached a sheet of what I want the result to look like.

Try this in G6:

=IFERROR(INDEX(A:A,SMALL(IF($C$6:$C$12<>"",ROW($C$6:$C$12)),ROWS($1:1))),"") Ctrl Shift Enter

Drag to the right then down.

Hi
Thanks for your quick reply 63falcondude
but all I get is #NAME? in all cells

I just realized that you are using Excel 2003. IFERROR is not supported in 2003.

Try this instead:

=IF(ISERROR(INDEX(A:A,SMALL(IF($C$6:$C$12<>"",ROW($C$6:$C$12)),ROWS($1:1)))),"",INDEX(A:A,SMALL(IF($C$6:$C$12<>"",ROW($C$6:$C$12)),ROWS($1:1)))) Ctrl Shift Enter

Hi 63falcondude
I know I'm missing something simple here,
it works great on the first line but all the lines below are blank.
I dragged across to the right and then down to the bottom of my list.
sorry for my uselessness (if that is a word)

Have you tried using "Autofilter"?

Select range A5:C12, then "Data" -> "Filter" -> "Autofilter"

Click "drop-down" "Qty" and click "NonBlanks" then click "Copy"

Click cell A5 (Sheet2) and select Paste Special + All. Remove "Autofilter" from range A5:C12

And of course one could easily write a macro to do the job.

Something like this perhaps:

Sub filter_copy()
With ActiveSheet
        .AutoFilterMode = False
        .Range("A5").CurrentRegion.AutoFilter
End With

Selection.AutoFilter Field:=3, Criteria1:="<>"
ActiveSheet.AutoFilter.Range.Copy

Sheets("Sheet2").Range("A5").PasteSpecial Paste:=xlAll
Application.CutCopyMode = False

Range("A5").CurrentRegion.AutoFilter

End Sub

Alf

Originally Posted by Alf

Have you tried using "Autofilter"?

Select range A5:C12, then "Data" -> "Filter" -> "Autofilter"

Click "drop-down" "Qty" and click "NonBlanks" then click "Copy"

Click cell A5 (Sheet2) and select Paste Special + All. Remove "Autofilter" from range A5:C12

And of course one could easily write a macro to do the job.

Something like this perhaps:

Sub filter_copy()
With ActiveSheet
        .AutoFilterMode = False
        .Range("A5").CurrentRegion.AutoFilter
End With

Selection.AutoFilter Field:=3, Criteria1:="<>"
ActiveSheet.AutoFilter.Range.Copy

Sheets("Sheet2").Range("A5").PasteSpecial Paste:=xlAll
Application.CutCopyMode = False

Range("A5").CurrentRegion.AutoFilter

End Sub

Alf

Thanks Alf
That worked perfect

You are welcome

Thanks for feedback and rep.

Alf

Ps Happy Easter