CODEWITHSUNDEEP
google-sheets
Huzaifa Ezzi
Huzaifa Ezzi

Reputation: 7

How to automatically fill data in an invoice in Google Sheets

I am sharing this sheet with you so that you can have a look and give me a proper solution. Basically what I want to do it generate an invoice based on the data entered in "Purchases" but I don't know how to do it as the lot numbers aren't always in the proper sequence. Therefore, if you notice in the "Sale & Inventory" sheet, I have to create multiple entries for one invoice. Is there a simpler method where the invoices are generated automatically? I don't want more than 30 lots/invoice.

Thanks,

Huzaifa.

Upvotes: 0

Views: 1829

Answers (1)

CalamitousCode
CalamitousCode

Reputation: 1414

I can't leave a comment to ask a question, so I assumed you'd want all lots from a certain date. Please see the 'Invoice' tab from this spreadsheet (I duplicated yours). Changing the yellow drop-downs should fill the invoice for you. You can also create multiple pages so it stays A4 size.

I also had to make sure column C on the 'Purchases' tab was formatted as a date 'yyyy-mm-dd' by using the 'Format' menu.

I did this by adding =QUERY(Purchases!$A$2:$T$500,"Select B where B<>'' and C >= date '"&TEXT(REGEXEXTRACT(K3,"[0-9]{6}"),"20##-##-##")&"' and C <= date '"&TEXT(REGEXEXTRACT(L3,"[0-9]{6}"),"20##-##-##")&"' limit 30 offset "&IF(M6=1,0,(M6-1)*34)&"",0) to cell H8 (highlighted green) in 'Invoices' tab.

How it works:

=QUERY(
Purchases!$A$2:$T$500,                  //the data I want to query
"Select B                               //tells it which column i want data from
where B<>''                             //make sure column B is not blank

and C >= date '"&TEXT(REGEXEXTRACT(K3,"[0-9]{6}"),"20##-##-##")&"'
                                        //makes sure the date is greater than or equal to
                                        //the start date found in your lot code and formats
                                        //it as 'yyyy-mm-dd' which is necessary for the
                                        //query function

and C <= date '"&TEXT(REGEXEXTRACT(L3,"[0-9]{6}"),"20##-##-##")&"'
                                        //makes sure the date is less than or equal to
                                        //the start date found in your lot code and formats
                                        //it as 'yyyy-mm-dd' which is necessary for the
                                        //query function

limit 30                                //restricts the returned results to 30 so it does
                                        //not exceed the number of lines on your invoice

offset "&IF(M6=1,0,(M6-1)*30)&"         //this shows the 'page' of data that you select
")

Let me know if you have different criteria for invoices and I can help, but try and see if you can figure it out from here first.

Upvotes: 1

Related Questions