How to | Create an automated Invoice | Google Sheets / Excel
ฝัง
- เผยแพร่เมื่อ 16 มิ.ย. 2024
- How to make an automated invoice, using google sheets, same techniques can be used in excel. Ideal for contractors / freelancers / people using YunoJuno. One click to fill in the information from your database. No Apps Script or other coding - just basic formulae! :)
Download your free template at www.BloomfieldAnalysis.com/sting
Timings:
0:00 Example
0:42 Create a template
3:32 Set up the database
6:21 Automate filling in the database
14:27 Formatting - วิทยาศาสตร์และเทคโนโลยี
so helpful, thanks a million
This is great thank you 😊.
came across a neat tip to easily Change INV-0001.
in that cell F12, create a Dropdown (from a range)
Data > Data Validation > Add Rule > Apply to Range, Invoice!F12 > Criteria, click Select Range & to Database sheet & click A column. done.
or type custom formula =Database!$A$2:A
Advance options: Plain Text (optional) just to get rid of the Dropdown Arrow.
Now you can double Click on INV-0001(F12 cell) & select From a list. these are attached to your Database sheet so select one & itll auto populate like normal.
the more you add to Datatabase sheet, the dropdiwn menu in F12 will auto update as well
Thank you for this tutorial!
Yes ! This works really well, in other invoice solutions I've created I use drop downs exactly like this. Its makes things simple to use, reduces typing and therefore typos :) Thank you for sharing !
Thanks alot!! Definitely gonna add this into my assignment eventho im not an IT student but somehow Excel is quite fun to play around with
Glad it was helpful! Yes, I love playing around with excel & google sheets, especially building little tools 😊
great video, thanks!
Thank you, glad you like 😊
This is literally the BEST tutorial. THANK YOU! Could you please also make an invoice tracker I’m begging!!!
Aww, thank you! What were you thinking with the invoice tracker? what would you need? A dashboard / summary - or highlighted overdue ?
@@bloomfieldanalysis mostly highlighted overdue
Hi @AnahiSidonio-uc5dh - you are in luck ! I've literally just published a short on highlighting overdue invoices: th-cam.com/users/shortso0XZxqII5y4?feature=share This is excel, its the same formula in google sheets, just under Format/Conditional Formatting/Custom Formula. This video may also be of use: th-cam.com/video/nu06D2Qj8No/w-d-xo.html I should be back to longer videos shortly, just testing whether shorts can help get me to 1000 subs a little faster, less than 20 to go !
Thanks, helped me
Fab! Good to hear :)
Awesome video, how to add the invoice data to the database sheet? Mines isn't moving to the database sheet or updating.
Hi! Thanks for watching and your comment :) Have you tried downloading the template from the website www.bloomfieldanalysis.com/sting ? That has a free working version. The idea is you update the database and then this will flow through to the invoice page. I've updated the online version so that you can add multiple items (in the items tab), the most up to date video is here: th-cam.com/video/OADE34B56hM/w-d-xo.html But if you'd like the older version where it all comes from one database sheet, email me at bloomfield.analysis@gmail.com and I'll send you a link. Thanks again !
Thank you for watching!
For more videos: th-cam.com/channels/yqHAMnFt2yTyYgYsxQMCjQ.html
If you want to add a simple price list (one item per invoice) : th-cam.com/video/LmwS_HmNfuY/w-d-xo.html
If you want to add multiple items and a double drop down price list: th-cam.com/video/OADE34B56hM/w-d-xo.html
If you want to print multiple invoices to pdf: th-cam.com/video/oNzaij7f5yM/w-d-xo.html
Or do something different and create a leaderboard: th-cam.com/video/PryrvvSaNkE/w-d-xo.html
Download FREE templates: BloomfieldAnalysis.com/sting
OR Subscribe and access ALL TEMPLATES in the Community tab: www.youtube.com/@bloomfieldanalysis?sub_confirmation=1
If you would like us to build you your own invoicing solution go to: bloomfieldanalysis.com/booking
Hi, what do I do if I were to have many items for the "Description" list, and don't want it to overlap to the next invoice's "Description" items?
Then watch this video ! :) th-cam.com/video/OADE34B56hM/w-d-xo.html&lc=UgwWEx-9V4_2f0ihktd4AaABAg You can also download the template "Invoice with items and price list" from bloomfieldanalysis.com/sting , these have "category" and "item" as a double drop down, but if you want a simpler item list: th-cam.com/video/OADE34B56hM/w-d-xo.html&lc=UgwWEx-9V4_2f0ihktd4AaABAg , let me know if this is what you wanted, and enjoy!
I've had a look at this and it could be what I'm looking for. I work with an excel database of customers/jobs and at the moment manually create a quote with the same information and if it goes ahead, eventually create an invoice with the same information. So far so same as your tutorial (and the one to create an invoice and quote from the same database). However, I'm now stumped as occasionally some clients ask for quotes for several jobs - these would normally have the same quotation number, so how do I get around adding more items to the quote? I'd be really interested and grateful for your answer. I am not an expert by any means and had to slow your tutorial down to follow it and copy the formulas haha.
Hi! Yes, sounds like you've watched the most relevant videos :) Always best to have unique quote numbers, although if you are printing/downloading to pdf at least you have a back up copy. Should be easy to add more items, have you seen this video? th-cam.com/video/OADE34B56hM/w-d-xo.htmlsi=_GPOUMZEFwBSCI2k We can also create bespoke solutions for you, check out our website bloomfieldanalysis.com/sting
All invoice templates are deleted from your website. I cannot access the sheets. Any way to fix this?
Thank you for alerting me! This has now been fixed. Apologies!
Thanks every helpful
Glad to hear that! :) Thank you for your comment!
Hi what if we ad item 2 in description? ‘ how do its add on database
Think what you need is this video 😊Create a | Automated | Double Dropdown [Dependent] Price List | Google Sheets / Excel
th-cam.com/video/OADE34B56hM/w-d-xo.html
Hi, thank you for great tutorial. i use it to create packaging catalog. can i want to print multiple items at one?
Aww, so nice to hear what people are using it for! Print multiple pdfs at once? This is a question I had a few times, this video may help :) th-cam.com/video/oNzaij7f5yM/w-d-xo.htmlsi=b28PQFWYPZzdS28c But if I'm misunderstanding what you mean by printing multiple items, please let me know!
Hey ive got a problem, in my sheets ive got an the same number of invoice in various rows (e.g invoice 0016 is in row 10-17), therefore your explanation doesn't work. What can I do instead? sorry im a noob at this
Invoice numbers need to be unique. Why do you have multiple invoices with the same number? is it for multiple items? if so, one of these videos may be useful: this is a simple items menu
th-cam.com/video/LmwS_HmNfuY/w-d-xo.htmlsi=LSqPUSL1peUQtYdl
and this is more complex but more flexible and realistic if you have lots of different items: th-cam.com/video/OADE34B56hM/w-d-xo.htmlsi=xpWR9KQpTsiXwQPr
Hi, I've got this working, I also have a separate workbook with just my invoice in it so I can produce a PDF version from my phone on the go, is there a way of referencing the data using the IMPORTRANGE function? I've tried it but can't seem to work out what I need exactly... TIA
Hi @ibz844 You should be able to get anything in the spreadsheet to another using IMPORTRANGE, not sure why it wouldn't be working... maybe the sheet id? or making sure you have verified access through the importrange - google are getting more fussy about access... although if you own both spreadsheets it seems overkill! Why do you need a separate version? You should be able to access on your phone using the google sheets app, and to cheat, I would probably screenshot the invoice page, and crop it, to send on the go. And make sure invoice number is on a dropdown. But that might not be what you want?
@@bloomfieldanalysis Yeah, it's just a separate, invoice only, workbook that I can export as a PDF, you can't export single sheets on the iPhone google sheets app. The IMPORTRANGE works normally, but not in conjunction with MATCH. Not to worry, thanks for your response
@@ibz844 Ah, thats annoying! One way round, that I have working for another client, is that you do the match function elsewhere in the invoice sheet. We call this "internal reference" - or you could hide it somewhere. This will get you the row number, that you can then put in the index match function, like this: =IMPORTRANGE("docs.google.com/spreadsheets/d/[sheet id]","[tab name]!h"&E6) - this is looking in column h and the row number is in cell e6.
Hopefully thats more helpful
😊🐝
Thank You
Pleasure 😊
Hello, I trying to repeat this but I'm totally confused after 12:00 min when you mess the formula, how did you change and repair it, cause I totally mess and can do it... you just copy and paste some $ but in my case they doesn't work. Could you explain how did you do the case with Project?
Hello !
If you want to use a formula without $ you can use this formula for project:
=index(Database!H:H,match(F12,Database!A:A,0))
This looks for the invoice number that’s in F12 in the database column A. And will tell us the row number.
It then gets the project number for that invoice number by taking that row and finding it in database column H, which has the project number numbers in.
As where the invoice number and row doesn’t change you can fix them by putting in the dollars using the keyboard $ sign or by having the pointer on the cell reference and rotating through. One click fixes row and columns, a second click fixes row only, third column only, four clears the fixing.
If you fix the invoice references like below:
=index(Database!H:H,match($F$12,Database!$A:$A,0))
Then you can copy and paste this formula elsewhere but update the column reference when you are looking up other items. This should simplify the formula so you can see what’s working and what’s not. Hope that helps!
@@bloomfieldanalysis I tried but doesn't work I will check that again but if I use the first formula which you used for Row Number give the same result.
I’m not sure I fully understand the issue, have you downloaded the template from Bloomfieldanalysis.com/sting ? It’s got a full working version and you can play around with that
@Bloomfield Analysis greatly! appreciated !
that's genius
Thank you ♥️
Hii nice Video, But I have a question what if I wanted to print all the invoices in a single PDF file like one after other can I do it
Hi! This is our video on printing invoices to PDF: th-cam.com/video/oNzaij7f5yM/w-d-xo.html Unfortunately, if you are on the free version, Google Sheets has a max you can do at once, but you can do about 6-8 at a time.
Use apps script on google sheets extensions.
So I need a year of learning google sheets before I can use google sheets. 😂
I am an idiot so skipping over that I have followed your WONDERFUL tut here 6 times or more...but I cannot get it to update when I change the invoice number and I am obviously missing something significant.....is there a trouble shooting list maybe? What dose it do when i change the invoice number? absolutely nothing. HAH
Hi! Glad you like the video ! Sorry it’s not working for you, commenting on here is great, because I’m sure a trouble shooting list will be useful for others too!
1. The first thing I would do is instead of writing the invoice number in the invoice page I would copy it from where it is in your database into the invoice cell. Just in case it’s not an exact match, e.g has a space somewhere.
2. Try the match function on its own Match([the invoice number in invoice tab],[the full column of invoice numbers],0) if your invoices are not in alphabetical order you need to make sure the 0 is there so that it’s an exact match. Check what row number you are getting and that it changes to the right one when you change the invoice number.
3. Make sure all invoice numbers are unique.
4. Check the index function by index([column that you are trying to get],[row number from your match function). If this works you can replace the row number with the match formula instead so it’s neater,
5. If it’s still not working, go to BloomfieldAnalysis.com/sting and download the free template - it’s always being updated and may also have new functionality that’s useful - I’ve just added a drop down price lis!
pdf?
Yes, you can download pdf, or print to pdf. You can also use this video to learn how to print multiple invoices to pdf to a google drive! th-cam.com/video/oNzaij7f5yM/w-d-xo.htmlsi=SnGEVXQgfgWSjhNf
How did you auto fill your address?
Do you have different addresses depending on who you are invoicing? I have mine always fixed to the same address, so I dont autofill, just plug in once and its there, if you have different addresses at the top, you'll need to have something to lookup, like : "Registered Address" - then do the usual lookup, you might want a larger cell, so merge some below rathan than multiple lines, hope that makes sense!
need demo spreadshhet
Go to Bloomfieldanalysis.com/sting
Watched this about 40 times gone along step by step and I cannot get it to work.
Oh no! Sorry to hear about that ! What part exactly are you having trouble with ? Have you tried downloading one from bloomfieldanalysis.com/sting and then you can compare versions?
I guess no one sells multiple items to a customer on one invoice.
Of course they do! Theres a few options for multiple items, but this is my favourite option, create a drop down list with a broader category and then items within that: th-cam.com/video/OADE34B56hM/w-d-xo.htmlsi=Vs7sPGFPYZs6ZDpp And please subscribe for more or browse the channel page th-cam.com/channels/yqHAMnFt2yTyYgYsxQMCjQ.html
Pause at 1:08
Convinced. What about multiple description lines? Say five-ten items.
Hi! For 2 description lines, in a double drop down format, I think think this is the best video: th-cam.com/video/OADE34B56hM/w-d-xo.htmlsi=SA9pFTQWIRpQdV_e I've not seen a case yet where a triple drop down is necessary, but it is of course possible! Or if you are thinking more lines down to fit in more items, you can delete some rows else where then insert some rows in the middle. The best thing about learning how to do it yourself is you have complete control and can adapt to fit your needs. Hope that helps!