Subscribe if you want to get these videos make sure you click the subscribe down below and don't forget to click the notifications icon I do create these free and I'm so happy to do so if you would like I do have 100 of these applications for you available in a zip files along with the library so if you want to pick that up right now it's just $37 so be sure I'll include the links down below if you like these workbooks you want to get a hundred of them I've got 37 dollars special going.
On right now also if you do like creating invoices I've got a brand new mentorship program where we're gonna creating an entire accounting application one single component of that accounting application is an invoice that looks just like this this is a much more complex invoice than we're going to be creating today but I want to show you the potential and power of Excel and as I take you through I'm going to show you every part of an invoice like this along with a complete accounting and that means purchase orders inventory items we're gonna cover dashboards sherry and.
Sink and a of other features that's going on in the mentorship where I take you week by week and we create an amazing application and I want to teach you how to define design develop and deploy your very own applications for passive and reoccurring income so if the mentorship sound like something for you I'll include the links down below and I would love to have you alright let's close this out and get started we've got a blank sheet sheet one that is what I want our invoice so we're gonna name that sheet we're gonna call that invoice sheet I want that to.
Be our main invoice sheet on sheet 1 sheet 2 what I'd like to have that I want to have that our invoice list that's going to be a list of invoice so we'll call that invoice list sheet 3 what I want to do is I want to track the individual items of each invoice we need to track those in a separate table and database so we're gonna call that invoice item list or invoice items should be fine for now invoice items keep it small only thing we have is customer lists and items that's all we have so we've got three sheets and.
Invoices basically what I'd like to do is design this in front of you now how do we go about designing this what always as you know if you're familiar with my videos I'm gonna keep the first two columns available for our code so we can hide those so let's drop this down we're gonna be using a lot of formatting on this and we'll just call it that gray that's gonna signify that we want to use those and save them for code so I think that's quite important and next up column C I want to give this just a blue color I want to make sure we're gonna need that column but I want it visible.
And blue up here I want to create the name give it a name and we'll call this invoice that's gonna be larger field perhaps we're going to go to about 30 and I'm going to create a larger because I want something nice and large so we'll up that to about let's say 22 and then we're going to make it italicized and I also want to merge that maybe up to F so we'll merge the center this is gonna grow here I'd like to put the invoice number invoice number at least a name.
And then the label for the invoice and I want the invoice number to go right here perhaps in h1 these are going to be blue let's keep put those in blue for now and I'm gonna go all the way down give that just the light blue so we can start to form out our invoice so basically what I would like to have is our customer I want our customer information probably in e let's say we'll put the e to customer right about here I want to put the customer customer and then what we'll do is we'll have a drop-down list of customer in III.
Something like that then maybe in row 7 what I'd like to do is I want to put the items in here so we're gonna have a drop-down list of items so let's put the items description of the invoice so each item is gonna have its own description I want to put the quantity how many the cost what does the cost we'll put that actually the price or cost either way and then the total is we're gonna total it that should be sufficient and I don't want the database to row this is important and I'll explain that a little bit later on and this is gonna be the.
Item rule and I'll explain that later on - I want to keep track of what row and we select the item I need to know what row it's on so let's give those a board just give it a basic I'm not gonna create an amazing beautiful thing because what we're gonna be focused a little bit more on functionality than we are beauty descriptions gonna need to be a longer field the quantity is gonna be a smaller field and this will be fine this is fine okay and let's see what I want to do is I want to have a drop-down list of customers and then I want to put the address line 1 and address line to.
The customers so when we select a customer and then want to have a drop-down list and then down here I want to have the totals so let's say in row 28 let's put in a subtotal here subtotal here and then we'll put the subtotal and then in case there's tax let's do tax and we'll just set 10% or something like that and then of course you can adjust it and then I want to have the total here the actual total here so we can drop all this down a little bit and the.
Same thing was here and then what I want to do is I'll have a merged field so then here I'll do a message so let's
Merge the center that left and top and then make this will say thank you for you can do custom messages down here thank you for your business and we want to make that a little bit larger so we can go up and then italicize something like that all right good so now we're getting to form our invoice I'm gonna put an outline on our form on our invoice so we're gonna do format the cells and put a border around that I'll choose that we're using a blue.Theme so I'll choose like a blue like this just an outline like that that should be sufficient for now and I want to get rid of these gridlines because I really don't like the way they look so we can get rid of those so we have a much cleaner looking invoice but I like to put some dotted lines here so I'll go down here to about right here then I'm gonna format those cells sorry it's off the screen there but right-click format the cells I go with a lighter blue something not too and then I'm gonna use a dotted line vertical dotted line that's gonna give us a nice.
Look there so now we just have to add the borders here so I'm sorry I right-click I'm off the screen right click and format the cells that's all I did but it's off the screen a little bit so you know what I did and so let's give it a border here so I'm giving it a top order and I also want borders here and here format the cells again sorry right-click right-click format the cells also the border right click format cells and then.
I'm gonna bring it up here so I'm gonna do also a left border and then a solid vertical border and then maybe a dotted let's do a dotted centered border there okay so you get to see now we can right justify that so you can see the invoice kind of come to life here get an idea of what's gonna go customer and I also want an invoice date let's put that here invoice okay that's gonna go in here and I'm gonna write justify these two so that they're on the right and I'm gonna left justify these two that way the invoice date so put an invoice date on.
One and then I'll format the way I want with a short date all right so it's starting to come together and we'll put an invoice now but I as you know or as you will know I need some more information we need hidden informations here that that's going to help us create this invoice so the first thing I want to do is I want to know a customer Roah custom let's just call it customer row so when we select a customer I need to know what row they are in this database here so that's going to be helpful I also need to know the invoice.
Row invoice row when we selected the invoice I need to know the original row of the database coming from here it's going to come from here so that we need to know we also need to know if it's a invoice a new invoice or not so click new invoice and that's going to be true or false we also want to know if it's loading invoice load true or false also on that we can put false that's going to help us I'll explain that later and then all the next invoice number I want the next available invoice number we're gonna color all of these just give it a nice color so we can know we understand.
That those are important for us and those are for the developers you will hide these columns and nobody will see them so that's okay but we can keep them all right so now let's focus on our other sheets I need an invoice list a list of all the invoice this is a very very I'm gonna keep this very very basic for our purposes here so let's call this invoice list and then invoice numbers gonna be our first call date you know we have a date for that and customer I want put customer and then total this is maybe the total may not be so important but it may be for you when you do accounting purposes but for our all.
We're gonna do is just save it not too important so let's just increase this give it a little bit of formatting nothing too formal nothing too fancy for our purposes today just trying to keep it simple and I want to do also I want to keep track of invoice items so that's very important I need to keep track of the individual item so let's type in invoice items we need that and that's gonna be a little bit bigger the table invoice number we need to know what invoice number each one is I need to know the item name and the description now the description may be different.
Than our save description meaning we need to save the invoice description so that's very important also that the quantity and the cost or the price we can do price and then what we want is invoice bro I need to know the row of the invoice that this was placed on and I'll explain that a little bit later and then the what I mean is I mean when we save an invoice item here I need to know is it wrote 11 10 9 8 so I need to know the role it is so I can place it back when I load the invoice I can put it back in the right place so that's important all.
Right so we've got everything on that let's merge the center it and then grow that a little bit bold it and make it
Look a little bit better and then also Center these add some borders around it and then we'll just give it a color any color will be fine all right let's alright so now we have invoice items and we have everything that we need on the center that and let's just increase the columns but also on invoice on this particular one what I'm gonna be doing I'm going to need to be running an advanced filter and what that does is so let's say there's items from tons and.Tons of invoices I want to get a result of only one specific invoice I want all the items for one specific invoice so how do I do that well I use an advanced filter based on an invoice number so let me copy this I'm gonna run an advanced filter I'll put it right here and I'll call this criteria and so what I want is I want a criteria based on the invoice number whatever invoice numbers here so how do we do that I'm just going to create a formula it's gonna say equals what is it going to equal whatever the invoice number is right here so when we do place an invoice here it's.
Automatically set as the criteria here okay so we can Center those color those just so we can let's give it a different color so now we see the criteria and also so I want to run advanced filters so I'm gonna copy this and I'm gonna paste it here and then I'm gonna call this filter results so these are gonna be results of our filter so only one specific invoice out of all the invoices let's say two let's say we have tons of invoice - I'll need the items with invoice - are gonna display here so I'm.
Gonna merge the center that and then make it a little larger so you can see it and then give it it just a color let's give it this color so we can differentiate between the results and every other car all right so we have our basics now down so that's important and let's say our invoice list let's say we have one two three our invoice numbers are going to go down like here so we need to know those invoice numbers that's very important now we need to do some named ranges so let's create some named ranges I need.
Primarily three named ranges I need an item list name I need a customer list name and then I need an invoice number named range so let's create those three we're gonna create dynamic named ranges using offset so all we need to do is just highlight the selection I'm gonna include the header row because I don't want there an air if there's no date in here so into the formulas name manager and there's no name managers I'm gonna create a new one and I'm gonna give it the name of invoice item name which is fine and so what I'm gonna do is I'm gonna create offset which is gonna be dynamic offset.
And we're gonna start out at a two which is what I want but of course a two is the header so I wouldn't want to include that so we're gonna move it one column down then comma comma count a and what are we going to count we're gonna count basically everything from here let's say two nine nine nine nine all right so just a large number of rows then but of course I don't want include a header so have to do minus one then comma one for a single column tab it over double check and we're tapping it over look double check the dancing outs are correct.
Good so we're good on the item name we've created and I had a name tab it over once again it looks proper let's create a brand new one I want four customers I want to do the same exact thing so name manager new let's do customer name tap it over do the same thing offset formula offset starting out at 8 2 comma 1 comma call account a we're counting all the ones where we count any one account all the ones and then again we're gonna do nine nine nine nine and parentheses and then minus one.
Because I want to subtract one because we're including the header and I don't want to include the header comma one and then tab out tab back into it make sure that we've selected all the ones does that look right make sure it goes to the end yep that is correct okay so we have customer name and we have now item name and so let's continue on one more to create goats go into the invoice list and I want to create invoice numbers so name you're again new you get the idea here.
Invoice number just create that and again same thing offset so we'll start out with offset and what are we gonna offset a two again so we're gonna do again comma 1 comma comma count a same thing pretty much also gonna highlight these go to say nine nine nine one more nine and then minus one comma one okay so tab over it and make sure it.
Includes all the data that we want good okay great so now we've got our three named ranges so now we're ready to create some a few formulas that are gonna help us so we can close that out let's say this always save our work so we created our work okay back into the invoice so first thing I want the next invoice number we noticed we have three invoices I'm going to erase those in a moment but I've got three invoices so the next available would be four so let's create a formula for that equals if air while if air because if there's.
No invoices we need to make sure max what is the max the max of the invoice numbers which is the name brain injury's just created plus one right our maximum is three our next one would be four plus one comma what if there's no invoices if there's none I want to start it off in one if there's an error right there because there's no data I want to start it off on one right now it's four right because our next one's for we've did the max plus one but what if there's none if there's none we want it to revert to one why would it.
Be none why would there be in air because as soon as I delete that data it's gonna revert back to one that's exactly what I want what about the customer rub well I need to create a role for that and I want to create a drop-down list if the customers here so we can do that with this just data validation pretty simply here so into the data data validation list equals customer name that's the name drink view just created click OK and make sure that.
Includes all of our customers great so basically what I want to do is when I select a customer I want to put the address 1 and the address 2 lines down here but I need to know the customer row customers keep in mind they start off in Row three so keep that in mind so let's get that customer roof through a formula and we're gonna place that formula right in b2 equals what is it you go if air in case there's an air match we're gonna use them match what are we matching we're looking up what are we gonna look up I'm looking up III I can't click it III and then comma customer name that's.
The range I'm looking it up zero on an exact match then I want to add two why are we adding two Manning to because our customers start off in Row 3 so the if we get a return of one that's gonna be the first customer I want it to actually return 3 so comma while I come if there's an error just click empty alright great so let's double check that our our first one should be Row 3 which is exactly right that's exactly the way I want it first one is in Row 3 our first customer.
Here's in Row 3 perfect ok great and now we need to invoice row I want to do that pretty much the same thing but the invoice row if we look at let's put some invoice numbers back in here 1 2 3 so invoice number 1 is going to be Row 3 let's make sure we create a formula for that so refer to this can be based on h1 equals if air what is the what would the value we need to match again mask what are we matching this time we're gonna look up the invoice number and we're.
Going to match it with the invoice number and then we want an exact match so it's going to be 0 again we want to add 2 because our first one starts in Row 3 plus 2 comma double quotes for if there's an air and perfect now invoice one is wrote 1 we change this to 2 it changed it to 4 perfect that's exactly the way I want it now we've got all of this setup what about our item names I want a drop-down list of item names here so I'm gonna highlight this and I'm gonna go into data validation data validation list and I'm going to create.
Item equals item underscore name that is the name that we gave the item name so good now we have a list of item rates great but what I want to do is want to know what row this is on we can increase this a little bit sure what I want to know is what row is this again we can do that with a formula so let's do that equals again if there as always in case there's an error I want to match use a match and what does this match based on is based on d8 so d8 and what's.
The lookup array it's going to be item name exact match again also adding to 0 actually adding to again cuz I need to know the row comma and then in case there's an error so great the first one is on Row 3 and Row 4 perfect now I'm going to copy that formula down copy and I'm gonna copy it all day this can be hidden if you like paste formulas okay so now we have it if there's now as soon as we select and any row in item we have the road we're gonna need that because when I want to load the description the.
Quantity and the price in a macro alright great so our invoice is starting to form let's create some buttons we want some buttons here and that's gonna help us and we can save the invoice so let's do that right now let's save our work and insert I'm gonna insert a shape here let's just write here and let's format I wanted let's say point two seven and one point about one point one point two five okay so that's about the right size here and then I'm gonna give it this color maybe this color now I got.
Some icon save and I want to create basically four buttons save the in saver update the invoice I want to cancel new I want to add new and I want to print so I like it before different buttons and I want to add some high cons so let's insert some icons I had something saved so I can just copy these into them and I want to size them all the same so I'm gonna point to that's the size I want to give a point to I'm gonna give them all squares and this one actually this one I want a little bit bigger this is our invoice background actually that's fine I'm gonna resize the others a little bit.
Smaller someone hold down the ctrl click on all three of these those are gonna help us and let's go to 0.18 point one six for those cuz I want them smaller okay so our first one is going to be save update save save our exes update and then now I'm gonna write justify that okay and then I'm gonna put this icon right up here and then I'm gonna bring that I come to the forward I won't actually I'm gonna bring them all so click on all three of.
Them and then bring them to the front so we're going to bring them to the front now they're all in the front alright that looks good I'm gonna group it but let's duplicate this one one two holding down the ctrl D 3 ok so that way we have three buttons three more buttons four in total and I'm going to put this one I'm going to are going to overlap so I'm going to size these and gonna line those to the left and then I'm going to.
Distribute them vertically here so that they're all distributed and this one is going to be add new add new and this one's going to be cancelled new so add new and cancel you're not going to show up at the same time cancel new so those two are gonna all should be same so let me copy this I want this icon for each of them add new we can use that plus for this and what dot printer icon go here that one needs to be brought to the front format bring to the front okay and so this one I'm going to duplicate.
Again and I'm gonna bring it here and then the cancel new I want to bring here because that's gonna be I'm going to bring that to the front too so I want to cancel to be able to use this to be able to cancel to get out of that new and so we can do that let's zoom in kind of a that'll help us work with them and we'll create them these buttons right here add new and weird that plus sign go there just bring that to the front format align bring bring forward okay so we'll have to realign those again now we.
Have everything let's just bring it up and we'll start to group our buttons and that way we have everything set so we can start assigning creating macros and assigning them to that let's just align these once again on now that I moved him aligned to the left and and distribute vertically alright great so we have add new and they're a little bit big aren't they let's move them let's shrink them a little bit okay so let's shrink them a little bit hold down the control and then shrink them all a little bit okay that should.
Be good all right so we have add new invoice on the green duty invoice add new that's fine add new save update and this should be print print invoice and we can also duplicate that and put that and bring the printer up in front all the way to the front bring it to the front okay so we're getting our butts now we're going to name our buttons accordingly and so we have specific different buttons for that I don't know.
If we need that okay that's good that's okay I think we've got everything just right okay so I'm gonna hold down the control select all the icons within this button and then I'm gonna group them and I'm gonna give it a specific name save the invoice button it's really save update but that's fine save this one I'm going to give another also holding down the control I can also grip gonna group them and then we're gonna call this add new button and then cancel new button.
Holding down the control grouping them cancel new button and then the last one print invoice button holding down the control on all of these and then grouping them print invoice button okay so we have them all named and basically add new and cancel aren't going to show up at the same time so I want to make these pretty much overlap because they're not gonna show up at the same time all right good now we've got our buttons and we've got them all named that's gonna help us zoom in too far okay so.
We've got everything it looks really good now we have so we're really ready to create some macros now why don't we create why don't we get the address here and here on change of III when we make a change we're ready to have that address automatically and if we look at our customers we see that address is located and B and C so let's start out and let's get into our code developers if you don't have the developers available you can find that into the options just go into the customize ribbon make sure you select developer here alt F eleven is a shortcut that'll give you the individual.