I have an order form of about 200 rows of product descriptions, prices and an empty cell for the customer to enter a quantity. I want to take the order form data and use it in an invoice which will list ONLY the products for which a qty was entered, all the rows together, no blank ones.
So far, the best I have come up with is to read each row with an IF statement that pulls the data from the order sheet to an invoice sheet IF qty > 0. But, where qty is not > 0, it leaves a blank line. And so, if the customer ordered the first item and the 200th, the invoice sheet will show the item on line 1, then 198 blank lines, then the 200th item. Otherwise, the products ordered are scattered up and down the invoice sheet.
I sort this list to push all the blank lines to the bottom, and there I have my invoice. But I suspect there is a more elegant way simply to bring the data across, without blank lines, from the get-go.
Any thoughts?
Thanks!
So far, the best I have come up with is to read each row with an IF statement that pulls the data from the order sheet to an invoice sheet IF qty > 0. But, where qty is not > 0, it leaves a blank line. And so, if the customer ordered the first item and the 200th, the invoice sheet will show the item on line 1, then 198 blank lines, then the 200th item. Otherwise, the products ordered are scattered up and down the invoice sheet.
I sort this list to push all the blank lines to the bottom, and there I have my invoice. But I suspect there is a more elegant way simply to bring the data across, without blank lines, from the get-go.
Any thoughts?
Thanks!