Streamline Your Billing with the Excel Invoice Template with Database for Hospitality

Excel invoice template with database for Hospitality

hello this is Randy with Excel for Freelancers and welcome to the free Lancers invoice in this week's training I'm going to show you how to create this incredible Freelancers invoice designed completely from scratch from a blank worksheet it's an incredible invoice for any type of freelancer to automatically create and time their billing entries and invoice them I cannot wait so let's get started thank you alrighty thanks so much for joining me this week I've got a really fantastic training the Freelancers invoices one was highly recommended so thank you Hank for your suggestions basically what we're going to be able to do is track time and billions and easily add that to an invoice and then it's something that uh Freelancers often do as we do so we can select the customer here a project here that's native to that customer any particular service that you have a description add the number of hours then all we need to do is just click save then entry and that's going to be available for the invoice when we have an invoice for that customer all we need to do just go ahead and select and add it to that invoice it has already been added to that invoice so very very easily we can also total and like service items meaning that when we add them they're going to get automatically totaled up instead of adding new lines so there's a lot of features we'll be able to save navigate these features and I'm going to design it all from scratch for you this week it's going to be a great training I want you stick with us for the entire training because there's so much to learn not only will you be able to use this template if you download it but you're going to be able to customize it for your own requirements when you learn the skills and I'm going to go step by step before you are going to be starting with a blank worksheet on this invoice so what do you need to do just a few things just sit with me hang tight grab your beverage of choice don't forget to subscribe and don't forget also to click that notification icon Bell that'll ensure that you get notified when these trainings go live each and every Tuesday I bring these to you absolutely free this template is also free using the links down below I'll make sure you get that with your name and email don't forget to click like comment below I'll make sure to respond to each and every one all right we're gonna get started right here so this is exactly what we're going to be creating we'll be able to navigate to previously new save update print and delete so we've got a lot of features on this and we're going to get started right now and what I have here is just another workbook with an absolutely blank so I'm going to move this over to side on another screen and keep an eye on it from there and this is it so what else do we have we're going to design this brand new invoice screen from this sheet so we're going to get started right now on that so what I want to do is I want to take columns A and B and I'm going to reserve them for an admin so we're going to give them a gray color they're going to be specifically reserved for that admin this is where I want our title to go into so we're going to call this again just like you saw Freelancers invoice we'll give that a very specific font and a very specific color something a little bit large we'll go to let's say 30 or 236 or something like that how about 28 that's not too big and we'll just go with a different little bit of a font let's go change that all right so now we're going to set a font on that that one looks good nice and big and we'll give it a color there of dark blue we'll use a theme color on this one so I'm going to stick with a specific theme this blue color will be our theme of choice for today and it is that thing that we're going to use so I'm going to go all the way over to column s here I'm going to format that Top Line we're going to give it some fill we're going to give it a fill fade effect with two colors so we're going to go into the fill and then fill effects we're going to use that single column theme that we've been talking about here so actually we're going to go with this one I like this one a little bit better it stands out a little bit better here and then so we're going to give it that faded and on the next line below what we're going to be doing is also giving it that but a little lighter on that so again we're going to format those cells we're going to give it a fill and then we're giving that fill effect so this is going to provide that little effect so we're going to give it this medium and this color so this blue stands out a little bit more then all we're going to be doing is the entire background here we're going to give it a solid color and then we'll color those fields that we're going to be filling in ingly we'll give those the white Fields the one number so we're going to use that theme so we've decided on this so that's it so then all we're going to do is just merge and send this across we'll go to something like and that'll be suffice so that's the top of it then what I want to do is I want to set our invoice so here as you saw in the sample but if you didn't that's okay I'm going to put the billing information here I want to skip a call we will also want to use a always want to have a column as a let's say a spacer so you can adjust that spacer however you'd like so this is what we kind of call our spacer column on the left so that allows us to adjust it so after that what I want to do is I want to put some billing information in here we'll start out with D4 D4 skip one that way we have some 2 and 3 we're going to save for the buttons so we're going to put the customer in there I want that customer name in there okay and I'll go over some more details with you we'll get to the design and then below that we're going to put in the project below that I want the service what is the service that we're going to be using along with the description the description of the service when we create these billing items we need to select a service and a customer and a description I'm going to use two columns for that so we're going to color all of these giving those white so we're going to give it those white color and uh we'll just go with this white here and I want to give them a very distinct border okay so after that I also want to have the service days we're going to put in the service date here I also want to know the number of hours along with the billing rate billing rate what is the rate that we're going to charge and has it been billed yet or not right I want to know it's been built if it's been built it's going to be a yes or no so again we're going to color these giving it that white here make sure we're on white that's white this should be white also not that gray color okay great so we have the outline these are the basic forms of our I'm going to merge and center these because I want these fields a little bit larger and then what we're going to do is we're going to justify them so we're going to merge the center we're going to left justify them now we're ready for our borders so again I'm going to hold down the control key and I'm going to select all these fields just as we are here all eight of these fields and then what I'm going to do is I'm going to format those and then click the format cells we're going to go into the border and we're going to use that theme that we've been using we're going to use we're using this theme here and I'm going to select on the darkest color I'm going to solid outside border and then for the vertical line I'm going to use the dotted line here okay then what I'm going to do is I'm going to we've already left Justified those I'm going to left justify these and the labels we're going to write justify so I'm going to hold down that control and right justify these I'm going to double click on this it's going to space it ingly that service day we need to format that as a short date we want to know the billing rate that's going to be a currency or accounting that'll be fine customer project project service and description we're going to be putting in there now I want to add some information on here and I want to just kind of give it a group so what I want to do to group bit so I'm going to use a line for that a specific shape but just basically it's going to be an outline so we're going to use this shape this rounded rectangle here but we're not going to put any fill in it so what I'm going to do is I'm just going to reduce the corner radius here and then the shape fill is simply going to be no fill okay so that gives it a nice group and I'm going to use the color that we're using the shape outline is going to be the color based on our theme which is going to be used this one so that we're consistent both with the borders and them okay but now I'd like to give it a label and I'm going to call it time billing entries time billing entries so how do we do that well the best way to do that is going to enter another shape and I'm going to enter a text box and I'm into that text box right over here we're going to call that again time billing entries okay so I want to make sure it's centered and I also want to remove any of the spacing around that and I'll double check the spelling on that so again we're going to go into the format the shape this time it's going to launch this I'm going to go into the text options here in the text box and I'm going to remove all the margins on the space I'm going to all three of those top left right I'm going to put to zero okay then what I want to do is I'm going to expand this a little bit and then I'll fix the spelling here so we have that right entries okay that looks good time billing entries but what I would like is no border and the fill so the shape format I want the same fill as the background which is this color right here no outline or no border so we're gonna do it okay so that's nice that's exactly what I want so it gives it that nice group look and of course we can bolt it if we want to do it so let's maximize this to 100 we can even zoom in a little bit more so you can see what we're working on here it's a little bit small before okay great so we're going to save that now below that I want to put some information here on for the title basically this is going to be our time and building entries this is going to be on the top of our little table on this table I want to show all of the billing entries so the first row 10 is going to be our header row so I'm going to merge and center that and we're going to give this call going to call it billing entries let's do billing entries we can capitalize that okay so this is where our billing entries are going to go to now I want to give it again I'm going to do The Bold and I want to give it a theme that's consistent consistent with the top sorry I'm going to format those cell sorry it's off the screen fill again filling effects we're going to use the same theme that we wanted we want to be consistent with our theme we're going to go with this darker blue here and then to medium clicking OK we have a sub header on that one so inside the sub header I want to put the service date what is the service date the date that we've set if we track time we want to know what date that time has been tracked I want to know what project has been on project and I also want to know what is the service that was done the service along with the hours and then has it been billed or not okay very good so that's our subheading and I'm going to Center that bold it we're going to put on around both of the top and the header we're going to use up we're going to format that and we're going to go to the border on that we're going to use our same consistent border color so the border that we're consistent with is this dark one right here we're going to give it that solid line all the way inside and outside okay go ahead and format that inside that we also want to format The Fill of that the fill again it's going to use the same theme we're going to use fill effects just as we did before but a little bit lighter on that so that it's always consistent from dark to light clicking okay all right I've got that that looks really good now what I'd like to do is I want to put some information so the service day is going to go here so I'm going to format that I'm just going to put a short date on that and I also want to hold the control just to make sure that both of these are formatted ingly as short dates okay very good so now what we've got I want to put some buttons up here at the top of here but I want to also design the invoice so where's the invoice I'm going to use the spacer call I is going to be our spatial column between the invoice and that I want to put my invoice right about up here start that invoice write about J2 so I'm going to start it up here and it's going to go all the way over what do I need I need a column for service date I need a call for service item I need a description hours the rate and the total so that's going to cover and we're going to go all the way down to let's say row by 38 should be sufficient okay so now that we have all of that what I'm just going to give that white background color so we're going to white background color so our title is going to be here we're going to call this invoice and then I'm going to merge and center that across the top of the invoice Center that here and put it in the middle can you give it a larger font here and we want to italicize it we'll give it a theme color as we do so our theme color will go right about here that theme color and then I want to italicize it as I mentioned and that should be pretty good if we want to add a little bit we can okay we'll make it a little bit bigger and that's good I like that there what do we want here so inside I'm going to skip the row have a row blank and then what I want to do is I want to add the customer in here so in row four we're going to go ahead and put that customer in so we want to select from the drop down list so customer will go in here customer okay the drop down wants to be on the right I'm going to right justify that and we're going to put a customer drop down list so I want the customer name here the address one and the address to here moving over here we also want to put the invoice number now I'm going to have that invoice in column M at 3 and N3 is where our invoice is going to go so we're going to go up to invoice number and I'll put a colon there and then that invoice number is going to go right here we'll just put temporarily invoice number one just so we can see the data I want to put the invoice date next and I also want to put the due date and then I'll put a colon and the project I'm putting a colon because I'm not going to have uh any particular orders around this so that's why the colon is going to help me separate the label from the data okay so I've right Justified that I'm going to left justify these here so the invoice date again we want that formatted as a short date here so if I put in a day we want to make sure it is formatted good the due date as well so also a short day I should have done both at the same time and the due date 131 okay so then we could have a project drop down list our project is going to be a little bit longer name so I'm going to merge instead of that and then let's justify that that's going to be a drop down list based on our project okay I'm going to skip a rows of spacer and then I'm going to put those header rows right in here and give it the color of our theme which is going to be the light color I'm gonna use the Border color if I want to use the line color here and use a common line setting the line color here and then we can just use all the borders here if we want to allow the borders Okay so we've got our line so what do I want in this I'm going to put the service date and then I want to put the service item then I want to put the description of the work that was performed next up I want to put the hours how many hours were involved in that along with the rate and then the total simply going to be a formula and we're going to basically highlight that bold It Center that okay we cannotalize it too that looks a little bit good okay so I like that so now what I would like to have down here this is going to be the total do so right here I'm going to put total do then above that I'm going to put the total paid how much was paid then above that I'm also going to put the invoice total invoice total so I want that there I'm going to expand this column so that it covers that and then I'm going to again I'm going to right justify those now these are all going to be a mouse field so we're going to go ahead and set accounting currency either one would be fine so now we've got that okay so I'm going to give these borders and I'm also going to give our invoice an entire thick border so I'm going to format those cells again then we're going to go in to our border color here and I'm going to set it basic in on our significant color here and then giving it this a little bit thicker line border all the way around okay I'd like to have a message here so this I'm going to use this as let's say a thank you message so I'm going to merge and center that and I'm going to left justify that and put that on the top or maybe in the middle I think top middle is fine okay we'll put something like thank you let's do capitals U for your business and then exclamation point okay I like that again let's do the top and let's do top and left okay so now that we have that what I would like to do is I also want to just I bold and italicize that so you can see that and make it a little bit larger font here we're going to add the borders in now I'm going to stick with the I'm going to format those cells again now I'm going to stick with the called bottom and right borders and then I'm going to use a thinner border again we can use black or blue whichevers are consistent and then I'll use the middle and then so that's going to give us this Borders or one so now we've got our message and of course I want a top border on here as well all right so now that things are looking up pretty good we've got things that are consistent here on our invoice and I'm going to give it some dotted lines to separate those rows so it's probably just some dotted lines here okay so we'll choose this dotted line and only on the center that's exactly what I want and I guess we can do right and left on those because we have not touched the outside boards which I want okay our invoice is looking pretty good now now we've designed the screen we're going to save our work as we should always do we're going to add some button sets we want the button sets to be able to add save and delete the entries up here I want the invoice button sets to go around here so for invoice and we also need a search invoice so when we search let's do search invoice this is going to be a field that allows you to search for a particular invoice since it's an entry field to color it white and we're going to then use the middle and then I'm going to format those cells again with that same theme borders around there okay so we're going to select those borders here all the way around and then the dotted line in the middle vertical okay that looks good I'm going to left justify that and right justify this perfect so now we've got our design pretty much done we can zoom out a little bit take a look to see how we're doing we got our button sets that we're going to be going we'll be adding some data validation I'll be going over that with you got a project so we got a bunch of data validation and of course we're going to add some admin information in this column but I like the way that it looks now it's looking pretty good let's add our button set so what we're going to do is I'm going to use insert shapes and we're just going to go the basic square button We'll add some icons as well so our first button here we're going to call this add entry we're going to format that just the way we like it and then once we get it formatted we can then duplicate it for the other buttons so adding entry the button is a little bit High there and then what I'm going to do is I'm going to right just if I put it in the middle and adding entry is good now we'll give it a very let's say a specific color based on a theme but a little bit darker so I want these buttons to stand out so we're going to use this darker color here and I like that but no border on that so we can just take the outline and then no outline on that we don't need an outline okay that's looking pretty good at entry we can bold it if we choose to but sometimes I don't know okay so I like add entry so now I'm going to Simply use the Ctrl D I'm going to duplicate that and we're going to use this for Save entry so we'll just change instead of add it's going to be safe okay next up I'm going to again duplicate that we need to be able to duplicate our billing excuse me delete our billing entries so this is going to be delete entry okay I like that now let's just hold the control line those up ingly and then we want to separate them horizontally which will do that I'll send them okay so that's looking pretty good saving our work I'm going to now create buttons for our invoice I'm going to basically these are a little bit High let's go to 0.26 on that we don't need them that high okay looking good nice and streamlined I'm just going to basically duplicate one of these buttons here then we're going to use it for the invoice so control D and I'm going to slide this button over here I need to add some buttons for our invoice so we're going to bring it down here about here this is going to be for new invoice so new invoice okay I'll make that a little bit bigger so we can say that and then let's do that again duplicate that here bring it down here we're going to use that for Save invoice so we're going to use that for Save and then again duplicating that I also need to be able to print the invoice so we're going to print the invoice and then the lastly I need to be able to delete an invoice so we're going to again delete that and then we'll just go ahead and add some icons we'll use the inherent icon so delete you know okay I like that these can be a little bit larger so we'll go on 0.28 on those all right so now what we have is we have the we have all of our buttons that are getting lined up we have them all set up and what I'd like to do now is add some icons for all these buttons so we're going to go to insert icons here we're going to be insert so first thing we want to use add I'll use a plus for that so well let's just do let's type in plus we want to add in those okay once we have the plus I'm going to select it and insert it I'll take care of the size and coloring once we get them all in Saving tree we're going to use a check so again I'm going to go into the icons and I'm going to search for a check once we have that check box here it'll search for it and then I'll use this check it's a little bit it's fine and then we're going to insert that and then I also want to recycle so we're going to insert and that's going to be used for both deletes icons we're going to search for recycle r-e-c-y-c-l-e okay so now we've got cycle student spell right now we've got recycled there I'm going to use this one here I'm going to insert that and then lastly we need to print our invoice so we're going to use that so insert icons for the last time this time and actually I'm going to need one more icon so print so print we're going to add that after we that and then go ahead and select that printer one more icon that we're going to use when we add the items add build items I need an arrow for that so we're going to insert icons and then we're going to search for arrows so Arrow and then we'll use let's say I got one this one I like this one okay and insert that once we have all the icons here um they're all here I basically want to select them all so I'm going to use the selection button and then I'm just going to surround all these I want to make them all white so Graphics philadel I think I missed one let's select there we go I want to make them all white and I also want to set them to 0.2 so that's going to set the height and the width okay we can zoom back and now what we can do is we can place it we'll remove that selection we'll take a look and see what we have so moving this over and we have our I'm going to put this one here okay so that's going to be our and I also need to create a button for that so what I'm going to do is I'm going to duplicate this using Ctrl D I'm going to remove all of the text for this here because I need to create a background for this one and then we can shrink it up and I'll make that quite small so we'll go with 0.18 and then 0.18 on that that'll be sufficient for that point and then do the same thing for your all right point two that's going to have to be sufficient here for this all right I like that so now we've got the background here for this where all we need to do is either send that to back or move this up to the top so we can do that I'm moving it up to the top that's going to be our button that's going to actually add items to the invoice so it's going to be add billing so I'm going to use my selection tool I want to make sure that it's centered properly so I'm going to use the vertical horizontal here then I'm going to group them together and that looks good it's a little bit too big on the Arrow but that's okay we can adjust that size unselecting the selection and then we can just we want that Arrow a little bit smaller there okay so we like that now this button is going to appear as soon as we select an item it's going to appear there so we can zoom out that's its job we're going to give it a very specific name we're going to call this add item button okay now it's name now let's turn our attention back onto our buttons here so we're going to use this as new invoice and then what I'm going to do is I'm going to duplicate that icon using Ctrl D and I'm just going to drag that same icon over we're going to use it to add entries so add in entry or new is going to take on the same icon save is also going to do the same thing our printer icons are going to go right here our recycle is going to go here we're going to duplicate that again Ctrl D we're going to use the same thing for the billing icon so I'm going to move it over here for delete entry we're going to use that same icon here then lastly we also have the save again we're going to use it the same the invoice we're going to duplicate that and bring it on over here we're going to then group our button so everything looks nice saving okay so to do that all we need to do is Select make sure they're centered in the middle and group them together we can reduce the size of this button a little bit so that it looks a little bit more even then what I want to do is I also want to make sure that everything is centered properly so how do we do that we select both put them in the middle and then we're going to group them together delete this one I'll make a little bit bigger I want things consistent so then I'll again centering them and then grouping them together now we have all three buttons I'm going to then put them all in the middle so they're evenly vertical and then I also want to make sure that they have even spaces between them so again we're going to use this tool right here called distribute horizontally I want to group them together and I also want to go into the properties now once I group them together if I decide to change those column widths I do not want that group to also change so what I want to make sure is in the shape options here when we look into properties I want to make sure that we're moving but not sizing with cells so that's very important okay we're going to go back here and we're going to do the same thing as we did we're going to get again again we're going to make sure they're in the middle we're going to group them if we don't necessarily need to in this particular chain we don't need to name these buttons it's not necessary but if your Distributing an application it's always a good idea to do that whoops it's always a good idea to name them but we're not going to do it because we have limited time and I don't want to try to keep strings short okay very good now that we've grouped them all so they're individual groups again I want to make sure that they're all centered ingly here and I also want to make sure they're spaced ingly so we're going to space them out again I'm going to do the same thing here I'm going to group them and I want to make sure they're movement on size with cells there's one more thing I want to be able to navigate to previous and next invoices so to do that we're going to grab some shapes I'm going to add these shapes so we're going to use this the right angle here and I'm going to give it the same exact color as we did which is this color here but no border on that again so again we're going to remove the borders no shape outline on that we'll just make it a little bit thicker so they're going to use these buttons to navigate from previous to next Once I have the right shape that I want all I'm going to do is just going to duplicate that using Ctrl D I'm going to move it over and then what I'm doing is I'm going to rotate it I'm going to flip it horizontal so that we have that using our angle I'm going to make sure that they're the same right okay they look good we can group those as well if we want and we can group those with the main and group everything together again every time you group make sure you set the move but outside your cells okay great we have our invoices looking pretty good I like the way that that looks now we have to do is add in some data validation I'll walk you through the code as well if you are new to VBA I've got Auto macro add-in or if you're experienced automatic is an incredible add-in that is going to help you code a whole lot faster if you spent hours writing the same VBA code over and over like I do then Auto Mac or add-in is going to be a huge help for you if you are constantly searching for code Snippets Auto macro has all that and a whole lot more our add-in for VBA installs directly into the Visual Basic editor and comes loaded with code generators and extensor code library and the ability to create your own code Library which is really amazing with the Autumn macro you can save time and streamline your workflow by automating repetitive tasks and easily access pre-built code stimics plus of course our helpful tools and utilities make debugging and troubleshooting a bridge so make sure you grab that I'll include the link down below upgrade your VBA game today so that's Auto macro include the links down below it's a great sponsor of ours all right let's take a quick look under the hood and see what makes up this application now of course we're going to be saving invoices so we need to save that data when you save customer name invoice number the invoice date and so on and project we need to save all that information so we can save it to another sheet now what other sheet we're going to use the invoice list so this invoice list is everything that's going to be saved so we'll be saving it right here and of course we have the invoice ID the date the customer name the due date project and the page now of course we want to map that data to make it easier when we program it so what we want to do is we want to make sure that the invoice date is mapped to N4 so if we take a look back on our sheet here we take a look invoice date here is N4 you see that so we're going to map that so we do that for each field and I'll be showing you what ba team we're going to add some Fields there and five is the due date and so on and so forth so that's going to be the invoice list it's going to save all of our information okay and now as you know notice of course each individual invoice has a list of potential items so we need to save those items somewhere else and that's going to be on another sheet it's going to be a sheet called this like invoice item sheet inside that sheet we'll be saving the invoice ID the service date the service item description the hours the rate the total I need to know what row Associated on that invoice 10 11 9 and so on and so forth what does that mean that means what row this is row nine this is row 10 this is row 11. so I need to know what row it's been saved on so that when I load those items back into the invoice I know exactly what row on the invoice to put them is it going to go on road 9 10 or 11. so that row is going to be saved that's going to be saved right here inside column H I also need to know the row of the database that's this row right here this is row four this row five and this row six I could say that with a formula eventually we're going to have a formula in here so it's automatically added and I'll show you how to do that although there's no formula right now okay so the best way to do that is to use that with a formula so as we add in that information it automatically gets saved the reason I want to save a database I don't put that database in a hidden row right here is because if I make a change to this let's say I change the description or the service item I need to know what row to make those changes to so if I make a change to let's say the website I know that this is Row 4 to make those changes okay great so now we also understand that we want to save some billing items we're going to be adding some billing items here a project customer and I want to save those billing apps I want them to be displayed here but I want them to be saved somewhere else and those billing entries are going to be saved here we're going to have an entry ID which is a unique ID for every billing entry the customer ID and the customer name project ID the project name a service ID and a service name so I'll show you what those are the date what is the date what is the hours in the description and the rate and whether it's been billed or not and of course the road that's associated this one has the formula okay I mentioned service items here we saw the service items so we have a table here of service items this is a list of service items we have an item id we have an item name description and hourly rate so we can add as many as we want in there we also have projects again with project ID the project name customer ID and the customer name eventually we'll be adding and filling in all of this and I'll be doing a lot more on our patreon platform so I'll be doing things like this so if you have ideas you want to add to this project or maybe you don't understand something or maybe you want something fixed or you had an issue with it I do all that on patreon so every single week I create an updated video I also created an updated application and I present those for our patreon members and it's a great way for what you need to get additional and extra training and additional have your ideas see them in action so we do all that plus a whole lot more you get PF code books and I'll include the link down below so make sure you join us on patreon and lastly we have a list of customers so we have a customer ID customer name address city state ZIP phone and email okay so we got all the information of a customer we won't be using all that so that's basically the brunt of it so what I want to do now is add some more admin info information in here that it's going to help us with this particular so we can save a lot of information so what type of information so admin remember these columns A and B will be hidden for the end user so they would not see that okay and I've got some saved name range that are going to help us let's go over some of the named branches that I've already created to save a little bit of time so we're going to go to the name manager and we have a billing entry ID now all these almost all these are Dynamic named ranges meaning as our data grows so does our named ranges so see the billing into your ID is using an offset formula as they all do and we're going to familiarize so billing entry ID is going to be for all the entries as we add more this will increase we also have our customer ID customer ID is the same using offset I've got a customer name so we've got one for customer names we got one for customer projects so these are individual projects for a customer so if I select a specific customer I want to know all of the projects that are available for that specific customer only those therefore when we select the customer only those projects associated with the customer will appear so we're gonna I'm gonna show you that I also have the invoice ID another Dynamic named range for the invoice IDs we have the project ID another Dynamic name range for project IDs and one for project names I also have again the last two service Item ID so for all of our service items we've got a dynamic named range and then lastly for service item name okay great so that's everything we have for that so what do we want to do we're going to add that information in here so I want to keep track so we're going to start out with here I'm going to put the bill entry ID I'm going to zoom in a little bit so you can see here what we're working on here and then Let's Escape out of there and then zoom in here okay so now as we move over I want to keep track of a lot of stuff I want to know what id so we'll do the billing entry ID so every time we have a new billing information it's going to be associated with an ID that ID is going to go inside B3 here next up I want to know the row Bill entry row what is the row that's associated with that and I want the next billing entry number now remember next I want to know the next available when we add a brand new one what is the next available row okay I'm going to drop this down here and I'm still using some format features so I'm going to drop it down and just give those a bit of a color there and then all of these I'm going to basically right justify this and I'm going to left justify these just so we can get some consistency and I'll just cover the borders all the way until let's say 25 here so we'll give them borders all the way around color doesn't necessarily matter okay next up so that's all this information that's going to be for this billing entry next up I want to know what is the customer ID when I select the customer what is the ID of that customer so let's call it the billing because we have more than one we have a customer here for the invoice and I've got a customer here for the billing so I'm going to put this one as the billing entry custom our ID and I also want to know what billing entry customer I want to know the row that's associated with that customer and I also want to know the next customer ID in case we want to add a customer we can do that next customer ID that's going to help us okay that's good for the customer so we'll give that some unique colors that's going to be specific to the billing customers we can just give it this color here okay what else do we want well I want the project I want information about the project what project ID was selected so we're going to go this close to billing entry project ID so that's very specific to that so we're going to have it kind of a data validation a drop down list for all the projects here but they're specific to the customer okay next up what I want to know is also the row that's associated so we'll just call the billing entry project row so the row and of course I'm going to know the next project okay next project ID okay so that's very specific to our project so we're going to give that a different color that's going to help us differentiate the data when we're looking at it so that we don't get a confused although it's certainly easy for me okay next up that's associated with our projects next up I'll get I have Services here right we have a service ID the row that's associated so we're pretty much going to do that so it calls the billing entry keep consistent service ID and I also need to know the billing entry Service Road what is the row that's associated right there's a specific row if we're going to adding our service items are here I need to know what row they're associated with so we want to keep track of it I also want to know the next service right so next service ID so I'm going to have that here and we'll give those a unique color so we can separate those keep it accurate here okay next up what I want to do is I want to know how many uh mandatory fields are there all right well let's actually move these up I'm going to move these up just a little bit one row because I'm running out of room here move it up just a little bit okay so next up what I want to do is I want to be able to have the billing items Fields how many fields this is going to be required when we add a billing entry I need to make sure that they have a customer a project and a service I want to make sure and I also want to make sure that we have a service date maybe number of hours so I want to make sure some of these are required Fields what I want to do is I want to count those fields so that I know how many are required so how are we going to do that well what we're going to do let's go ahead and color these these are all going to be associated with that with a different ID so we're going to give those a unique color here and inside that we're going to call this billing items field I want to know how many fields are associated with that and because if there's less like if they have not filled out the required Fields I'm going to let them know so we can count those so what fields are we going to be counting well I want to make sure that we're counting at least the hours can be left empty but I want to make sure that there's a customer project service and a service date so how do we do that we can use count a for that so I'm going to use equals count a and one of the I'm going to hold down the control customer project service and a service data I want to make sure that we have have all those inside VBA if this is not 4 I'm going to let the user know to make sure they have the required Fields okay next up I want to make sure that the total service items right so I'm going to add some information here so let's go ahead and add in some additional let's take a look at here I want to put in make sure some check boxes on the form controls giving the user more control when we add a service item I want to let the user know do they want me to erase this because I don't necessarily like the font on this one I want to use consistent font throughout and so what I want to do is I want to give the user the ability to say total like service items maybe there's another way I can name that but basically when we have a service item it's the same let's say we have two service items are the same do we a want to just add up the hours so let's say we have one entry for five hours and one entry for six hours but they're the same service item do we total them all together into 11 hours or do we have them as separate entries we want to give the user the end user the ability to choose that so we're going to use this box right here I'm going to use a very specific cell to locate that so we're going to use it that here it's going to be called inside B15 so I'm going to give it a name inside a15 total so service items okay so here this check box here I'm going to format the control and I'm going to assign the cell link is going to be B15 clicking ok so that way when they select it that cell will go from True to false I want another one so I'm going to duplicate this and you know how here we got build whether it's build it's going to be yes or no here I want to give the user the ability to show only to include something that's built so I'm going to type in include build and then let's just right justify that just so it's on the edge there and so here in this option box so the question is if they if this is unchecked basically only those items that have not been built so this will be no are going to show up here so that means so but if it's unchecked both yes and no will show up here so it's kind of a nice feature here I'm going to put this one right here so you're going to call this include build so I want that link to include build linked to B16 so right clicking that format the control and then we're going to change that to B16 so again as the user selects it inside VBA will be able to know include build that's going to change the true or false okay next up I also want a button here I'm going to duplicate this button I don't think we're going to be able to get to that in this particular training but I want to put it here as a reminder here and then we can put it anywhere we want actually let's take this here I'm going to put this over here and put copy this I'm going to put it right about here and then I'm going to clear it from here we don't need the extra box that got created here clearing that and then because I need to have space for a button and basically what I'd like is a feature that allows users to add all right so I'm going to put that right here we're going to call it add all item let's say you've time tracked maybe 10 items for a single customer you want to add them all into the invoice I like the ability to have a button to do all of that as opposed to using this button here which just adds a single so as we select on those items it's going to add them here which is kind of nice but so I'm just going to use this here kind of this double check box here I'm going to use that also so we're going to just to make sure that I have everything so I'm going to use the selection make sure that they're all lined up grouping that okay so that's going to be add all items so I'm not sure if we're going to be able to get to that but certainly I buy it on patreon I'll make sure to do that okay so I like that there again saving our work so now what do we want here I want to have the last one I want to know the selected Bill inside it B17 the selected Bill entry row so the row that means the row that they've selected I want that road to go right here because we're going to use conditional formatting for that okay the next up next Fields we're going to set a bunch of fields probably all the way through 22. I'm going to mark this a little bit green and I want this to be for the fields that are going to be for the invoice so these are very specific to the invoice the first of which is I want to know the invoice customer ID when they select the customer here what is the ID for that customer so we're going to call this the invoice customer ID and we don't want to get it confused with the billing customer ID this is when we're going to add billions but we want to put this now I could have put this on a separate screen but it's kind of nice to have everything here at the same screen so what is the idea of that I also want to know what the row is so invoice customer row I want to know what the row associated with that and I also want to know what the invoice row is what is the row of this invoice what I mean by row notice that there's an invoice list if it's invoice ID one it's going to be row four two is going to be 5. so I need to know what rows associated with that and I also need to know the next invoice ID next invoice ID what is the next available one in this case it would be three and I want to put that directly inside there okay lastly in 22 as far as the invoice I want to know the search row invoice search row what does this mean let's close this for now and that means what if when they put an ID in here let's say they put invoice number one I want to know what row is associated with that if they've put an incorrect ID no row will show up here so we're going to use formulas in these and I'm going to show you those formulas all right lastly let's give it a last one we need some information here so I'm going to give this this yellow color here and I want to put so I want to know when certain things are happening that's going to help us throughout the code entry load that means when we're loading an entry if I select an entry I want the information that we've selected to load up here so when I make a selection on all the entries here I want the information to load up here now I need to separate that when I make it change here to a Pro customer or project or service I want certain things to happen I need to differentiate between these two types of changes one change when they select and loaded another change when the user makes a selection so I want to know if this is true or false this is simply going to be true or false so when the entry is loading it's going to be different and I'll walk you through that of course in the code invoice load I want to know when the invoice is loading and that's going to be true or false and I also want to know when the item load item load fast okay so what I mean by item load when I make a selection here and I click here and I add the item to the invoice that is certain as an action right we want to know but also when I select an item here I want the description hours and rate to show up when I select it manually so I need to differentiate between these two types of actions one we're adding an item based on the billing entry here or two when the user actually just doesn't have a billing item and they make a selection here so those are kind of two different actions I want to keep track of both of those differentiate those two actions so these are going to help us okay so that's it let's focus on some of the named ranges inside our data validation so obviously we have customer here so if we go into the data and we go to data validation and we go to list now I'm going to let the user I'm going to unselect the arrow now let's let's select list so what is the list we're going to use F3 what we're going to do is we're going to want the customer name so I'm going to click on OK and here's the area alert all I want to give the user the ability soon to add new customers and basically if the customer wrote doesn't exist say do you want to add this customer if so the customer gets added therefore I'm going to not show any error alert on that we'll have a pop-up on that so that's going to be customer name okay next up we want projects now the projects are going to be linked to a specific customer so when we add a customer I want only those customer projects to show up here so we're going to use a macro to do that but what I want to make sure is that we have that name Branch so I've already created that another formula is name manager if we take a look inside customer projects that's here basically it's the result of a certain customer ID so it is this named range that we're going to be using customer project so I'm going to copy that I'm going to close this out and we're going to go in again into the data and then data validation here and then what we're going to do is we're going to do list here and then equals and then we're going to paste that in and again also in the area alert we don't we want to give the user the ability to add brand new projects for specific customers so to do that want to have the hair clicking okay I'm going to try to get to as much as I can in this training this is a massive application potentially and so I also want to hear your feedback so we're going to get this all programmed up just as I'd show you but you're going to have a lot more ideas and I'll be putting those on patreon so we'll get this done and then there'll be a lot more to do so potential service what about the service item again I want to know all the service item data validation again adding it here again going into the settings it's going to be a list we'll use F3 to make sure we get the exact name here and we can see that we have the down below we have service name here clicking ok so again error alert giving you the ability to add brand new ones clicking OK description is going to be automated or it can be typed in so when I select a service name I want whatever description to set for that that would mean this description here I want to automate inside here inside this okay so we have that and the service date the number hours so let's put in a date make sure it's formatted correct the hours 5 about the billing rate let's say 25 and the build yes or no this is also going to be data validation so again it's going to be a yes or no if we go into data and then data validation here this is going to be a list value here and then we're just simply going to type in yes comma no that's all we need for that because it's either going to be yes we're going to default it to no okay so we got the customer projects we got the description everything is looking really good that's just the way I want it okay now that we have our named ranges we know what we're adding here let's go ahead and we're going to put the customer name in here too so again let's go ahead and add the data validation and then let's go ahead and list that and I want to put in the source again it is customer name so equals customer and then names here underscore name actually not names okay so now that we have that customer name here we can drop that down and also we're going to put the project I want all projects to show up here just so the user has more flexibility data validation going back in lists and then it's going to be equals we can use F3 if we're not sure the names we're going to go project name clicking okay okay now that we've got that set up we can select from any project here although we can certainly have another Dynamic based on this customer but just a little bit more flexibility okay so what do we want let's go ahead and add in some formulas here what is the billing entry ID well it's going to be based on whatever we select so if I select something here I'm going to have that ID placed in here so let's start out with one now if we look inside the billing entries we already have entry ID one it's on row four so what I want to do is I want to know row four I want to extract row four so if I go back to the invoice and I want to know what row Associated we're going to use a formula to do that so equals if there's an error of course all of it make sure that I account for that so we're going to use the match formula and I'm going to look up this particular billing entry ID I'm going to match it based on the billing entry IDs here I want an exact match but we're adding three because it starts on row four so we're adding three and if there's an error we're going to space that's what I want Row 4 Associated if I change that to two it's going to update to five perfect okay and if I change it to some other number that doesn't exist it's going to be empty all right what is the next billing ID so we're going to use again equals if error there's no data could create an error we're going to use the max formula billing entry ID is the name the next available one could be plus one if there's no data I just wanted to default to one that's going to give us the next so we know the next one is seven if we look at our billing entries we see we've got six here so we know the next one is seven all right next up we also want our customer ID what is the ID that's associated with this customer we're going to use an index for that again also wrapping on if error we're going to use the index what are we indexing or indexing that customer ID what row we're going to use match to find the row we're going to look up this customer name and we're going to base it on the customer named range we want an exact match also I want the column it's going to be column one okay if there's an error it's going to show empty and that means the customer ID for Dolores is customer ID too if we look in our customers we look up Dolores we see that a customer ID too that's exactly what we want okay I need to know the row that's associated with this again equals if air we can use a match formula basing on that customer name we're looking at the customer name based on the customer names here again we know that we want an exact match and we also know that they start on row four so we're simply adding three if there's an error we're going to show empty okay so we know that Dolores is on row five if we look here we see that Dolores is on row five okay good I also want to know the next customer ID because if we add a brand new customer we can quickly assign a brand new ID and we're going to wrap it on a fair reason again the max formula based on the customer ID I'm going to add one if there's an error we're just simply going to default to one so the next customer ID is 14 in particular looking customers we see the next one would be 14. all right now what we want to do is project so this kind of repetition is going to help you learn a lot what about the projects going to be based on this we want that project ID just as we did with the customers we're going to use an index match so to do that we're going to use equals if error in case there's an error we use Index this time we're indexing what we're indicing the project IDs run a match to determine the row number we're going to look up the project name and we're going to base it on that project name here project name that's the name range again exact match we want to know comments give a single column if there's an error we're just going to show empty okay perfect that's what we want and it's showing zero because we need to get the data we haven't added any data but just just for a sample I'm just going to use this one I'm going to copy this one our results are going to be right here I'm going to paste that in there paste the values this is our list here so now we go back inside our invoice we're going to make sure that we have that available to us so we're going to go inside and we see that when we select on this one now Betty website we do have project ID one that's what we want let's take a quick look inside our projects to make sure that Betty website design is Project number one perfect okay next up I want to know the rows associated with that again equals if error and then once we go into that we're going to use the match formula we want to look for the row we're going to look up this project and we're going to base it on the project names project names and then once we have that we want an exact match just as we did the rest and also adding three on that because we're looking for the row if there's an error we're just going to use semicolons okay so that means we know that Betty website design is on row four so if we look at that very website design is on row four all right I also want to know the next project ID again just as we did before equals uh if air we're going to use the max formula Max basing on the project IDs plus one there's an error which is going to return one get rid of that double we don't need that okay so now that we have all of that we can add in what about the service ID what's the id located for this service just as we did before we're going to use the equals if error we're goi