Is there a formula to automatically populate invoice with only the products that have been ordered and excludes the products not ordered?

Question

I have a range of products in my order data sheet and I need help creating a formula that automatically fills up my Invoice sheet with only the products that were ordered.

Currently I can only populate the Invoice with the whole list of products but I want to be able to send only the ones that were ordered (the rows in the order data sheet that are not blank).

For this workflow I am using Google Forms to gather the data per client order (row) and I have more than 10 products they can choose. I am also using an add-on (autocrat) to send out the invoice to customers once they send in an order. In my testing everything worked except I was sending the whole list of products in the invoice even with the products they didn't order as blank quantities and prices.

I am trying to automate the process so I don't want to have to manually add the products that each client ordered in the invoice.

Sample sheet link: https://docs.google.com/spreadsheets/d/1ZZ8aFUhnj8OUpDfKzBvq-4hKOISQP9JOOa3ooaVCLBU/edit?usp=sharing

Answer 1

See sheet 'INVOICE' in File.

Since cells B:D are merged, there are 2 formulas in B15 and E15 that return Product name and quantity with price for customer specified in cell B10.

=Query(
{Arrayformula(
 Transpose(
  Split(
   Rept(
    Concatenate('Order Data'!$C$3:$C$9&","),5),","))),
{'Order Data'!$D$3:$H$9;
 'Order Data'!$I$3:$M$9;
 'Order Data'!$N$3:$R$9;
 'Order Data'!$S$3:$W$9;
 'Order Data'!$X$3:$Ab$9}},

"Select Col3
Where Col1 = '"&Right($B$10,Len($B$10)-4)&"'
And Col3 <> ''",0)

Sheet 'Order Data adjusted' arranged in table form. In case of new products or customers it would be easier to add new data and form invoices, since formula in 'INVOICE adjusted' covers whole range of 'Order Data adjusted'.

=QUERY({'Order Data adjusted'!$B:$H},
"Select Col4
Where Col2 = '"&RIGHT($B$10,len($B$10)-4)&"'",0)