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 !
Hi @AnahiSidonio-uc5dh - you are in luck ! I've literally just published a short on highlighting overdue invoices: czcams.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: czcams.com/video/nu06D2Qj8No/video.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 !
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? czcams.com/video/OADE34B56hM/video.htmlsi=_GPOUMZEFwBSCI2k We can also create bespoke solutions for you, check out our website bloomfieldanalysis.com/sting
Then watch this video ! :) czcams.com/video/OADE34B56hM/video.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: czcams.com/video/OADE34B56hM/video.html&lc=UgwWEx-9V4_2f0ihktd4AaABAg , let me know if this is what you wanted, and enjoy!
Think what you need is this video 😊Create a | Automated | Double Dropdown [Dependent] Price List | Google Sheets / Excel czcams.com/video/OADE34B56hM/video.html
Hi! This is our video on printing invoices to PDF: czcams.com/video/oNzaij7f5yM/video.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.
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: czcams.com/video/OADE34B56hM/video.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 !
Hello. Is there any way to automatically download all invoices at once instead of switching manually and downloading one by one? If so this would be super awesome because I have almost 200 invoices to download lol
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 😊🐝
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 czcams.com/video/LmwS_HmNfuY/video.htmlsi=LSqPUSL1peUQtYdl and this is more complex but more flexible and realistic if you have lots of different items: czcams.com/video/OADE34B56hM/video.htmlsi=xpWR9KQpTsiXwQPr
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 :) czcams.com/video/oNzaij7f5yM/video.htmlsi=b28PQFWYPZzdS28c But if I'm misunderstanding what you mean by printing multiple items, please let me know!
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
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!
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?
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!
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: czcams.com/video/OADE34B56hM/video.htmlsi=Vs7sPGFPYZs6ZDpp And please subscribe for more or browse the channel page czcams.com/channels/yqHAMnFt2yTyYgYsxQMCjQ.html
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! czcams.com/video/oNzaij7f5yM/video.htmlsi=SnGEVXQgfgWSjhNf
Hi! For 2 description lines, in a double drop down format, I think think this is the best video: czcams.com/video/OADE34B56hM/video.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!
Thank you for watching! Want to learn more ? For more videos: czcams.com/channels/yqHAMnFt2yTyYgYsxQMCjQ.html Free templates from our website when you sign up to our newsletter: www.bloomfieldanalysis.com/sting If you want to add a simple price list (one item per invoice) : czcams.com/video/LmwS_HmNfuY/video.html If you want to add multiple items and a double drop down price list: czcams.com/video/OADE34B56hM/video.html If you want to print multiple invoices to pdf: czcams.com/video/oNzaij7f5yM/video.html Or do something different and create a leaderboard: czcams.com/video/PryrvvSaNkE/video.html If you would like us to build you your own bespoke invoicing solution go to: bloomfieldanalysis.com/booking No time to learn? Buy ready made premium templates: etsy.com/shop/bloomfieldanalysis
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 😊
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: czcams.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: czcams.com/video/nu06D2Qj8No/video.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 !
Boss man lol. Great video. Thank you
Thanks :D !
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? czcams.com/video/OADE34B56hM/video.htmlsi=_GPOUMZEFwBSCI2k We can also create bespoke solutions for you, check out our website bloomfieldanalysis.com/sting
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 ! :) czcams.com/video/OADE34B56hM/video.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: czcams.com/video/OADE34B56hM/video.html&lc=UgwWEx-9V4_2f0ihktd4AaABAg , let me know if this is what you wanted, and enjoy!
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!
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
czcams.com/video/OADE34B56hM/video.html
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: czcams.com/video/oNzaij7f5yM/video.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 helpful, thanks a million
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: czcams.com/video/OADE34B56hM/video.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 !
Hello. Is there any way to automatically download all invoices at once instead of switching manually and downloading one by one? If so this would be super awesome because I have almost 200 invoices to download lol
Nevermind I've just found the video where you explain it. Incredibly helpfull information. Thank you so much
great - glad you found it ! sorry for slow response 😊
great video, thanks!
Thank you, glad you like 😊
Thanks every helpful
Glad to hear that! :) Thank you for your comment!
Thank You
Pleasure 😊
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
😊🐝
So, I got it working, I rewrote the formulas, I think I'd missed some &s and "s. But it's working now. Thankyou so much for your help!!
@@ibz844 Great 😊
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
czcams.com/video/LmwS_HmNfuY/video.htmlsi=LSqPUSL1peUQtYdl
and this is more complex but more flexible and realistic if you have lots of different items: czcams.com/video/OADE34B56hM/video.htmlsi=xpWR9KQpTsiXwQPr
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 :) czcams.com/video/oNzaij7f5yM/video.htmlsi=b28PQFWYPZzdS28c But if I'm misunderstanding what you mean by printing multiple items, please let me know!
that's genius
Thank you ♥️
Pause at 1:08
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 !
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!
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?
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!
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: czcams.com/video/OADE34B56hM/video.htmlsi=Vs7sPGFPYZs6ZDpp And please subscribe for more or browse the channel page czcams.com/channels/yqHAMnFt2yTyYgYsxQMCjQ.html
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! czcams.com/video/oNzaij7f5yM/video.htmlsi=SnGEVXQgfgWSjhNf
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: czcams.com/video/OADE34B56hM/video.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!
Thank you for watching!
Want to learn more ?
For more videos: czcams.com/channels/yqHAMnFt2yTyYgYsxQMCjQ.html
Free templates from our website when you sign up to our newsletter: www.bloomfieldanalysis.com/sting
If you want to add a simple price list (one item per invoice) : czcams.com/video/LmwS_HmNfuY/video.html
If you want to add multiple items and a double drop down price list: czcams.com/video/OADE34B56hM/video.html
If you want to print multiple invoices to pdf: czcams.com/video/oNzaij7f5yM/video.html
Or do something different and create a leaderboard: czcams.com/video/PryrvvSaNkE/video.html
If you would like us to build you your own bespoke invoicing solution go to: bloomfieldanalysis.com/booking
No time to learn?
Buy ready made premium templates: etsy.com/shop/bloomfieldanalysis