I'm the visual basic coding - When adding in the sheet ranges (ie. cell numbers from the invoice template) it is important to include the sheet as well. In this video it just had, for example, "Invno = Range(" C6"). If you have multiple sheets include the sheet name as well, ie. "Invno = Sheet1.Range(" C6"). I knew very little prior to watching but I'm starting to really understand the backend now. Thanks so very much. So much increased productivity and data collection.
This tutorial is brilliant. Followed along step-by-step and even adapted some of it to suit me specifically. It's exactly what I was looking for and it's professional as hell
OMG IT WORKS... just found vbs the other day and im going crazy ! it might be better than QR codes at this point. now the last thing to do is figure out the pdf links part as i keep having issues and then email. this vba has taken 2hrs of my morning down to 3 min !
This concept looks like exactly something I need. Are you available to set something like this up to suit my specific business requirements? Please say yes!
GREAT breakdown and thank you for this !!. I downloaded your excel template and moditfied it. So this is really helpful in making sure i fix the macor's to to the right cells now and it works like a charm . Thank you for this
This tutorial is really good. Can be done the same but for sea shipping quotations that work on excel 365 ? I tested this invoice and office 365 don't let run any of the buttons ( VB ). Thank you for your kind feedback.
Hi Chester Really appreciate this video series.!!! I think I've watched at 4 times over. I am adapting to suit my own invoicing and quotation needs. Sometimes my invoice / quotes are larger than one page, so is there a way make the amt dynamic, instead of being fixed to "I41"?
Thanks for the videos.. Have made a similar Invoice to the one in How to Create an Automated Invoice in Excel | Including Formulas and Customer Database. Then use the video (Create an Invoice Tracker in Excel) worked fine for the first now its adding to info correctly.
Hi Chester, awesome tutorial, thank you. In my record of invoices the date displays as 01/01/1900. Can't seem to change that. Any suggestions as the date on my invoice is a formula =Today()
Excellent and very useful. Only one thing I noticed is that there will be duplicate of records when I do any two of the actions (Add to record, save as .xlsx, save as pdf and email the pdf). It should add to the same record.. Thanks a lot
This was excellent. I successfully did it in Excel. However, I now need to do all of these in Google Sheet, but I am not sure how. Could you make a video performing these macros in Google Sheet? Thank you.
Another great tutorial. I have one problem with the conditional formating. As soon as there is a date in the Date Due column the colour disappears?? Any ideas. Thanks again for making the seemingly impossible look easy...
What a great series of videos. I'm in the process of adapting my own needs to the template but I would have never gotten this far without your videos. One thing I cant figure out is I want the Record of Invoices to put the newest on top instead of the bottom. I've checked out your other videos on sorting but cant make it work. Is there a VBA code that will work without the data being in a table?
Hi Chester, Great videos, I have been trying to get the next invoice to the next row of my tracker for weeks. Everything worked fine except my first row starts at 18, fixed that but the next invoice will override the Sam row. Any suggestions?
I have learned a lot.Thank You! How Would I create a Summary page that shows amounts paid for that month if Paid columns says 'yes'. Like reconciliation of what is already paid for the month.
Great tutorial, Chester. However, I have a problem. when declaring invno= Range - there's a problem as Range is declared 'an invalid property'. Can you help?
First of all, thanks alot for this great tutorials. but i need to ask if i can save that record using macros in a seperate spreadsheet ? don't even bother telling my how, just please let me know if it is possible. thanks alot in advance
Hi! Thank you for the tutorial! I have a question. I have a situation where I have a lot of worksheets (one worksheet per invoice) and have one Record of Invoice sheet that displays certain information from each sheet, just like the video example. However, I want the Record of Invoice sheet to reflect any changes made to the each invoice, instead of creating a new line. What would you recommend in this case?
An awesome addition to this would be to set up a customer info sheet that auto populates the customer database, and generates a customer ID number. Why? For a nifty interface and some place to have additional fields like customer notes and scope of work a place to create a payment schedule and a contract for them to sign etc.
Very helpful tutorial! Only issue I'm having is when I run the macro, because I am using an alphanumeric invoice number with symbols (-), the value comes up as false on my record of invoice :( any idea's how to fix please!?
Weldone Job! Sir I tried to modify this template and it was very easy. Now I want to add a Spell Number and Outstanding Payment brought forward. I need help!
This is impressive Chester Tugwell. I have learnt a lot from this tutorial, thank you for very much. Is it possible to group the records of invoices according to Customer Name?
Followed the tutorial and adjusted as needed. Excellent work. I am very new at this and hit one snag. I am using an invoice with a drop-down list for driver names and I cannot figure out how to code it (driver name) to pull over into the invoice register. All other values come over just fine. Can anyone help?
Hi Chester really enjoying your tutalge. i opend Visual basic editoe and only a grey screen appeared. nothing was populated in. do you know what has gone wrong? thanks Chris
Great tutorials thanks, invoice created fine and now woring throgh this....I keep getting a Compile Error highlighting the Sub RecordofInvoice(), ther error states: Expected Sub, Function, or Property...I cant seem amyhting different between the video and what I have inputted but I cant seem to fix the error...complete excelnoob here just to be clear!
Hi Chester, could I ask how can I set up the tracker, when customer pay installment (like 50%, 40% and 10%), and invoice number start like ABC00001. Thanks
Excellent video. So far everything has worked very well and looks very professional. I keep getting an error connected to this line: "Set nextrec = Sheet2.Range("A1048576").End(x1Up).Offset(1, 0)" The error says: "Runtime Error '1004'. Application-defined or object-defined error." I don't know how to fix it and can't get it to play. Can you please advise? Thank you so much!
I see my mistake. I wrote "(x1Up)" instead of "(xlUp)". I have another question if you can please answer. For the formatting rule you made where the line is red until it turns clear by writing in YES, I am trying to make an additional rule where if there is a partial payment, I am writing PAR and the line should turn light blue. I copied the formula and replaced YES with PAR, changed the color to blue, but it will only do blue even when it says YES. Can you PLEASE advise? Thank you!!
this tutorial is very helpful, but I need some assistance here. When I tried this macro, I can't change my currency into Philippine peso and my date became the default time. How to change it?
i did everything just like you showed but when i enter employee it on records it on one line and nothing else it keeps changing only at that one Colum .please help
I followed you tutorial step by step but for macros for the invoice tracking is not working I typed in nextrec .offset(0, 3) = dt_issue and excel is giving me the time not the date. I keep going back and looking very carefully at your coding and it still not giving me the dates
Hi sir, Your video is excellent. Pls explain how to create invoice for multiple copy pdf for 1.original for buyer 2. Duplicate for transporter 3. Triplicate for central exercise 4. Extra copy.
my via function of Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1,0) is not working I have put it exactly as it was written in the video and yet I cant find to get it to work
Thank you, these videos are very helpful. I do have one problem though. When I run the macro I keep getting a compile error, saying the sub or function is not defined. It refers back to the Sub RecordOfInvoice(), Am I doing something wrong?
hello, is it possible to provide invoice number with year and month? For example "2022.06-0001". I very kindly request you, if you wish, to clarify that. My thanks are enormous.
Dates are showing 0/01/1900 in date issued column, term shows todays date and the ammount is $44,794 when it shows $176.00 on my invoice??????? PLEASE HELP!!!
Hello, little question. So I accidentally removed 1 line in the 3th sheet with customer data. Now every once i press the play button of the macro, it's all 0's and it says 12:00:00AM where the due date is... Help?
Hello, just saw you comment. I am on Mac and I was having the same issues. I was getting 0's and the 12:00:00AM, my problem was when I was playing the Macro I was in sheet3 looking at my Record Of Invoices. I had to be on the actual invoice and then played the Macro so it could move everything properly. Make sure you are in Sheet1 or the invoice when playing the Macro. That at least was my fix for this on Mac. Hope it helps!
you have to change the cell format, where ever the date column is select the whole coloum(deselect heading) rightclick, click on format cells, select date and its type suitable to you
Y'all!!!!!! I just figured out my problem! My dumbass kept running the module on the record page (when it should have been on the template page)....hence the button...I just wasn't ready to commit to a button until I figured it out lmao
I'm the visual basic coding - When adding in the sheet ranges (ie. cell numbers from the invoice template) it is important to include the sheet as well. In this video it just had, for example, "Invno = Range(" C6"). If you have multiple sheets include the sheet name as well, ie. "Invno = Sheet1.Range(" C6"). I knew very little prior to watching but I'm starting to really understand the backend now. Thanks so very much. So much increased productivity and data collection.
This was SOOOOOOOOO Helpful to this great video. Thanks to both of you, Chester Tugwell and @natbal77
This tutorial is brilliant. Followed along step-by-step and even adapted some of it to suit me specifically. It's exactly what I was looking for and it's professional as hell
Thank you very much. I have been found until I watch your video. Thank so much again
OMG IT WORKS... just found vbs the other day and im going crazy ! it might be better than QR codes at this point. now the last thing to do is figure out the pdf links part as i keep having issues and then email. this vba has taken 2hrs of my morning down to 3 min !
Building a drop down for a customer list isn’t working for me. Tried 10 times step by step …. No worky frustrated.
Great tutorials. Easy to follow and use! I do have a question though. If I am using MS office outlook, how do I get it to send it through there?
This concept looks like exactly something I need. Are you available to set something like this up to suit my specific business requirements? Please say yes!
GREAT breakdown and thank you for this !!. I downloaded your excel template and moditfied it. So this is really helpful in making sure i fix the macor's to to the right cells now and it works like a charm . Thank you for this
Thank you. These are fantastic videos. Now how to I remove all of my "test" invoices from my "record of invoices"? Thanks for the advice
This tutorial is really good. Can be done the same but for sea shipping quotations that work on excel 365 ? I tested this invoice and office 365 don't let run any of the buttons ( VB ). Thank you for your kind feedback.
Very useful, thank you so much. I noticed that my invoice sheet is different than yours by one cell... I am doing step by step with you.
Hi Chester
Really appreciate this video series.!!! I think I've watched at 4 times over.
I am adapting to suit my own invoicing and quotation needs.
Sometimes my invoice / quotes are larger than one page, so is there a way make the amt dynamic, instead of being fixed to "I41"?
Thanks for the videos.. Have made a similar Invoice to the one in How to Create an Automated Invoice in Excel | Including Formulas and Customer Database. Then use the video (Create an Invoice Tracker in Excel) worked fine for the first now its adding to info correctly.
These tutorials are wonderful. You do a great job of explaining everything. Thank you so much for this content.
Hi Chester, awesome tutorial, thank you. In my record of invoices the date displays as 01/01/1900. Can't seem to change that. Any suggestions as the date on my invoice is a formula =Today()
Very useful, many thanks. I do have a question the set next rec =sheet3.range("A1048576).end(xlUp).(1,0) isn''t working. Please advise
Chester, can you reopen an invoice and edit/correct is after adding it to the record of invoices?
Great tutorial Chester! Very thorough and well explained. Thumbs up!!
Excellent and very useful. Only one thing I noticed is that there will be duplicate of records when I do any two of the actions (Add to record, save as .xlsx, save as pdf and email the pdf). It should add to the same record.. Thanks a lot
love this tutorial, can i ask if i want my Invoice number to have text at the start how would i code this?
Just use this and results successfully... thanks very much!
amazing content mate, one of the easiest to follow tutorials ive seen on youtube, keep up the amazing work
This was excellent. I successfully did it in Excel. However, I now need to do all of these in Google Sheet, but I am not sure how. Could you make a video performing these macros in Google Sheet? Thank you.
Another great tutorial. I have one problem with the conditional formating. As soon as there is a date in the Date Due column the colour disappears?? Any ideas. Thanks again for making the seemingly impossible look easy...
It's great how you explain! Thank you very much for sharing your knowledge!
My pleasure!
well done , so when i save data in other sheet so i want i update the price in same invoce number
What a great series of videos. I'm in the process of adapting my own needs to the template but I would have never gotten this far without your videos. One thing I cant figure out is I want the Record of Invoices to put the newest on top instead of the bottom. I've checked out your other videos on sorting but cant make it work. Is there a VBA code that will work without the data being in a table?
This tutorial was so well done!!!
Thanks for taking the time to give feedback.
How can i deal with partial paid invoice and If I want to adjust it accoedingly productwise? Need your help
Hi Chester, Do you have any suggestions on how to attach expense receipts to the Invoice? Then email them over with the invoice.
Hi Chester,
Great videos, I have been trying to get the next invoice to the next row of my tracker for weeks. Everything worked fine except my first row starts at 18, fixed that but the next invoice will override the Sam row. Any suggestions?
I have learned a lot.Thank You! How Would I create a Summary page that shows amounts paid for that month if Paid columns says 'yes'. Like reconciliation of what is already paid for the month.
Great tutorial, Chester. However, I have a problem. when declaring invno= Range - there's a problem as Range is declared 'an invalid property'. Can you help?
First of all, thanks alot for this great tutorials.
but i need to ask if i can save that record using macros in a seperate spreadsheet ?
don't even bother telling my how, just please let me know if it is possible.
thanks alot in advance
Hi! Thank you for the tutorial! I have a question. I have a situation where I have a lot of worksheets (one worksheet per invoice) and have one Record of Invoice sheet that displays certain information from each sheet, just like the video example. However, I want the Record of Invoice sheet to reflect any changes made to the each invoice, instead of creating a new line. What would you recommend in this case?
An awesome addition to this would be to set up a customer info sheet that auto populates the customer database, and generates a customer ID number. Why? For a nifty interface and some place to have additional fields like customer notes and scope of work a place to create a payment schedule and a contract for them to sign etc.
Brilliant series!
Very helpful tutorial! Only issue I'm having is when I run the macro, because I am using an alphanumeric invoice number with symbols (-), the value comes up as false on my record of invoice :( any idea's how to fix please!?
Weldone Job!
Sir I tried to modify this template and it was very easy. Now I want to add a Spell Number and Outstanding Payment brought forward.
I need help!
This is impressive Chester Tugwell. I have learnt a lot from this tutorial, thank you for very much. Is it possible to group the records of invoices according to Customer Name?
let me kow if you get a response :)
Followed the tutorial and adjusted as needed. Excellent work. I am very new at this and hit one snag. I am using an invoice with a drop-down list for driver names and I cannot figure out how to code it (driver name) to pull over into the invoice register. All other values come over just fine. Can anyone help?
Hi Chester really enjoying your tutalge. i opend Visual basic editoe and only a grey screen appeared. nothing was populated in. do you know what has gone wrong? thanks Chris
Very good tutorial. Thanks sri.
Hello. Great step by step. But I am stuck. Why doesn't the amount apply in column 3, sheet 3 ? It`s left as a blank. What do I have to do ?
This is another awesome video!!!! Helps a lot
Glad to hear it!
Great tutorial, thank you
Why is it when i prees record it says something having an error and it says to debug then it highlights the part with A1048576
This series is excellent, thank you for your effort!
For some reason in sheet 3 my Invoice no. field stays blank...what would I be doing wrong?
Great tutorials thanks, invoice created fine and now woring throgh this....I keep getting a Compile Error highlighting the Sub RecordofInvoice(), ther error states: Expected Sub, Function, or Property...I cant seem amyhting different between the video and what I have inputted but I cant seem to fix the error...complete excelnoob here just to be clear!
Hi Chester, could I ask how can I set up the tracker, when customer pay installment (like 50%, 40% and 10%), and invoice number start like ABC00001. Thanks
quick question: is the invoice number added automatically from the invoice record? You didn't show how that was done
Hi Chester, when I create the macro the invoice number does not update and I'm not sure as to why this is. I was hoping you could help please.
Excellent video. So far everything has worked very well and looks very professional. I keep getting an error connected to this line: "Set nextrec = Sheet2.Range("A1048576").End(x1Up).Offset(1, 0)" The error says: "Runtime Error '1004'. Application-defined or object-defined error." I don't know how to fix it and can't get it to play. Can you please advise? Thank you so much!
I see my mistake. I wrote "(x1Up)" instead of "(xlUp)". I have another question if you can please answer. For the formatting rule you made where the line is red until it turns clear by writing in YES, I am trying to make an additional rule where if there is a partial payment, I am writing PAR and the line should turn light blue. I copied the formula and replaced YES with PAR, changed the color to blue, but it will only do blue even when it says YES. Can you PLEASE advise? Thank you!!
@@ceagals.6344 Check to see that you applied the rule to your worksheet area.
Before you make the rule, select the area.
@@Avvura thank you. I just saw this now 🤦♀️. I will try it. Thank you again
this tutorial is very helpful, but I need some assistance here. When I tried this macro, I can't change my currency into Philippine peso and my date became the default time. How to change it?
Hello, after doing the VBA, my invno = 0, Custname = " ", amt = 0, both date = 12:00 am. What did I do wrong, please?
Sir, please write a VBA code if we need to record the description of multiple line items in an adjoining sheet.
My invoice number goes as “INV0001”
What will be macro formula for that ?
I mean will it be “long” or “string” ?
Can you help me wth the macros for creating a PDF, and mailing the PDF with a Mac?
hi iv downloaded your invoice ( thats Grate )when i send a email outlook dus not sent it can you help
perfect. the invoice No not move to record of Invoices
I'm trying to implement the formula to create the red background if the payment is overdue. Here is what I entered: =AND($A2**,$E2
how do you code/formulate a unique invoice # for example "5P063-01"?
i did everything just like you showed but when i enter employee it on records it on one line and nothing else it keeps changing only at that one Colum .please help
I followed you tutorial step by step but for macros for the invoice tracking is not working I typed in nextrec .offset(0, 3) = dt_issue and excel is giving me the time not the date. I keep going back and looking very carefully at your coding and it still not giving me the dates
Hi sir,
Your video is excellent. Pls explain how to create invoice for multiple copy pdf for 1.original for buyer 2. Duplicate for transporter 3. Triplicate for central exercise 4. Extra copy.
Hello, i had a problem, the invoice num shows me always 0 and the data it always gives me the same data and it's not even the date that i have ???
Please help
If I would like to record all the description to the records of invoice what wil be the vba
my via function of Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1,0) is not working I have put it exactly as it was written in the video and yet I cant find to get it to work
hello
Can you help me I have some issue with Conditionally Format
Thank you, these videos are very helpful. I do have one problem though. When I run the macro I keep getting a compile error, saying the sub or function is not defined. It refers back to the Sub RecordOfInvoice(), Am I doing something wrong?
Is there a way just to download this?
hello, is it possible to provide invoice number with year and month? For example "2022.06-0001". I very kindly request you, if you wish, to clarify that. My thanks are enormous.
where would one find how to add additional dims? like I want to add phone number etc and which formula to apply for it
What if i want to save the description in the invoice as well
thank u very much god bless u
Dates are showing 0/01/1900 in date issued column, term shows todays date and the ammount is $44,794 when it shows $176.00 on my invoice??????? PLEASE HELP!!!
I am running excel on Mac and I don't know how to get "Format values where this formula is true:"
How about if i want to record with Full Details. Not only the Total.
Run macro error coming error is run time error 424 object required please tell us solution
Nice, thank you!!
Hello, little question. So I accidentally removed 1 line in the 3th sheet with customer data. Now every once i press the play button of the macro, it's all 0's and it says 12:00:00AM where the due date is... Help?
Hello, just saw you comment. I am on Mac and I was having the same issues. I was getting 0's and the 12:00:00AM, my problem was when I was playing the Macro I was in sheet3 looking at my Record Of Invoices. I had to be on the actual invoice and then played the Macro so it could move everything properly. Make sure you are in Sheet1 or the invoice when playing the Macro. That at least was my fix for this on Mac. Hope it helps!
@@norcal237 Thanks alot. I was abit worried
My formula is not working. What am I doing wrong. Please help=AND($A2**,$E2
I got it sorted 😆
How Can I do that if I already have a table with a name?
Invoice# 24-500 gives me error 13 type mismatch. any solution. the - is the problem.
Try - Dim invno As String (instead of Long)
set nextrec = sheet3.range("A1048576").end(xlUp.offset(1, 0) error
Perfect
When I hit play it shows the date as random numbers but when I click on the cell it shows the date why is this?
you have to change the cell format, where ever the date column is select the whole coloum(deselect heading)
rightclick, click on format cells, select date and its type suitable to you
thanks boss
Y'all!!!!!! I just figured out my problem! My dumbass kept running the module on the record page (when it should have been on the template page)....hence the button...I just wasn't ready to commit to a button until I figured it out lmao
I have meticulously followed every part of this and can not get it to work. Far to complicated for anyone with limited excel experience
I wanna remove the usd symbol to none
Select whole coloum/row, right click the mouse, select format cells, select currency and the none