1

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

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)
2
  • Hi Oleg S, Thank you for taking the time to look into my enquiry. I really appreciate it, this will help me a lot. Thank you for sharing the =QUERY formula, this is very exciting and I will learn how to use it. Would you know of a way to automate the process of adding new data to the 'Order Data adjusted' format? I have data from my Google Form Responses and it has a specific format. Please see original link with updated form as example. Kind regards, Andres – dacostarican Sep 14 '20 at 13:16
  • Andres, sorry I'm on vacation, will take a look next week. – Oleg_S Sep 18 '20 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.