Create an Invoice Tracker in Excel | Conditionally Format Overdue Invoices | VBA-Find Next Blank Row
Вставка
- Опубліковано 10 лип 2024
- Download the featured invoice template here
www.bluepecantraining.com/exc...
Full playlist for this video series: • Create an Automated In...
Check out Simple Sheets Invoice Template (all purchases help support this channel)
www.simplesheets.co/a/2147525...
This tutorial is part of a series of tutorials that demonstrate how to create an automated invoice system in Microsoft Excel. This video looks at how to create the invoice tracker using conditional formatting, drop-downs and a VBA macro to copy over current invoice details.
Other videos in this series show you how to write VBA macros that
1) Create the invoice itself
2) Automatically generate the next invoice number
3) Save the invoice as an Excel workbook or as a PDF
4) Automatically email a PDF version of the invoice to the customer
Table of Contents:
00:00 - Introduction
01:30 - Overview of the record of invoices (or invoice tracker)
02:49 - Start creating the record of invoices
03:13 - Apply conditional formatting to overdue invoices
04:32 - AND formula used for the conditional formatting of overdue invoices
06:01 - Create drop-down list indicated paid invoice
06:34 - Create the VBA macro to add the invoice details to the invoice tracker
08:15 - Declaring the macro's variables
10:33 - VBA to find the next available (blank) row in the invoice tracker
13:13 - VBA code to populate the record of invoice
15:17 - Create a button to run the macro
------------------------ - Навчання та стиль
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
Great tutorial Chester! Very thorough and well explained. Thumbs up!!
These tutorials are wonderful. You do a great job of explaining everything. Thank you so much for this content.
amazing content mate, one of the easiest to follow tutorials ive seen on youtube, keep up the amazing work
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
Just use this and results successfully... thanks very much!
It's great how you explain! Thank you very much for sharing your knowledge!
My pleasure!
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.
Great tutorial, thank you
Nice, thank you!!
This tutorial was so well done!!!
Thanks for taking the time to give feedback.
Very good tutorial. Thanks sri.
This is another awesome video!!!! Helps a lot
Glad to hear it!
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?
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 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?
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?
thank u very much god bless u
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.
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"?
Chester, can you reopen an invoice and edit/correct is after adding it to the record of invoices?
love this tutorial, can i ask if i want my Invoice number to have text at the start how would i code this?
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
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...
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
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?
How can i deal with partial paid invoice and If I want to adjust it accoedingly productwise? Need your help
Perfect
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.
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?
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 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
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 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!?
thanks boss
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 ?
Please help
If I would like to record all the description to the records of invoice what wil be the vba
Hi Chester, Do you have any suggestions on how to attach expense receipts to the Invoice? Then email them over with the invoice.
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
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!
well done , so when i save data in other sheet so i want i update the price in same invoce number
What if i want to save the description in the invoice as well
Can you help me wth the macros for creating a PDF, and mailing the PDF with a Mac?
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
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?
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?
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?
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
perfect. the invoice No not move to record of Invoices
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!
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
How about if i want to record with Full Details. Not only the Total.
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 :)
hi iv downloaded your invoice ( thats Grate )when i send a email outlook dus not sent it can you help
hello
Can you help me I have some issue with Conditionally Format
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 ???
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
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 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.
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.
quick question: is the invoice number added automatically from the invoice record? You didn't show how that was done
My invoice number goes as “INV0001”
What will be macro formula for that ?
I mean will it be “long” or “string” ?
Hello, after doing the VBA, my invno = 0, Custname = " ", amt = 0, both date = 12:00 am. What did I do wrong, please?
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
Sir, please write a VBA code if we need to record the description of multiple line items in an adjoining sheet.
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
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.
Is there a way just to download this?
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
How Can I do that if I already have a table with a name?
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!!!
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?
I am running excel on Mac and I don't know how to get "Format values where this formula is true:"
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!
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
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
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
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
My formula is not working. What am I doing wrong. Please help=AND($A2**,$E2
I got it sorted 😆
I wanna remove the usd symbol to none
Select whole coloum/row, right click the mouse, select format cells, select currency and the none
I have meticulously followed every part of this and can not get it to work. Far to complicated for anyone with limited excel experience
Run macro error coming error is run time error 424 object required please tell us solution