Download Our Free Excel Invoice Template with Automatic Invoice Numbering for Facilities

Excel invoice template with automatic invoice numbering free download for Facilities

hello Excel champions its William aka dr. Excel today I'm going to show you how to add an automatic invoice number to to to an invoice so here here's an invoice template that we've been building or ready for for a few videos and we're going to add an invoice number which is going to be automatic which means that each time you make a new invoice it's gonna the number is going to adapt so you can have invoice number three four five each time it's going to increase this way it not even have to worry about the invoice number so as usual I will give you the final file with all the formulas everything set up you can download the template right below the video there's a link for you so you can access it and see how I built it so so what we are going to do we are going to add the invoice number and to be able to add this invoice number we must make an archive we must actually archive the whole list of invoices so we must have the whole list so what are we going to archive we are going to put the invoice number customer and total amount so I'm going to make it simple so it's easier to show you basically we could we could archive everything so what we need we need to put some dummy data first and so now how do I know my invoice number basically I'm gonna do a vlookup and what am I going to do to look up I'm going to look up an infinite number so I'm going to put a huge number to add a ball into my I kept my archive array actually I can look it up just in here I want the first value and yeah I want an approximate match so it's going to return the the last value and I'm going to add one so that's a bit of a tricky use of vlookup is that we look for for an infinite value so basically it's never been a match were never gonna find it so if we make an approximate match Excel is going to return the last value in the table so here I just want to test it invoice number if I had one now if I archive in verse number one it's going to give me in verse number two so that's good for the invoice number and now I want something that's going to archive automatically all my on my info so what I'm going to do I'm going to make a macro button here so I'm going to put developer insert form I'm gonna put a button which is going to be okay okay in verse I'm going to make a new and how should I write this macro I should write [Music] line backup so that's going to be the line where the row where we should back up the file and it should be how do i computed is sheets sheets okay I take the Ranger a1 I go to the bottom and exhale down so this means that I go into a1 and it's exactly the same in VBA as if I did ctrl + down arrow like this which sends me to the last last value in the table so and I'm going to take the row of that so in that case it will be 4 and I'm going to take plus 1 so I've choose to use double quotes here so that's the line where I should back up and what I'm going to do I'm going to do that sheets in a and then back up I'm gonna put sheets in voice so I need the inverse number which is going to be in f8 f 8 you first thing and have three things to back up then in mark you have on the customer and the total amount so the customer is going to be f5 and then H 20 this one's gonna be f5 what did I say or do you forgot f5 h 20 h 20 be B and C okay so let's write this micro right now to see if it works so if I do this and I have the micro what did it do it it added the micro here and if I do it again it you see it increases the the invoice number each time because it backs up all the data here so of course you could make a full archive of everything I just did I just did three information three pieces in verse number customer and total amount of course you can add all the info and you see that if I change the if we if I had some more some more information it archives the correct information in my database so I'm just going to to edit the text for that and when you put okay and voice like this and and that's it we have our move this somewhere move here we have our automatic invoice number each time that you archive the invoice it's going to make a new one with an automatic it's going to improve to to update the invoice number you just have to delete it to put a new a new invoice so as usual you can download the file below there's a link so you can access the template and reuse it and I will see you tomorrow for a new excel tutorial don't forget to subscribe this way you'll be notified as soon as they publish see you tomorrow guys ciao