Collaborate on Invoice Reconciliation Template Excel for Technical Support with Ease Using airSlate SignNow

Invoice reconciliation template excel for Technical Support

creating invoices in Excel is nothing new what about when we need to track installment payments when we need to create multiple payments on multiple frequencies how do we keep track of the payments when they're due hi this is Randy with Excel for Freelancers and today the invoice with installment plan is going to be created I'm going to show you how to create your own invoice with any type of installment payment plan so that you can automatically create those payment plans track payments and whenn they're due and best of all we're going to do it all from scratch I cannot wait so let's get started thank you so much for joining me I've got a fantastic training invoice with installment payments we're going to show you how do you can create your own invoice with any type of installment payment at any type of frequency we're going to create this incredible application we're going to create it and design it absolutely from scratch it's going to have print email invoice you're going to be able to add additional payments custom payments and a whole lot more so it's going to be a really incredible training so I really like the idea of this so first I wanted to thank Nathaniel Nathaniel uh had a suggestion on YouTube comment and of course I respond to each and every one of your comments he said he wants to create a system for a hire purchase company where clients will make initial payment and they given three to six months to make payments depending upon the item they bought or the amount of course given some considerations so it's a great idea and I really liked it to be able to uh change the payment system and customize it on any frequency and so that's exactly what I've done here this week right so you're going to be able to add payments create installments based on any type of frequency so it's going to be really fantastic training because I'm going to walk you through every step as we design it from scratch so we've got multiple items we've got popup date Pickers here we've got print we've got invoice navigation we've got uh search for invoice so it's really a cool training so I hope you do like these trainings I create these each and every week for you and also I want to make sure that you get subscribed and don't forget to click that notification icon belt that's going to ensure that you get these trainings each and every week when I create them every single Tuesday I create these incredible applications and walk you through every step sometimes design them from scratch so that you can understand learn how to build these larger applications for VBA beginners basic training that's going to be on the weekends every Saturday I'm now creating a short 15 to 30 training so you can learn the foundational basics of VBA so you can round up and get really really good at all the fundamentals enable to create these applications so I do that for you so I do appreciate it a great way to support us is with patreon just a few weeks ago we created this really cool work order manager on YouTube so that was really nice and added a ton of features for our patreon members so our patreon members now have this really cool scheduler with full navigation so all those work orders can get scheduled up when we select on a work order all the details we can save and update those we've got filtering so that's just one of the features that our patreon or YouTube uh silver members are getting so I'll put the link down below patreon is a great way to support this channel for just a few dollars a month you get updates upgrades and of course these are all based on your ideas right here we had different ideas from Larry Duffy he added suggestions so we put those in here scheduling a whole lot so make sure you're on patreon that's a great way to get updates and lots of cool things all right so here's what we're going to do we're going to create this application we'll go a rough overview of it although I already have then what we're going to do is we're going to start on this blank template and we're going to design it from scratch and then I'm going to go over every step of the code with you so that's exactly what we're going to do this is a relatively simple invoice we just added payments to it so we can create a brand new invoice by clicking this button we have some default information those defaults are set up in our admin screen here so we can set certain invoice default we can customize the tax we can set frequencies we can then have a customer message and the really cool thing about this invoice is we have a full payment schedule let's pull up an invoice uh this invoice here so we see we have the payment schedule at the bottom of the invoice so the customer can get the full payment schedule which is really nice we can print the invoice we can email the invoice in a PDF selected and a PDF will automatically be able to view and see that invoice as we can see it down here so that's kind of a really cool feature inside a PDF I'm going to show you how you can do that we also can create installment payments so for example if I create a brand new invoice here and I just select a customer here and I select a frequency we're going to start the payments on this and then I just add some items to this here it's very very simple so then let's just put in 100 and 100 okay so we have a $16,000 invoice total we're going to save that invoice and now we want to create a payment schedule so maybe we we want to create monthly payments of let's say 12 monthly payments all we need to do is just click one button that's going to automatically create a list of payments for that invoice each with their own due date once you get paid all you need to do is put in the paid date here and then the actual paid amount here and then you're good to go just save and update that that's going to record that payment you can edit it we can select on any payment so very very cool we got a lot of cool features in here and of course those payments are also listed down here on the invoices self along with a lot of information so that's what we're going to be creating today for you so we're going to get started right away and invoice navigation we can search I think that's pretty much it you know it's relatively simple we don't have a lot going on on this I do have a very basic invoice from scratch so this is kind of like a step above that where we can incorporate multiple installment payments with the invoice all right so let's get started what I'm going to do is I'm going to move this over to the side here and I might be looking it away I might be looking over here to make sure that I follow what we've done before so I've got two different screens open in case you see me looking to the left that's what I'll be doing what we're going to do here is we're going to take the first two columns I'm going to drop this down here show tabs and comment so that I can format that and we're going to give it a gray color this is going to be for our admin we'll be filling in some Fields here we're going to use this for our title some things as you see are already formatted just to save a little bit of time because these videos tend to be a little bit long so we're going to call this um invoice as you just saw so notice the formatting and the font is already there that just saves us a little bit of time since this is an intermediate training installment and on the work order I actually misspelled it and I was staring at that the entire time you will see me misspell it I'll try to check invoice with installment payments I think that's right okay so continue on so that's going to be our title we'll put in a background I'm going to save this row row two that's going to be for our buttons our invoice will start right about here so this is going to be invoice so I'm just going to put in invoice and notice that it's already formatted that simply just saves us a little bit of time can help us okay so now I'm going to start this out right here in D3 yep that's what I want going to put the customer in there so I want the customer name right an E3 and that's going to be a data validation drop- down list right here it's a merge cell already next up I want the invoice type the invoice type is going to be a drop- down list of a list of invoice types that are fully customizable and that's going to be on the admin screen next up is just a free cell called location you can put anything you want in there and then also I want the project name going to go right here okay next up I'm going to put in that's going to be whether we're going to use progress payments I guess we're going to I'm going to call them installment payments my initial was called use installment payments okay that's going to be just simply yes I'm just going to put in yes that's going to be a drop down list right there then I want to start on date that's going to be a date field I'm going to go up here and I want the invoice date right here invoice date next up below that I also want the invoice total that is going to be uh based on a formula so we're going to put invoice total I want the total paid next up and then of course lastly I want the the balance so we'll take a quick look at that this will be the balance we'll just call it invoice balance can abbreviate that okay so that's pretty much it for our main invoice information here we will be formatting that when we do everything else we're going to give it some color so what I'm going to do for these fields I'm going to hold down the control and then I'm going to Simply color these I want to give these a green background I've got some green colors saved here we're going to be using I believe this one right here okay let's color these two so what I'd like to do is I'd like to skip two rows we need a button set here for create and we also need add payments here so we're going to go a little bit below that right on D13 we're going to put that due date so due date this is going to be for our payment information below that I want to know the paid date as we saw in the sample so due date and pay date we're going to skip some information I'm going to put in the schedule notice it's already merg centered payment amount it's already long videos as I have to create this and of course go over all the code with you so actual payment amount so we have that in there so both of those are going to be important okay then what we're going to do is we're going to skip a row and right here we're going to put the installment payment schedule so all in caps installment payment schedule so this is going to be all the information we want the First Column will be that due date you want to know notice it's already been bold already payment amount that's going to be what our payment amount is and then paid on when was it paid on if it was paid I also want to know the paid I'll put in paid amount the actual paid amount is what I want to show in here and next up I want to have the balance so that's going to be calculated okay very good so we've got that information there we'll just give it that same background color so I'm going to hold down the control under all of these fields and we'll give it that green and we can put in a background I've got a background already set up I'm going to go to the background something I created with mid Journey here and just kind of a nice feature there so this is what I created the dark green one and then I made it light and change it a little bit and I've also got a logo there too so here's our background so I'm going to insert that here and that's going to insert that background okay so that's kind of a nice background now what we want to do is we want to make sure the user entered fields are going to be in white and we want to make sure the calculated fields are in green so we're going to change that to White so these are the ones that we're users we're going to enter and the invoice total total bait and invoice balance these are actually going to be green because we do not want users filling those in okay so that looks pretty good also these are all going to be user to entered so we want those in white as well now we can just do some formatting using a border so I'm going to hold down the control and I want to format each one of these individually using borders but since I'm holding down the control then we can format them as one so all we're going to do is just basically keep highlighting these as we want to as I'm holding down the control now I'm going to use control one and what that's going to do is going to launch the format here I can go to the Border here we'll just use the standard black color which is fine outline and then I'll use a dash on the middle okay that's going to be sufficient for our purposes there that looks good okay I'll have a button set up here this will be for our invoices I'll have a single button here we'll be adding the buttons soon right after I finish up so here we can put in the borders we can just set make sure that our line color is automatic and then I'll just do all borders on here we will add conditional formatting down here for alternating rows and I want to know the selected row I'm going to skip a column here and then we're going to have our invoice information here so our invoice here all the way down let's see where I went I'm going to check my sample here I went down all the way to row 53 that's the one so 5 three y that's correct so let's take a look all the way down here starting in column J and all the way down to 53 which is exactly right there I'm just going to color that white okay so that's going to be our basis for our invoice and now what we want to do is I want to have information for our total here so we've got our payment schedule here yeah that's it it's already emerg Center this we're going to call payment schedule because when we send the invoice to the customer we want to have that payment schedule this is going to be the payment uh number each individual payment will have it own number payment number which is kind of nice so the client knows what payment I want the scheduled payment amount we'll abbreviate that amount okay so that's the scheduled and then the due date due date here and then we'll do the pay date when's the actual pay so the client knows when they actually paid pay date and then paid amount so what is the amount they actually paid and then the balance okay so notice that these are exactly like these so in fact we're simply going to link whatever's in here is going to be in here so it's going to be nice except for the payment number these payment numbers are going to just set uh 1 through 12 right so that's really all I want to do is just set them to 1 through about 12 that's it now of course these will be hidden if there's a value here they will be displayed if there's no value in the schedule payment these will be hidden so keep that in mind and I'll also add conditional formatting to here okay so these are going to get a light border color in fact I'm going to also color our header row our header Row for our items is going to be let's take a look inside row seven that's where I want to go so let's add that into row seven which is here inside that we're going to put in our item date next up I want the item or part I'll want the item description next I want the quantity then would be the amount and then the total okay very good so that's it now we want to give those a color just want to use a very light background color so I'm going to uh hold down the control and we'll also actually color the borders and the backing also on these two so these fields are going to get a very light green so I've got a light green saved here I want borders all the way around here okay so that looks good so that's exactly what I want starting up here inside here I'm going to put the customer that's going to be in row three so I'm going to put in customer and we can do a callon that's going to be a customer that will just be linked in fact I can link it right now it'll be linked to whatever is located in right here perfect next up I'll have VBA handle the customer addresses in here and here so VBA will take care of it here lastly I want the project so we'll put in Project put a colon and then whatever's inside I'm going to use equals whatever's inside here we're going to do okay so when I select a customer here I want that customer to show up when I select a project name or add a project name Fred's bathroom I want it here okay perfect okay next up I want the invoice number to show up right about here so let's put in invoice number and then the invoice number is going to be here we'll set that that's going to be a formula also you won't need to edit anything in this that'll all be controlled from here which is going to be nice next up what I'd like to have is the invoice date so I'm going to put invoice date here and location and lastly we're going to have the balance due notice that it's already formatted there and the balance du is simply going to be equals to whatever is located right in here so we can start to format those the location again equals is going to be whatever we put in here and then the invoice date also equals is going to be whatever is located here lastly the invoice is actually going to be invoice number number is going to be in B2 so equals B2 that's where we're going to put that invoice number it's not here yet so while we're here why don't we put some information inside our admin section which is going to be columns both A and B so I want to know when the invoice is going to load that's going to be important why is that because when I make a change on here I want the item in description to load up and the quantity and the amount however when I'm loading the invoice from a previous I also want to make sure that we loaded whatever description was previously saved so I need to know the difference so it's going to be true or false it's something like that okay next up what I'd like to have is that invoice number invoice number invoice ID same thing so we're going to put an invoice number here that's going to be in B2 so let's just say that's two or whatever and then next up I want to know the invoice database row invoice database row now that row is important why is that important because if I look on our invoice list this is our list I've only got two saved I want to know that invoice number one is on row four or invoice number two is on row five how would I know that well I've got a named range already that's going to help us it's called invoice ID and that's basically just uh going to show is going to handle that and we're going to use an offset formula to handle that so now that we know that and we also know that it starts on row four if I want to know what row invoice number is on two which is five I'm going to use equals if air I'm going to run a match and I'm looking up this invoice number here and I'm going to look inside the name Branch it just showed you called invoice ID I want zero because it's an exact match I want to add three actually on that three because our first one starts on row four if there's an air I'm going to show empty it's going to tell us that the invoice database is on row five which is what I want I also want the next invoice number now that next invoice number equals we're going to use if air in case there's no data at all I'm going to use a Max formula invoice I know that I move fast but you're welcome to slow it down and I want to add one that's going to get us the next available invoice ID but if there's no date at all it will create an error but I want to default it to one to do that and that's exactly what we're going to have okay perfect going to give those a color and a border just so we can distinct those from anything else we'll give that a blue color here next up what I'd like to have is I want to know the selected customer ID selected customer ID now how do we know what the selected if I have a drop- down list of customers which I do I've got a named range for their customers if I look down here under customer name and I tab over to that we see that we have a customer database with a list of customers so that's exactly what I want that customer name is going to be our named range keep in mind that I also have one for customer ID so if I have a customer ID and I have a customer name what I can do is I can index that so the first thing we want to do is add some data validation here and we're going to put in a list and we need that customer name so equals customer uncore name that's our named range and now we see that we have a list okay great so we have Betty White here but we want to know the IDE of Betty White if I look in my customer list I see Betty White has an idea of one so how are we going to arrive at that well what we're going to to do is we're going to use equals we're going to index we could probably wrap it in if a it's always a good idea index what am I indexing I'm indexing that customer ID and I'm looking for a row what is that row I'm going to base it on that customer name so I'm going to look up Betty White here I'm going to look it up in the customer name named range here I want an exact match and I want column one if there's an air we'll just put empty okay that's going to tell me that Betty White is customer ID one perfect I want to know what row Betty white is on that's going to help us extract the email address if I know the row and I know the email address is in column H then I can simply extract that for our email so that's going to be kind of nice we could also index the email there's a few ways to get it so I want the selected customer database row and that's even easier all we need to do is we can match it based on the ID or based on the name either way so we're going to use if air again we're going to run a match this time I'll look based on that customer name here and I want to look up that customer name just as we had here so the customer customer name and also I want an exact match again customers start in row four just like we did with the invoice so I'm going to add three if there's an air I'm going to show empty it's going to tell me that Betty White is on row four if I look in our customers I see that Betty White is on row four perfect that's exactly what I want I'm going to leave a space here in case I want to add a new customer I want to put in the next customer ID maybe for patreons we have the ability to add a new customer so I'm going to give those a little bit of a distinct color these are for our customers okay next up I also want to know the search we need to put in our search invoice which is going to go right here so I'm going to put inside Q search invoice okay and then I want to give this a color which is our standard background color here and I want to give this a white color because this is where users will enter the search and then of course I'm going to enter the borders around there that's sufficient enough I'm going to add buttons here so when I search for an invoice I want to know if that's an accurate invoice number how do I know that well what I can do is I can put the search invoice database row what is the database row associated with that search value so equals if a going to match again just like we did before I'm looking up here what is that invoice number and I'm looking it up inside our invoice IDs and then I'm going to put zero I want an exact match if there's an errp put nothing that's going to actually I put in the row number is necessary here I just need to know that it's accurate so I know that it's on there okay that's perfect Also let's know the pay schedule fields we need to add some here I want to make sure that this should be white actually start on the user will enter that so these are some required Fields if we create these payment schedules I need to know that we have the frequency we need a frequency here actually and the number of payments so we need two more here let's add these and in fact I'm just going to copy these down here and then I'm going to paste the formats one more pasting those formats here and then here I'm going to put in the frequency which we need and then all I want the number of payments so there's some required Fields here number of payments okay I need some required Fields the frequency is required the starton and the number of payments are all required if I'm going to be creating those payments right so I need all three so I want to make sure that before the user presses or after they press it to make sure that they've add the frequency to start on and the number of payments so how do we ensure that well what I'm going to do is I'm just going to put in pay schedule Fields I want to know just by counting equals count a how many of these fields have been filled out holding down the control because all three of these are required right now it's zero however if we add in a frequency let's go ahead and do that data validation here going to add in that frequency drop down list list here and then what we're going to do is I'm just going to put in frequency equals I believe that is if you're not sure we can use F3 to double check that F3 will tell us we can look down here and we can see that uh frequencies is the correct name so that's what we want to do clear that out and then click okay so what that's going to do is list the those frequencies so now we see that it's going to be one okay giving those two unique color just to differentiate between everything else and okay so that looks good so now we want to know this is going to be a date format this is going to be the number of payments and so we want to make sure that all these fields get filled out I want to make sure that this is three okay next up I want some information for our payment information so we're going to start that on 11 here and I want to know the selected payment ID selected payment ID now each individual payment is going to have its own ID if we take a look at our invoice payments we see that each payment has a unique ID the invoice number that's associated with that payment the payment number like which payment number that's important so we can keep track of individual payment one two or three we want to make sure that this is not accurate I'll be updating that I want to know the customer name the payment amount here so we need that and also I want to know the due date here the actual payment amount the pay date and the row so all of that is very important we also want to keep track of the paid date and of course the row that's associated so I do need to update this payment number they should be unique for every single invoice so we'll be getting on that I'll be updating that although it's not pivotal everything still works without that okay so what I want to know is I want to know selected when you see a list of payments down here when I select on one I want that payment information be displayed here when I make a selection whatever payment ID you selected I wanted to go right here so let's just put in 35 or something like that I also want to know the next payment number this is what we're going to be working on next payment number so that's going to be important and what we're going to use is we'll just use count a for now CU I want to know that equals count a how many payments there so we'll use the large number because that's going to be for any individual unit plus one so that would be the next available payment number so if I've got a payment listed here and here and here this should be a a general not a date format then I know if there's three payments already I know the next payment number is going to be four okay all right continue on I also want to know the database row that's associated with this so this is going to be our payment database row and again we've got a payment ID so I'm going to use equals if air I'm going to run a match just like we did before I'm going to be looking up this payment ID and I got a named range just for that as we do always payment ID that's our payment ID all those unique payment IDs let's put in zero here for that exact match I want the row number so we're going to add three as they start on row four then if there's an errow we're going to put empty okay so that's going to tell me me right there there's an air right here payment ID so we want to match so the payment ID in B1 so let's make sure we have that payment ID let's see 15 okay so I got 15 on Row 18 that's double check to make sure that everything looks good that's going to be our invoice payments we see that we have payment ID number 15 and that's going to be on Row 18 that's exactly what I want perfect next up I want to know the next payment ID so what's that going to be next payment in case we have a new payment ID so that next you're going to use max formula equals if air using Max Max formula payment ID here plus one if there's no data we're just going to revert to one and so our next payment ID is 23 if we look on our invoice payments we see the last one's 22 the next one's 23 that's exactly what we want okay continuing on we move back inside our invoice here and then what we want inside row 15 I want the next payment number I think we got that we don't need that and I'm going to create the pay schedule I'll keep that one empty for now create pay schedule so basically what I want to do is I want to differentiate between when we're adding a brand new payment manually or when we're creating all them at the same time basically want to differentiate between those so that's it this one we're going to keep empty for a while I had a duplicate there on my sample so we don't need that I'm going to give those a distinct color so that we're going to use those for the payments and I use this yellow okay so these are associated with payments these are miscellaneous these are for our customer this is for invoice okay everything's organized we're ready to add our buttons now so what I'm going to do is I'm just going to insert and I'm going to get one button exactly the way I want it and then we can duplicate it from there so we're going to move on to this here and I don't need to outline on that so I'm going to use no outline I'm going to pick a unique color which we're going to use this color and I want of course a font color we'll use the white text and we're going to call this new invoice okay what we'll do is we'll format it just how we want it and then I think I can bold that so we'll go back in we'll bold it here I'll write justify it and put it in the middle here and then we want a little bit bigger here okay so that's sufficient for our purposes here and I think I'm going to set the height at about 027 or 0 28 that's fine okay great so the icons will last after we create all the buttons then we add our icons so we've got new invoice here I'm going to use controll D and that's going to duplicate it and we're going to use one for our save or update so this is simply going to be called save invoice keep it simple save or update so we can do save invoice or save or update I'll do save or update so that we know either one is fine save update okay so we're going to use that same macro for both saving and updating and lastly we're going to duplicate that and this one's going to be for our delete so we're going to call this delete invoice we've got enough space so it's no problem we can make buttons a little bit larger I want to save room for an icon so we'll need that on both on this and make them a little larger we also want to make sure that they're lined up so we can do that holding down the control making sure that they're lined up Okay so we've got our buttons here again icons come last after we create all the buttons so now what I want to do is I want to duplicate these so I'm going to use contrl D and now I'm going to duplicate this these are going to be for our payments so I'm going to bring this right down here so we're going to call this add payment we'll adjust the size of the button and then this one's going to be called save or update which is fine and then we're going to call this delete payment sometimes I don't necessarily like to have buttons with the same name so in other words both of these shouldn't probably be save or update even though cuz this one's saving or updating a payments this one's saving or updating invoice so we can change change this to save invoice just to make sure that people don't get confused save invoice and this would be for payments we'll create something similar so that we can always make sure the user knows exactly what they're clicking and what they're doing okay perfect I like that that's things are looking very good on that now what we want to do is I want to create a single button using contrl D I'm going to put it up here this is what's going to create our installment payment schedule we've got a lot of space so create installment payment and then schedule payment schedule okay so it's going to create and we can just make this a large button so we have enough space for everything and add a t onto that okay create payment schedule making sure I got the spelling right which I rarely do okay perfect so now we've got these buttons set up and I want to create some buttons on the right side and I'm just going to control d one more time here selecting on the outside not the text on the inside I'm going to bring it over here and then this is going to be for our print invoice so we'll just do print invoice and I want another one for the email invoice then I also want navigation for previous and next that's going to be a different button style so this one will be for our email I'll bring it down here email okay very good so lastly I'm going to insert shapes here and I'm going to use these arrow keys here this is going to be for our previous and next I'll just bring them down here for now I want it like this again I'll make it about the same height which is about. 27 we do want to have some text in there so outline and the shape fill this one's going to be called Next invoice and then I want to make sure to format that to maximize the text so reduce the padding control one's going to help us do that going to go into the text options here here in the text box here I'm going to remove all margins here get rid of all of that and then I want to make sure that it's centered here then I want to maximize the button uh width here so about 6 I think should be fine we'll increase it just enough to get the text okay so now that we have it just about how we want to what I'm going to do is I'm going to bring it up here and I'm GNA use control D it's going to duplicate that we're going to use this for our previous so I'm just going to change this to previous P our previous invoice okay now I just need to switch it so all I need to do is just go to the rotate here and then flip the horizontal okay so that's going to be good and just going to bring those line them up all right very good just the way that I like it I'll make sure this button height should be. 27 and this one looks a little bit different okay I guess they're both the same all right continuing on what I want to do is I want to make sure they're in the middle here bringing them down here and we're ready for our icons now we can browse for our icons so I'm going to do is do insert and then let's get out of there pictures we want them over the cells this device and then what I'll do is I'll enter I've got a bunch of icons here I'm going to use all of these except this one here okay so what I'm going to do can hide them all and then I'm going to click insert here also while we're thinking about it I got a little bit of a logo so let's insert that logo right now and then this device and that you just saw that logo right up here which is right here kind of an interesting logo now we can bring the logo up here and also we'll use our select section tool and I want to make sure the slide so I'm going to reduce that and now what I'm going to do is I'm going to just simply use our selection tool and I'm going to make sure all of these are point2 so reduce it really small which is fine and then we'll set oops too small there I want to make sure that I only encapsulated those icons and nothing else none of the shapes there setting those picture formats height point2 the width will automatically adjust now that we've got that we can then move them over there and this logo here will just shrink this down here to something reasonable and then move it over here we can turn off our selection tool here now we can start to position our icons here and we can bring them over here so first thing what we want to do the print we're going to bring that up here that's going to go over that email a little bit hard to see because it's kind of white that'll go here then our delete and then our scheduling here component we bring that over here and then we're going to be duplicating some icons because we're going to be using the same ones for both adding new and saving so this is going to be for our scheduling here this is is going to be for our saving and I'm just going to do controll D because it's going to be used for both our payments and our invoice the same thing with the delete and same thing control D here we're going to use this for both here and then we'll size them up for a delete and then this one also for New Again contrl D and then we're going to add a payment okay great we can zoom in a little bit and now we can set our sizes ingly and we want to make sure that we can group them ingly so we'll check our button sizes I'm going to hold down the control here make sure in the middle group them do the same thing here we'll do this for all the buttons in the middle group them and then we can increase the width of this to make sure that we have enough room for the icon okay that's looking good also group them doing the same thing here making sure that we have enough space in the middle and then grouping them going back up here we also want to make sure that we set size but don't size or shape so we're going to set that in a minute in the property section as I hold down the control each one and group them individually we'll then group them together which is going to be kind of important okay so now that we have them what I want to do is I want to make sure that they're all even I want them adjusted right distribute them horizontally and then group them we're going to do the same thing with these here holding down the control to make sure they're all in the middle these quick access toolbars really help for those things and grouping them together okay very good so what I'm going to do now is I'm going to hold down the control and I want to check the properties here and I want to make sure here that we're moving but we're not sizing I'm going to do the same thing with this I want these also aligned grouped together oh let's uh update these I'm going to zoom in a little bit so we can get a look at these two buttons then we'll be assigning macros to these and I'll show you exactly how that's going to work we're just about done with the entire screen design so it's been fun then we'll get into functions but I'll go step by step so you don't have to worry about that okay very good so now that we have all three of these grouped I'm going to line them up in the middle make sure that they're spaced bring them down ingly that looks good I do want to set these also make sure that when you regroup you reset that property value okay continuing on let's bring it back to 100% zoom and see where we're at okay things are looking pretty good now we've got everything done let's go ahead and add some conditional formatting saving our work should have done that a lot longer ago one more thing I want to do is just a little bit of a shape here I just want to put a box around this section right here with a thin line oops let's bring that right about here of course there's going to be no fill on that so no fill on that and we'll make sure the Border colors are consistent with our theme which is this color and that just kind of groups our payments together so we can see how that is together okay very good I like that things are looking good let's add some conditional formatting now when we have payments here I want the payment ID if we remember correctly invoice payments everyone has an invoice let's just say I've got these payments going in here and inside our invoice I want to put these IDs here so let's say we're going to have that information here let me turn off I've got some formatting and I want to turn off that notice it said custom I just want to bring it to General okay so we have our due date here our payment so we're going to bring in those IDs all that information information is going to come directly in here so I want conditional formatting in here I want three different rules so I'll go over those rules with you so the first thing is we can just highlight that go to conditional formatting manage rules and we're going to create a brand new rule and there's going to be two conditions on the first rule so we're going to use a formula and it's going to be equals and and what the first condition is I want to make sure that due date is going to always be required so we'll make sure that we have a due date so d18 but it's going to be for every row around so we don't want to use absolute on the row we want to use dynamic does not equal empty so that's the first rule do not equal empty the second rule is mod I want to use even rows which is the mod of row two equals zero so that's even rows so what do we want when that is I'll color that white so we'll give that a fill color here and we'll give it a white color and I'll do a border around that in the bottom left and right so I'll use our standard color I use a dotted line and I'll use the left the bottom and the right so those are the two conditions we're going to click okay we'll just make this a large number so we'll do 99 on the row okay that's sufficient okay so now what we're going to is one I'm going to edit this rule just make it a little bit faster I'm going to copy this I need a rule for odd rows so I'm going to do new rule here use a formula and I'm going to paste it in odd rows is going to be for one so I'm going to change this to one and then close parentheses there formatting almost similar except this one we're going to use custom color now the colors that we've been using are only available inside let me show you that again they're only available in the fill effects so I'm going to use this light green right here I'm going to use the same color right click okay now the borders are going to be exactly like we did the white and to choose our color here dotted line on the left the bottom and the right clicking okay so the only difference is these are for odd rows and clicking okay so then also we want to make sure the applies to is the same as before so I'm just going to copy this applies to and paste it up there I want one more Rule and that's going to be for the selected row so I'm going to add a new rule and I'm going use a formula and again it's going to be two conditions equals and and I want to make sure that the two conditions are this one I want to make sure that c 18 and any row associated with that so all the rows below does not equal empty there has to be an ID there that's associated next up I want whatever's the payment ID in b11 I want to make sure that that is equal to whatever's located in CN any row now any row makes sure that we get rid of the dollar sign so those are the two conditions that means whatever payment ID if it's found we're going to give it a color so I'm going to give it a unique color that it stands out so the font is going to go to bold and it's going to go to White next up what I want the fill and I'm going to go into the fill effects and I want to give it uh let's say these two colors right here these darker colors clicking okay and okay okay so that's exactly what I want to look like and click okay and then the applies to should be the same as before so I'm going to click applies to and paste and click apply click okay okay great so the idea is this when we add information in here put in a date it could be anything it doesn't matter so we see how that our conditional formatting now notice that this is three when I change this to three we want that row to be highlighted exactly so we see that there's a match so that's the conditional formatting we're looking for the match and when it's found it's highlighted what we used to do is we used to select a row but I found another shortcut that reduces code using this so I like this row better okay very very cool so what I'd like to do now is I want something similar down here so I'm simply going to copy this and I'm going to paste it right down here and then we're going to paste the formats here now it's not going to be a date so what we're going to do is I do want it colored background white and I want this to be General but those conditional formattings it's going to be a little bit easier I can now manage those and now what we can do is we can update those there's going to be no selected row so we're going to delete that so basically what I want to do is use similar rules but this is going to be column J so we do need to update that and it does start on row 42 which is correct but we do need to change this to J so we're going to change that to J clicking okay and we're going to do the same thing for here edit Rule and then we need to change the applies to so this is going to be J clicking okay but otherwise the same two rules apply now the applies to Simply is going to start in j42 it's going to go for all these rows here these are going to be what the customer sees copy this and paste it here and then apply now actually what we can do is actually I want to set it to column K I got a better idea column K the reason is I want to put the numbers always there but I don't want the numbers visible so I only want data in column K and I'll show you what we're going to do it's kind of a cool trick for column K so the idea is this click okay okay and then apply everything else looks good okay so there's a scheduled payment amount here and here and here and here so that looks good right but what I don't want is that let's say there's four payments I don't want these numbers to show up I I want them there but I don't want them visible so how do we do that that means if there's nothing here then don't show this so that's the idea behind this so let's do that what I'm going do is I'm going to go into conditional formatting add a new rule use a formula and this is basically the formula so when k42 equals empty then what do I want to do now it's going to be any row so we need to remove that dollar sign there 42 equals empty then what is the format I want to set the format and we're going to go to the number format I'm going to customize this and what I want to do is I want to change this to two semic columns which is going to hide those numbers click okay and I just want to make sure that the applies to is only for J which is correct so now we see when we add values the number shows up that's exactly what I want perfect however the information that we have here is simply going to be linked this is always going to remain consistent the scheduled payment amount is going to equal what it's going to be equal whatever is in here so what I'm going to do so I'm just going to go down about 12 rows here which is what we have I'm going to copy this and then what I want to do is I want to paste the links and that means so whatever payment amount information shows up which I need to change that to zero not empty what do I mean by that notice they showed up why because there's a value now look there's a value so what we really need to do is on the conditional formatting is change it instead of empty it should be does not equal zero it should be zero which is good click okay and click apply and perfect now when we add information look E18 so when I add information to E18 let's say I add a payment amount here of 15 we then see the one show up if I add another one here 15 we then see it and that's exactly what what I'm saying is I think uh it's just a preference of well yeah on the sample I did we will keep these alternating even if they contain no data so it's kind of nice I'll just keep the colors the way they are even if there's no data but I definitely don't want to show that okay that looks good I like the way that that looks now what we have we've got our conditional formatting set up we're going to save our work inside we need to add some little bit of formatting on these columns I want dotted line on the inside of these so we're going to do that right about here updating it right to this and I want dotted lines on the separate of the column so format control one will launch this we're just going to Simply add those black dotted lines on the left the middle and the right right and clicking okay I want the entire invoice surrounded by a thick border so we're going to add that around and then control one to do that and then what we want to do is we'll put that thick border all the way around clicking okay all right so our invoice is looking pretty good we need to add some total information this area right here is going to be where our messages the end of our invoice and our total so I'm going to format that we're going to put this line border up at the top that looks good here's where I want the information so I also want a line border on the right here that's going to separate so we've got this large Square for a good uh customer message this customer message what's it going to be I want linked so I want it link to whatever we put in the admin screen where's that admin screen it's right over here what do we want it's customer message right here I want it linked right to there so it says thanks so much for your business perfect here's we're going to have our total information so I'm going to put in the sub total here I want to put in the tax information that's going to be a formula what is that going to be now let's take a quick look inside there I've got some tax information are we charging ing tax yes or no the name of this is called charge tax I got a tax name we can put any tax name we want it's called tax name and I've got a tax rate so it's 10% so knowing that we can put in some kind of a custom tax information so equals if it's going to be a formula and let's go up here so we can see it if the charge tax are we charging tax equals no then what are we going to put we simply put nothing in there right then nothing what if we are charging sales tax if we are then I want to know the tax name and I want to put in something else I want to put in the percentage of that tax so and and I want to put it in here in parenthesis so I'm going to use quotation marks the parentheses and I want the tax percentage the tax rate now here's the tax rate but I kind of wanted to format it it so let's use text because I want to format that and I want to give it a specific format so we're going to use 0.00 percentage that's the format that I want we need to surround that in quotation marks otherwise it won't work so we're going to add them in here here and here okay great and what else I want to just end the parentheses so and sign the end parentheses here and then that's it okay so let's take a look at that and see how that works let's finish that up okay so perfect so we've got here charge sales tax no tax name so we see here it's going to show GST 10% which is exactly what I want if I decide to change this to 9.5 it's going to show just this oops let's put this drop down that one that was what I want okay so now what we see is going to show 9.5 that's exactly what I want so now again we need another formula here we need to know what that is so let's put in some information here I need some information here and I need a total here so equals if again are we charging sales tax charge sales tax equals no then just put in zero which is fine no then zero otherwise what what is it we're going to charge the tax rate then tax rate times what is it times the subtotal which is going to be right here that's all we have to do okay there's no total there yet but we'll get to that okay next up I want the total the total is simply going to be the subtotal plus the tax so equals the sum we can do that here just the sum of these two values here okay great so that's our total I also want to know the total payments so total payments we abbreviates that and then where's our total payments I'm going to put them all the way up here this is going to be right up here total paid right here okay great so we have that there now what we're going to go is the balance I want the balance so we're going to put in the balance or balance do I think balance do works better balance do so what does the balance do it's simply going to be equal to the total minus the total payments okay perfect so we don't have any data in there but we'll get there I'm going to format that so again you can use control one or you can use formatting the cells and we'll just add some borders around there so this border right right here should be sufficient and this border dotted line here okay that looks pretty good saving our work so far all right we got a few more things to tidy up here I want a dotted line on the column here so we're going to again control one put that dotted line on the right side which is here and of course we're going to need item what item do we need we've got some items and parts here got a bunch of information here you know to get this list really quick I used a really cool application that I built this called get table data this is in the AI tool pack I've got six different features if you want this so what I needed is a bunch of parts and I didn't want to have to put them all in so I just put in something like construction items include name description and price this should be amount by the way okay so I just select here and then all I need is just click get table data and with this AI tool pack that I developed I'll include the link down below you just wait a few minutes and even less few seconds and it's going to come to you you'll see the processing and then what it's going to do is it's G to get a number of items now you can also request the number of items too which is kind of cool so here you see you've got a bunch of uh items and just automatically brings it if you were to ask for more ask for a number of items it will do that so that's just one of six different features get table data so that's kind of a cool thing I wanted to show you all right so we do have an item name in here so if we go into the formulas name manager and we click on the item and we see that we have item name and that item name is the drop- down list that I want to use for our invoice so we're going to close this up go back to our invoice and we're going to hold shift control there and we're going to add some data validation so data data validation here and we want to use a list and it's going to be the equals item name underscore name okay so that's it we move along we can also remove these grid lines we don't need them anymore so we can get rid of those now we can select on them that's exactly what I want we can also start typing it in and it's going to come up for Excel 365 uh automatically it will use auto complete which is kind of cool finally took a many years but here we go so I really like that so that's automatic we used to have to include the list down below in the same column for updated versions of excel you don't need that anymore which is a welcome upgrade okay great so we want to put in an item date here I'm going to have a popup calendar here why don't we add that in right now format looks good so when I select on something I want to popup calendar I've got one here that I'm going to use I'm going to copy this and I'm just going to bring it in here and we're going to be using that soon once we get VBA activated okay let's take a quick look we need the invoice total I need some formulas here the invoice total is simply going to be equal to whatever the total is right here so that's our amount okay so that way if we put in one we need a formula here so if I put in 10 and 15 I want a formula that's always going to be constant on this invoice that will automatically total this up so equals if I only want that to appear when we have an amount and a quantity so equals if two conditions the quantity does not equal empty and I want to make sure that the amount does not equal empty when those two conditions are true then I want something to happen then I want to Simply multiply the quantity times of course the amount that's all I want if either one of is empty then I'm going to show nothing okay we need an and here almost forgot about that so and that's what I want the condition is so there we go so if it's false that's what we're going to do so that's exactly what I want I'm going to take this formula copy that and bring it all the way down here and then pasting that in there so contrl + C going down here and then paste the formulas here that's what we're going to do pasting these formulas okay good so now as we add in data we want that to automatically populate perfect so we want to make sure the subtotal is here so that's going to be equals the sum of basically everything up here so that's perfect and enter okay whoops there we go let's fix that up sub total sum of what here a little too fast on those bringing them down here okay very good now that we have that I also want the balance do equals that's going to be in several different places I want it right here okay I also want the balance do the invoice balance this one is actually going to be calculated so we should color that in Green Giving it that green color here invoice balance is simply equal to whatever the balance is so we have it calculated in multiple places which is what I really want to see so I've got it here here here and here the total paid is going to be simply equal to the sum of what all the paid amount here so everything all the way down we'll just use a large column here and that's the total paid okay very good so now we have the total paid so if we were to add $100 onto that we would see that our invoice balance is simply equal so everything checks out just the way we want it perfect we've already total the payments so we've got the total payments so everything is looking really good now saving our work so far number of payments this is just going to be a general should not be a currency here that's exactly what I want monthly we have that use installment payments yes we do want to use installment payments this should be a yes or no data validation here and then list here and then yes comma no that's sufficient and then click okay all right so that's exactly what I want now we can probably add some conditional formatting down here if it's no maybe we really don't want to see anything here so what do we want to happen if it's no we don't need any of this here so I'm going to some conditional formatting so we're going to go into the home manage rules and then we'll do a new rule and use a formula and it's going to Simply Be This equals no right so if it's no we don't necessarily need to see those fields so I'm going to format those we don't want to give the user any ideas I