I freaking love this guy. Creating this invoice - not as sophisticated as this guys, but helped me on my first day on a new job. I need more! Love this guy.
It always worth watching your videos, even when the title seems to be just a simple tamplate lessons. As mentioned before, many golds inside. Just learned about offset!
Excellent explanation and inspiring techniques to apply to solve problems, that I never thought there could be a way out. Thank you very much wholeheartedly for your great videos. I do really appreciate your efforts. And please keep up the good work.
Cool, like all your videos! There is one thing with merged cells. Click on any merged cell in the sheet to make it active > Ctrl+A > go to the main panel "Merge cells ▾" and click that tiny arrow > "Unmerge". It works only if a merged cell was selected before selecting all.
Very informative! Thanks Much!! Now to expand on this: have a form input which auto imports info (purchase) into the Template and sends email confirmation to customer and to distribution officer. That would cut out a lot of work. Just a thought for future tutorials.
Great video. I was really hoping you would show how to reset the template after I print/save the one I worked on. It would be time saving. I wouldn't need to delete all information from the previous template one by one and then start making a new invoice
Hey - love your work! Using the above principle I constructed a fairly complex Project Management spreadsheet, which seems to be working fine, except for the fact that my array formulas are not "auto-updating". I checked the sheet calculations settings and bumped up iterations, but to no avail. The only way it updates now is, if I delete the "content" of the cell and let it repopulate. Would really appreciate some guidance with this issue. Thanks!
would you be able to group 3 of those order lines and add a subtotal below, and then have 3 more with their sub total? I mean, of course it is possible, but is it possible with the 1 array formula above that basically has to do AND row totals, AND decide when to do a subtotal on a dynamic number of rows above it. This might seem like something that needs a custom appscript or am I overtinking it?
great video. But after you made your invoice, how do you save them to a data base to be able to retrieve later? And only bad thing is doesn't look like you can set up the invoice numbers to go in sequential order so you need to look at the previous one you made to know what invoice number to use next...?
Great video.... Following up with this invoice..... How can I capture the variable data of this invoice and paste them in a table in an other sheet...... 🙏
Many Thanks dear, i am your subscriber and have been learning a lot from your videos, this video is also great and teaches about the offset function, however i was wondering why did google made the 22nd row hidden, and apparently discovered that they applied the sum formula from 18 to 22 and then purposely hid the 22nd row so that the calculation works well even if a new row is added BELOW the last row ( thats visible - 21st row ) , I applied the same logic to the 18th row and hid that as well, so basically I got to learn 2 new things from your video. THANKS BUDDY.
Thanks to your video I am now able to make an invoice template with the features I need. However, I was wondering if there is a way to create a function in this template with some sort of dropdown menu with the customer's info. So lets say by selecting "Plastering Business" it automatically fills in their business information. And if possible; is it also possible to create an automated function to E-mail it to said customer?
Dears this is amazing presentation thank you so much can you add on this the profit and lost calculation.by separate way for eg. By day or by item. Thank you
Learn Google Spreadsheets ... LoL! Great Answer...I should have seen that one coming for sure! Seriously though, its that Last 20% that's way out of my Abilities. I know I have been after you for quite awhile now about this, probably why you made This Video. Let's see if I can Communicate this to you Clearly. In your Video, the User has 9 Lines of Entry until Data is Pushed to the Next Page. That is where Invoice Templates Fall Short. Page 2, 3, etc. do not need all the Header Data from the First Page, just more Item Lines and the Footer Portion only on the Last Page. Every Page should have a: Page 1 of 1/2/3... and a File Name/Number Entry Inserted Automatically. Also, the Item Lines should be Added Automatically whenever the Last Item Line is Filled In. BTW...Item Lines should be Automatic Starting from the First Item Line despite Insertions or Deletions. This may Require Scripting?
@@cheewurz This is why I gave up googlesheet and used RapidBooks Invoice Maker. Using a professional Invoice Maker would not have such a problem. Of course, there will be a lot of room for customization to use Google Sheet. But It’s enough for a Small Business to use RapidBooks
say i wanted to hide the array formula in a title 2 rows above my value section. example there was a space between my header and my first value. is there a way to make the array formula add 2 rows instead of 1 when i add the semi colon?
is there a way to get the last cell reference dynamically without hard coding it in the formula for example, i have a range from a2:a10 so i need the reference to get a10 and once i add new data say like another 10 rows it will reference cell a20
OMG, this is really cool, can you make a video to show us how to add products into this invoice from a products sheet, customer information to be saved into a customer sheet if customer is not already in the sheet, other choose a customer from existing customer sheet. 💚💯💥
Wow everything was great , I was trying to give basic input, with tht input BoQ shld add item automatically. And print the value in quotation form , can we do it ?
I have made a form and have added a script for numbers to text in the response spreadsheet. Now after filling the form for the invoice, I want the total in words. How to auto-populate it response sheet for every new entry?
As a follow on, could you show how to fill in the unit price using some lookup from a product list on another sheet? I'm not sure vlookup would work if the columns changed.
Can i put a formula like this = Q4+H5-I5-P5 in an arrayformula? i need to add the last value to the new one its not like the invoice its the first plus the second then the third plus the last sum to see the changes after every operation
The idea is I want to fill an invoice template with the lines of client debts from another table and then to export it as pdf. But the problem is that all the clients doesnt have the same amount of line. So I would like to make a script that will generate a pdf with a dynamic number of pages according to the number of lines - while it use the invoice template for the header and footer of each page
@@ExcelGoogleSheets No, i mean from sheet 2 or page 2 (products) select the products with a tick-box, select the client and automatically pass them to the invoice.
Awesome video. A COUPLE OF QUESTIONS? How do you make the date active? How to make invoice #'s sequentially count automatically or track sequentially to next invoice # from last#? How to make due date be the same as current date unless a different date value is manually entered? (This code would be applicable to the payable to: cell too). How to make it so not every line item is taxable?
I freaking love this guy. Creating this invoice - not as sophisticated as this guys, but helped me on my first day on a new job. I need more! Love this guy.
Ive been using that offset formula for years! And never came to me the idea of inserting a header! Gosh you are amazing pal, thx so much
So much gold in this lesson! And FINALLY a practical use for the offset function! Many many thanks
Thanks! Somebody gets it! It's not about the final product, it's about the technique.
Yeah, really practical! Especially for those comfortable with arrayformula() and literal array {}
It always worth watching your videos, even when the title seems to be just a simple tamplate lessons. As mentioned before, many golds inside. Just learned about offset!
Wow, I know its 3 years old now and I plan on looking at some of your newer stuff, but man, you made this so easy to follow and learn from. Thank you.
Excellent explanation and inspiring techniques to apply to solve problems, that I never thought there could be a way out.
Thank you very much wholeheartedly for your great videos. I do really appreciate your efforts. And please keep up the good work.
Your channel is handsdown one the best channels on TH-cam in terms of content, both quality and quantity!
Thank you very much you changed my life, you deserve at least one million views
really practical and handy. you deserve more subs. thanks.
Excellent videos! It's refreshing to get good, practical, advanced tutorials so well-presented and at a decent pace. Keep 'em coming :-)
Much appreciated!
Cool, like all your videos! There is one thing with merged cells. Click on any merged cell in the sheet to make it active > Ctrl+A > go to the main panel "Merge cells ▾" and click that tiny arrow > "Unmerge". It works only if a merged cell was selected before selecting all.
That's a great use of OFFSET and Array formulas.
Nice work!! It makes me happy!! You are innovative guy!! Ypur videos are very helpful it makes my work much easier. Thanks
Thanks a lot mate, I have been struglling with this all day, I appreciate it
One more amazing tutorial... Thanks a lot 🙏
Perfect! Thank you.
Great video! Thank you. This will be very helpful for my small business.
amazing video as always. thank you for sharing.
thanks, great way to learn the google sheets, i appreciate it
Excellent tutorial, I learned more than I needed.
This is helpful. Thank you so much.
Thanks for making great free content!
Your template tutorials are my favorite
Glad you like them!
Excellent! Thanks!
I'm just using scripts to automatically approve cash request in our small company! Saving us precious time! Many thanks, man!
Can you tell more about your scripts?
nice video thanks for help and ideas!!
Great tutorial
Thanks for the informative video!
SUPER COOL , THANK YOU!! : )
Thanks a lot very helpful!
This is great learning!
Great video as always. Have you done, or can you do a video on putting each invoice's key information in a master database? Thank you!
Thank you.... Great video!
Very informative! Thanks Much!! Now to expand on this: have a form input which auto imports info (purchase) into the Template and sends email confirmation to customer and to distribution officer. That would cut out a lot of work. Just a thought for future tutorials.
I've made 2 videos recently to generate and email PDFs that cover 80% of this, one of them also includes using Google Forms.
Thank you!
Great video. I was really hoping you would show how to reset the template after I print/save the one I worked on. It would be time saving. I wouldn't need to delete all information from the previous template one by one and then start making a new invoice
you are amazing pal, thx so much
Thank You!
Hey - love your work! Using the above principle I constructed a fairly complex Project Management spreadsheet, which seems to be working fine, except for the fact that my array formulas are not "auto-updating". I checked the sheet calculations settings and bumped up iterations, but to no avail. The only way it updates now is, if I delete the "content" of the cell and let it repopulate. Would really appreciate some guidance with this issue. Thanks!
Wow, offset is cool. usually i use indirect
would you be able to group 3 of those order lines and add a subtotal below, and then have 3 more with their sub total?
I mean, of course it is possible, but is it possible with the 1 array formula above that basically has to do AND row totals, AND decide when to do a subtotal on a dynamic number of rows above it.
This might seem like something that needs a custom appscript or am I overtinking it?
Thank you
great video. But after you made your invoice, how do you save them to a data base to be able to retrieve later? And only bad thing is doesn't look like you can set up the invoice numbers to go in sequential order so you need to look at the previous one you made to know what invoice number to use next...?
Thank You🎉👌🎉
Is there a way to auto generate invoice nos. As well as save it to a file on google drive?
Great video.... Following up with this invoice..... How can I capture the variable data of this invoice and paste them in a table in an other sheet...... 🙏
Many Thanks dear, i am your subscriber and have been learning a lot from your videos, this video is also great and teaches about the offset function, however i was wondering why did google made the 22nd row hidden, and apparently discovered that they applied the sum formula from 18 to 22 and then purposely hid the 22nd row so that the calculation works well even if a new row is added BELOW the last row ( thats visible - 21st row ) , I applied the same logic to the 18th row and hid that as well, so basically I got to learn 2 new things from your video. THANKS BUDDY.
Thanks to your video I am now able to make an invoice template with the features I need. However, I was wondering if there is a way to create a function in this template with some sort of dropdown menu with the customer's info. So lets say by selecting "Plastering Business" it automatically fills in their business information. And if possible; is it also possible to create an automated function to E-mail it to said customer?
Dears this is amazing presentation thank you so much can you add on this the profit and lost calculation.by separate way for eg. By day or by item. Thank you
could you advise how to populate data collected from google form to invoice, then send email automatically in a PDF?
Amazing! Beautifully Done Sir! Your on the Right Track, but your only about 80% there in order to make it Perfect IMHO ;)
I left the other 20% for you to complete :)
Learn Google Spreadsheets ... LoL! Great Answer...I should have seen that one coming for sure!
Seriously though, its that Last 20% that's way out of my Abilities. I know I have been after you for quite awhile now about this, probably why you made This Video. Let's see if I can Communicate this to you Clearly. In your Video, the User has 9 Lines of Entry until Data is Pushed to the Next Page. That is where Invoice Templates Fall Short. Page 2, 3, etc. do not need all the Header Data from the First Page, just more Item Lines and the Footer Portion only on the Last Page. Every Page should have a: Page 1 of 1/2/3... and a File Name/Number Entry Inserted Automatically. Also, the Item Lines should be Added Automatically whenever the Last Item Line is Filled In. BTW...Item Lines should be Automatic Starting from the First Item Line despite Insertions or Deletions.
This may Require Scripting?
@@cheewurz This is why I gave up googlesheet and used RapidBooks Invoice Maker. Using a professional Invoice Maker would not have such a problem. Of course, there will be a lot of room for customization to use Google Sheet. But It’s enough for a Small Business to use RapidBooks
say i wanted to hide the array formula in a title 2 rows above my value section. example there was a space between my header and my first value. is there a way to make the array formula add 2 rows instead of 1 when i add the semi colon?
is there a way to get the last cell reference dynamically without hard coding it in the formula for example,
i have a range from a2:a10 so i need the reference to get a10 and once i add new data say like another 10 rows it will reference cell a20
Thanks a lot
Actually, I created a new google account just to watch your videos and use Google Drive only to practice google sheets. You inspire me.
OMG, this is really cool, can you make a video to show us how to add products into this invoice from a products sheet, customer information to be saved into a customer sheet if customer is not already in the sheet, other choose a customer from existing customer sheet. 💚💯💥
will you make a another sheet for tracking of invoice?, i mean connect a database.
Great Video ! is there any way to stop downloading of google sheet, but allowing editing ? kindly help
Wow everything was great , I was trying to give basic input, with tht input BoQ shld add item automatically. And print the value in quotation form , can we do it ?
Spreadsheets is a little difficult. I suggest you use professional Invoie and quote maker, such as RapidBooks
Very helpful, thanks. Please is there any way to add subtotal to each page if the invoice has many printed pages?
In this case, I suggest you use professional Invoice Manager, such as RapidBooks
I have made a form and have added a script for numbers to text in the response spreadsheet.
Now after filling the form for the invoice, I want the total in words. How to auto-populate it response sheet for every new entry?
🙏🏻
As a follow on, could you show how to fill in the unit price using some lookup from a product list on another sheet? I'm not sure vlookup would work if the columns changed.
Sure, VLOOKUP would be fine. Just use OFFSET for lookup key range.
Can i put a formula like this = Q4+H5-I5-P5 in an arrayformula? i need to add the last value to the new one its not like the invoice its the first plus the second then the third plus the last sum to see the changes after every operation
If you use RapidBooks, it will be a lot easier
4 months ago, but found it just now , 😭😭😭
How do you get this to print a full page ?
Hello sir is there any way to add direct print function
Hi do you have any video that could help me with creating variable length invoices from data of a spreadsheet?
The idea is I want to fill an invoice template with the lines of client debts from another table and then to export it as pdf.
But the problem is that all the clients doesnt have the same amount of line.
So I would like to make a script that will generate a pdf with a dynamic number of pages according to the number of lines - while it use the invoice template for the header and footer of each page
Could be possible send this invoice by mail in PDF or jpg wihtout loosing the format?
It is.
Can you make a video pulling data from other sheets?
IMPORTRANGE ?
@@ExcelGoogleSheets No, i mean from sheet 2 or page 2 (products) select the products with a tick-box, select the client and automatically pass them to the invoice.
You may benefit from watching this th-cam.com/video/nAcNgmYPA-4/w-d-xo.html
@@ExcelGoogleSheets How about populating the invoice from the inventory items
NO MORE INVOICE TEMPLATE FOR 2021?
How to save all the data from invoice from all customers?
can we use other currencies?
Sure.
first!
Awesome video. A COUPLE OF QUESTIONS?
How do you make the date active?
How to make invoice #'s sequentially count automatically or track sequentially to next invoice # from last#?
How to make due date be the same as current date unless a different date value is manually entered? (This code would be applicable to the payable to: cell too).
How to make it so not every line item is taxable?
Thank you