Effortless Bill Making in Excel for Financial Services

Bill making in excel for Financial Services

hello this is Randy with Excel for Freelancers  and welcome to the invoice payments manager   in this week's training I'm going to show  you how to create this incredible invoice   payments application complete with one  click apply payments applying payments   to multiple invoices previous payments  navigation automated invoice balances   and we're going to do it all from scratch  every feature every function formula and   line of code built before your eyes  I cannot wait so let's get started all right thanks so much for joining me here today  I've got a really fantastic training it's going   to be from scratch this is what we are going to  build an invoice payments manager we will be able   to add brand new payments we're going to be able  to navigate to previous payments we'll be able   to edit those previous payments if a customer has  a bounce we're going to be able to see that we're   going to be able to apply payments to multiple  invoices if a customer has multiple open invoices   and will be able to apply just like by selecting  or of course we can edit the payment amount and   we can enter the total payment amount here delete  payments previous and next add news save payments   now the best thing about this application of  course is that it's already tied to this invoice   now previously we did this invoice training  this Dynamic invoice last week and so now we're   building on top of that so if you want to catch  last week's training on this Dynamic incredible   invoice I have that for you available I'll make  it for you put the link down in the description   and that was a great training so we're building on  top of that now creating now a payment structure   on top of that this is a sample this is exactly  what we are going to be creating I'll be putting   this away and of course building it for you I  hope you do like these trainings we're going to   be going back to every single week as you have  and of course I'll try to do more from scratch   a lot of you asked me to do from scratch I can do  smaller applications just like this when they're   from scratch the larger applications we're  still going to have to walk through otherwise   larger applications you know would take five six  hours and I don't think we always have that kind   of time although some of you do and still as  much as I would love to do that we'll try to   keep these trainings down to one to two hours if  possible I know they're long and extensive but I   try to give you as much value as possible each and  every training and I do appreciate your continued   support if you like to support us there's several  ways that you can do that first of course Very   simply just by subscribing to the channel clicking  that like button or commenting below let me know   what you like any feedback you have I create  these tradings for you each and every week   so I do appreciate another way to do that is  through our patreon form not patreon I create   these trainings every single week and of course  I take your suggestions your ideas your comments   and I put them into a brand new updated training  for these templates I put in whatever features you   might want to add whatever fixes there might  be and of course I focus on certain areas so   the feature fix our focus is a great updated  training with an updated workbook we also have   PDF workbook downloads and that means all the code  in a beautiful PDF a format I add that on patreon   lots of other features going on into the patreon  platform where I answer your questions directly   through the comments and of course private  messaging there as well so join us on patreon   that would be great all right let's get started  so this is exactly what we're going to be able to   create add new delete previous and next payment  so they're going to be able to apply that what   we're going to do this is a sample we're putting  this away I'm going to put this over on another   screen I can take a look at it and this is the one  we are going to create so we've got a blank screen   here payments notice it's complete really empty  we're building it we've got an admin screen that's   already done now the admin screen most of that is  for the previous application the only thing that   I've added to this is something called payment  types payment types is going to allow us to do   payment types we have a payment list these are a  list of payments that it got created so every time   we add a new payment and then individual items  per payment we'll be adding here so these are   individual when we make an individual payment on  a specific invoice that gets tracked here invoice   the rest of this is part of another the rest of  the invoice application which we covered in other   trainings but it's all combined to create a really  cool application we also have a list of invoice a   list of invoice items for the invoices customer  list items that are calendar pop-up okay so this   is what I want to focus on this payment so this is  the screen that we're going to be working on right   now and so the first thing what I want to do is I  want to reserve columns A and B for admin so what   I'm going to do is I'm just going to highlight  those here if you've seen my videos before you   do understand that we use these for admin there  will be using just a few information there and   then what we'll do is on the top line maybe what  we'll do is we'll add a larger text so what I'll   do is call this invoice payments manager as you  saw in the sample invoice payments manager and   what we're going to be doing on this is I want  to add of course I FedEx just a little bit of   a color so what I'm going to do is I'm going to  format these cells and I'll give it a fill now   of course your color ranges may be different but  that's okay fill effects here and then what we'll   do is I'll add a little bit using this blue and  this medium so we're going from medium to lighter   and then I'm going to click OK and I'm just going  to copy this format all the way over here and we   can highlight and then we're going to paste those  formats in here I want to give this a larger font   we'll go with let's say 30 and I'm going to give  it a different type of a font here we'll go with   Ariel rounded here which is one of my favorites  area rounded Mt bolt I'll double click on this   that's going to expand it automatically we have  invoice payments manager and I'll give it a color   like this okay all right maybe 30 is a bit big  so we'll go with 26 it's gonna take up because   I've got the zoomed in okay the next lineup where  we do this but is going to be for our button row   so we're going to make sure that we have enough  space for our buttons on this row here more like   a menus also give it some fill effects and we're  going to start out at the same color and go to a   lighter color this lighter color is going to  be our background color so it's going to go   from darker to lighter clicking okay and then  I'm just going to select a bunch of cells down   here and then I'm going to give it that main  background color here that I want all the way   up to column P so we'll go ahead and do that so  we've got that here and this is going to be for   our button sets and then what I want to do inside  let's say uh D what I want to create this is going   to be our apply so I want to apply that so our  table is going to be down here starting at let's   say d10 I'm going to call this apply a so when  we apply payments we want to select it so it's   going to be applied the next column over I want  to do invoice date and then I'm going to have the   invoice number after that invoice amount and after  that previous payments and then I want the balance   here so what is the balance and then whatever the  amount applied and I'm going to format that with   our theme so I'm going to highlight that we'll  go all the way borders around it and then format   those cells sorry it's off the screen and then  fill effects similar to our theme so we'll use   a fill effects here and then we'll use the two  color theme here which is the same as our theme   and then down here okay so that's going to be  sufficient there and then I'll go ahead and do   control B that's going to bold it and then we'll  Center that I like that for the total I also want   to know what the total applied is so we'll just  put that here total applied and I want to know   how would I know what is applied so I'm going to  italicize that and basically every time I select   something here I want that amount all everything  that we've applied here so it's going to be this   column but only for those select so that's what  that's going to be so let's put inside three this   is going to be our customer I wanted to select  a customer we're going to skip a row we're going   to put our payment type and then I'm going to  skip another row and we're going to put payment   notes and this will be a double row for that so  I'm going to hold down the control and I'm going   to merge the center all three of these and then  I'm going to right justify so merge and center   then right justify that then what we do inside  let's say f and g I want the same thing f and g   those are going to be the field so I'm going to  call those white merges Center and left justify   that payment notes we're going to extend all the  way to let's say J so we're going to merge and   center that let's justify upper and color white  okay so that's going to be our payment notes so I   put the top next up I want to also have a payment  date so inside column I am going to put the label   payment date and then I want payment amount we'll  abbreviate that also giving it our theme which is   white and then left justify it so I'm going to  hold down the control and we're going to do some   borders I'm going to format those so a little bit  off the screen giving it borders that are fit our   theme so we'll do border and then I'll do do a  color let's say this color all the outline borders   and on the left side we're going to do that dotted  line not diagonal okay I like that similar holding   down the control button we are going to then  format those cells here and then I want to use   the same border but this time I'm going to use the  the straight line on the upper left and the lower   one keeping that dotted line on the bottom okay I  like that I'm going to format this cells too this   one I just want that solid here because that's  on the right there that's the way it looks good   we've got payment date customer payment type and  notes and then we have our table down here now our   table I'm just going to give it a white color  a white base color all the way from column D   to column J and giving it that white so we've got  that white I also want to give it some conditional   formatting a little bit different here so as we  add data here I want that conditional formatting   to appear here what I'm going to do is I'm going  to highlight all these and can highlight more   but that's a good start conditional formatting  manage rules and uh for all of them I want to   put a dotted line so I'm going to click a new rule  use a formula and that's going to be basically any   line with a value where let's say the invoice  date does not equal empty and it's going to be   for any row so I'm not going to put the dollar  sign before the 11 and I'm going to say it does   not equal empty so it does not equal empty there  that's going to be for the rule and all I want to   do is just simply add a dotted line here so we're  going to do the Border here using that same color   that we've been using that darker blue and I'm  going to add a lower border so that means for   every line with a value I want to put it there I  like that that looks good now what I want to do is   I also want to add some so let's go ahead and take  a look at that so now as we add in the invoice   date we get that color okay good so I want to also  work on alternating rows now alternating rows what   I want to do is I want to give them a little bit  more of a color like you saw on the sample but I   only want to do that for this because I want the  amount applied to show up a white you know always   white because that way the user knows those are  user entered fielded in white whereas the column   colored backgrounds tend to be a little bit darker  you know they're not editable there so what we're   going to do is click on new rules condition  formatting now this is going to be based on   two conditions equals and so it's two conditions  the first condition is whether there is a value in   column e so again in the column starting at e the  second value is going to be what is going to be an   even row so what we'll do is we'll do mod row here  and mod of row two equals zero those are even rows   and I want to give that a format and I'll go into  the fill and I'll give it a single fill color but   fill effects I got some safe colors here this is  a very light color here and a light blue so I've   got that here and click ok now what I'm going to  do is I'm going to copy this because I also want   it for the autores but I don't want to retype that  so I'm going to click on OK and I only wanted to   apply to these rows that's correct that's what I  want and then I want to do conditional formatting   and then new rule I'm going to use a formula I'm  going to paste in that but this is going to be for   odd row so I'm going to put in a 1 here I'm going  to format that I'm going to give it a little bit   darker color so we're going to go to the fill  effects we're going to choose this darker color   here okay give us a nice alternating okay so  I like that there that looks good and click OK   and then okay so we see how we've got darker and  lighter here but now I just want to do one thing   is I want to add the same rule here but I want to  extend this so that way we've got an extension it   looks a little bit nicer all I'm going to do is  conditional formatting manage the rules and in   the lighter one I'm just going to extend that to  one additional column to include J so that's going   to give it a nice look and of course we only want  it to affect those particular rows the container   value so we're going to remove this dollar sign  here and we want it means does not equal empty   we're going to do the same for the other rule  two it's clicking OK clicking edit that rule   again removing the dollar sign here does not equal  empty so we'll set that and apply that click ok   then apply we see that that only applies to those  rows that contain the value that's exactly what we   want now that we have our screen somewhat set up  what we're going to be doing is we're going to be   adding our button set so let's merge and center  this we'll add some shapes to start out we'll   just use our basic shapes here so oval and we're  going to be adding about three buttons in the two   navigations so we'll take this as our button and  I'm going to use this one here that's a good start   for our button and then maybe just a different  fill color so let's add a little bit darker fill   ing to our theme Here I like that so what  I'm going to do is I'm going to format this one   here size and properties here and I want to make  sure that we have a little less space for the text   right or more space for the text less space  around the button so what we're going to do   is we're going to go into the text options here  inside the text we're going to go to the right   margin I'm going to make this .05 we're going to  be duplicating this button so it's a good way to   get it started we probably don't need any top  margins or any bottom margins on that and we'll   call this save payments so I want to set this font  to White here if it's not set up right here and   then what we'll do is we'll do save payment and I  want this right Justified here so we're going to   right justify it that way we have room for the  icon I'll make sure that let's say it's set to   0.22 and the width right around 1.2 here that's  good okay so what I'm going to do then is I'm   going to duplicate this using Ctrl D I want one  for the add new and which we'll place right here   and I want another one for the delete payment so  I'm going to duplicate that and that's going to be   delete so this one we're going to call this delete  payment we need to make this just slightly larger   here so we'll make this 1.3 or 1.4 that should  be fine and this one's going to be the add new   buttons okay we'll move that over back over where  it belongs right here and also what I want to do   is I want to make sure that they're all lined up  and now we're going to add navigation so they're   all the same height what we'll do is I'll do the  control and I'll just make sure they're all in   the same row and then distribute it ingly  horizontally like that that's good now we've got   room for the icons okay so we have a room we've  got our nice button set here we do want to add   some icons and of course I'm going to add the logo  so we're going to do is insert picture and I've   got some saved up if you do want these particular  icons and Logo they'll be available on our patreon   platform so I'll reset the heightened with to  0.2 because our button sets and I'll make the   logo a little bit bigger so I want to take that  logo right here I'm just going to put that right   here now we have our add new I'm going to put that  here they're a little bit big but no worries then   I'm going to put our save here and I'm going to  put our particular recycle bin here that's going   to be used for the delete I'm going to hold down  the shift then I'll make this a little bit bigger   so we have some 0.25 we'll do that on the button  sets makes them a little larger holding down the   control selecting the icons I want them all in  the same line so we're going to do that I like   the way that looks now what we want to do is  group them individually after we set the width   just the way we want enough room for the icon so  hold down the control I'm going to again line them   and group them we're going to do the same thing  with this one here aligning them grouping them   and the last thing with delete payment we'll make  that one a little bit larger too so we have space   for the icon Okay so we've got our buttons set up  all here now all we need to do is the navigation   so I'm going to insert another shape here but  this one we're going to use the triangle shape   here the arrow shape so this is is going to be of  course our I'm going to shape fill here let's give   it this one and then shape fill it again like that  okay so I'm gonna make this a little bit larger we   have to add some text inside there so that's going  to be important we need to be it needs to be large   enough for the text this is going to be called  our next button we do want to set in this case   we want to make sure that we have enough room for  the text so again I'm going to go into the text   options here I'll zero out all the margins here  that's going to give us a little bit more room   and then we will Center this and then put this in  the middle so that's going to be for our next can   make it a little larger and then all I want to  do is do previous so all I'm going to do is move   this over here I'm going to duplicate that using  Ctrl D when you go into the shape format and then   I'm going to rotate this and I'm going to flip it  horizontal and then we'll just change the text to   previous p r e v and then period okay that looks  pretty good now what we'll do is we'll move those   over here exactly where we want them lined here we  will give this a little bit more of a height .30   just slightly more and I like the way that that  looks make sure they're all in the line I'm going   to group these two individually and then also I'm  going to align these to make sure everything's   all lined up vertically and then what we do is  we're going to group the entire shape now after   we group it you know we want to move but we don't  want to change the size so what we're going to do   is we're going to go into the shape options here  and we want to make sure to go into the properties   here and move but don't size with the cell so  that's very important so we've got our button set   up we've got our logo set up let's save our work  what we've done so far and we'll get some formats   the payment date I want that formatted as a short  date and make sure it's left Justified the payment   amount I want that's an accounting the payment  type is going to be a drop down list now let's   take a look inside our admin I've already created  something called payment types and I've already   created a Dame range for that so if we look inside  our formulas and our name manager and we scroll   all the way down here we have something called  pay types this is using an offset formula so it's   Dynamic based on this it's going to Encompass all  the payment types so that's when I want the drop   down list for the payment so if I go back into  the payments in the payment types I'm going to   do data then we're going to do database validation  and we're going to do list now you can use F3 to   automatically select if you're not sure which one  where it is you can do just select it from here   and it's pay types and click OK and click ok now  we've got data validation for the payment types   we can put any notes in our field here but I want  it left Justified here and I want it on the top   that's what I want now the customer also we've got  a list of customers in our table here I've already   added name Grange called customer name so if  we're going again formula name manager and we see   customer name again this is a dynamic named range  for all the customer names so customer underscore   names so again going inside our payments data  validation here and we're going to add a new   brand new data validation under list and this  is going to be equals customer names clicking   OK click name not name so with a single there's no  s at the end of that okay so now we have that so   now we can select from our customer list all right  I like that we've got a payment date let's check   the format on that so if we like that and we've  got a payment amount here okay that formats okay   so we're set up with the formats we're looking  good now what we want to do is we want to take   advantage of this column here I want to put  some information in here so what is it that I   want to put well we're just going to be using new  I want to know if we're going to be loading when   we're loading a payments or not and that means  when I make a change to a customer I want on a   brand new for adding a brand new invoice I want  all the unpaid customer invoices to show up here   however when we load an existing payment that we  previously created I do want to load all the ones   that were paid before so basically we're making a  change to customer on a few different times right   and I want to differentiate between those we're  also going to be making a change here so I want   to differentiate between when we're loading a  previous payment or we're creating a new one so   we just need to sell tell us what we're different  so we're going to call this is called payment load   and this is going to be a true or false we'll put  it as false for now and also what I want to know   is I want to know the payment ID each individual  payment has its own ID and I want to know what   row that is associated payment row and I also want  to know the next payment ID and what we do is give   those distinct color those two columns would be  hidden so user end users wouldn't see it and we'll   give it a border okay so now what is a payment  ID if we go into the formulas name manager we   look all under here I've created something called  pay ID if I tab over that we see that it is again   another offset Dynamic named range based on all  the payment IDs so as it grows what I need to know   is what rows associated with payment ID notice the  first one starts on row four that's what I want   so if we've selected payment ID one what row is  it on so we're going to use index match so we're   going to actually just use match for that with a  error so equals if air air catching we're going   to match it so what am I matching I'm looking up  the payment ID and what am I looking at under pay   ID that's the named range I just showed you we  want an exact match if it's found what I want   to do is is I want to add three to it because  I want the row associated with it if it's not   found I just want to use an empty it's going to  create an error so that's it for and I also want   to know the next one so what is the next one as  we add new ones so again equals if air I'll also   want to air trap that this time we're going to use  the max formula and then we're going to use pay ID   and I want the next one so we're going to do plus  one if there is no data at all it could create an   error of course it will can't find it so what I  want to do I want to set the default to one that   means if there's no data the first one is going to  be one all right good I like that let's go ahead   and let's justify that that's it for the column so  it's relatively simple on that okay so what do I   want to happen well if there's an invoice that's  located here and they select apply what I want to   do is I want to take whatever the balance is and  I want to move it I don't want to copy it to the   applied amount I want to assume that the payment  amount the applied amount is going to be the same   as the balance it may not be of course they can  change it but I want to make that assumption but   to do that I want to check mark to appear here  now how do I get a check mark well the best thing   to do if we go to insert and we take take a look  inside our symbol I want this check mark up here   how do I get this check mark well I know it's a  wingdings font and I know it's character 252 if   we zoom in on that we see wingdings 252 but I want  to use VBA to create that so the first thing that   I'm going to do is format this entire column as  Wingate so I'm going to hold down the shift and   then what we're going to do is we're going to go  to home then I'm just going to select Wing day so   I want that entire column to be the wing dates  next up inside VBA when do I want it to happen   well when I make a selection change that's exactly  what I want the change to happen if there's a   check box there I want to clear it if there's no  checkbox I want to add it so what we're going to   do is we're going to go into the developer alt  f11 is a shortcut to get you there and we're   going to go into the Visual Basic and this is  the sample that's been created so we don't need   to focus on that this is the one we're going to  look at the sheets called payments so this is the   payment sheet here's our sample one but here's the  payments this one's the one you're going to get   again it's going to be a selection chain so I'm  going to go up here I'm going to select worksheet   and selection changed the first one that came up  now when the user makes that selection change what   do we want to happen well the first thing is if  they make a selection change on a more than one   cell I wanted to exit the sub so if Target count  large is greater than one meaning they select   more than one cell then then exit sub this is a  good way to prevent errors okay so if they make a   selection if not intersex target range what is the  range that they're going to be selecting well it's   starting at d11 so let's put in d11 through D and  then let's put a large number 99 I'll put an even   larger number nothing if they make selection and  I also want to make sure that there's actually a   value so that means the date in range e so I want  to make sure there's a date and range e which is   the date column and the target row dot value does  not equal empty meaning there must be a date as   well then I want something to happen right so if  we take a look inside here when I make a selection   on D I want to make sure there's actually a  date or something here before I do do that so   what do I want to happen well basically if the  target value if equals empty nothing there then   the target dot value equals character 252. that's  the check marks else clear the else then there's   already the check box there then we want to clear  the cell right so else Target dot clear contents   then I want to select something else I just want  to select another cell so that way the selection   changes not so what we'll do is we'll use F3 as  that selection so we're going to select FD range   F3 dot select okay so what that's going to do is  automatically put that checkbox here when I make   that selection here let's reset that code here  there we go okay so now when I make a selection   change that checkbox is going to appear I do want  to Center that inside that column there so we'll   take that and then we'll go to home and then we'll  Center that and the reason we select something   else is because I can make a selection change to  the same cell in just one click that's why vbase   selects something else and it doesn't happen on  here because there is nothing in column e that's   exactly what I want now we're going to add more  code here once we we also want the balance here   applied to here if there's a balance here so let's  just say there's a balance here I want to put   that amount in amount applied now invoice amount  previous paint spouse those are all currency or   amount Fields so what we're going to do is we're  going to set that to okay so now we have balance   and not applied so we've applied that dollar sign  format we're using the accounting format in this   case to those fields so what do I want to happen  when I make a selection here if there's a check   box that I want to take whatever the balance is  then I want to put it to amount applied so let's   do that right now if Target dot value equals  character 252 which is that checkbox then what   do I want to do I want to take whatever balance is  there then whatever balance is located in column   here I and I want to place it in column J then  range J and the target dot row dot value equals   range I and the target dot row dot value else what  not let's say it's clear then I want to clear it   out else range J oops range J and Target dot row  dot clear content okay so let's take a look at   that and that's probably what I want to do so if  I unselect here nothing happens if I select here   the 454 if I unselect it it gets cleared out  that's exactly what I want to happen so we're   either applying or removing it based on that  check mark okay good so that's basically what   I want to happen with that selection character so  it automatically gets cleared out if we're putting   something and of course the dates are going to be  here not the F so what do I want to happen well if   we're adding a new what I want when I select the  customer I want all of those customer invoices   unpaid to appear here inside here and we can do  that so that's the first macro we're going to run   I want to load those customer only on new right it  has to be new if it's an existing one we're going   to load in the existing page so assuming that  payment row is not when we add new payment row   is going to be empty there's no payment Associated  I want to load all the unpaid invoices for door so   I'm going to load them right in here so let's take  a look at that and see how we do that also in VBA   now I've got a module called payment macros here  and I've got just a bunch of empty macros that   we're going to be creating and I've created some  variables dimensions and variables to help move   things a little bit faster last rows long last  result row payment Row the pay column the result   Row the last payment last payment ID and the  payment database row so we're going to go into   those and so what basically the first one we want  to do is just simply load those open invoices how   do we know that well if we look inside our invoice  list here we have an invoice list here a list of   all the invoices and the last column is balance  so we've created a formula every time we add a   brand new invoice we get that so if I go into here  and I create a brand new invoice and I select our   favorite customer delore course here and then I  say that but I also add some items here let's add   a date and some items here and so we automatically  are going to create that invoice so we're going to   save it so that brand new invoice save and update  if we look inside that invoice list that invoice   just got created here invoice number 11 so we see  that's invoice number 11 here and so we have that   balance of 465. we know there's a balance so  basically I want this anytime any customer for   Dolores and any balance and I want it to appear  here so I want to use an advanced filter and I   want that customer and the bounce above zero then  what I want to do is I want to have those results   appear here and I want to bring all those results  into our payments here okay so the first thing is   what I want to know is we need some criteria for  that advanced filter meaning we only want those   invoices for Dolores so how do we have that  well you see this is G3 so if we look inside   our invoice list list of invoices we've got our  criteria and this is automatically equal to after   actually after it's a merge cell right so if we  take a look here both column F and so it's really   F3 F3 is what we want to link it to so back inside  the invoice list so that's automatically so as I   change this so does that particular criteria for  that customer inside the invoice list so notice   it changed the jacket so it's so it's connected  and it's linked which is going to make things a   lot easier we don't need to have VBA do that for  us next up I also want to know anything above   zero right I'm only focused on those invoices that  have been unpaid and now the reason we want to do   this is because most time if you're paying a bill  right we may want to pay more than one invoice at   one time we may want to pay only partial invoices  and that's why this is really important if we only   have one payment per invoice we can put another  column here and call it payment and just put   a payment down here but what if the customer is  paying for more than one invoice at a time what if   the customer only pays half of the invoice or they  pay partial invoices then we need a separate table   of all the payments that's why we have it here all  the payments so Dolores makes a single payment of   fifteen hundred that particular payment then gets  applied to multiple invoices and those invoices   are attached to this payment item list so Dolores  notice this is pay ID one so Delores here made a   single payment both applied one applied to invoice  number two one applied to invoice number five so   if we look at our payment list we see that Dolores  Richmond amount 47.45 so this is the entire   payment amount pay ID one it got apply to two  different invoices and that's why that's really   important because we're able to apply a single  payment to multiple invoices or even partial   invoices that's what we want to do so to do that I  must know all of those unpaid invoices for Dolores   and put those right here now what we want to do is  we want to have the same columns as we do in this   so when I bring this results data over I wanted  to have it the same column so I want to have the   invoice date I want to have the invoice number  the invoice amount any previous payments the   balance or the amount applied so but let's take  a look at previous payments now previous payments   here as we know they all come from our payment  items so Dolores made two payments here she made   two payments here so these are all the previous  payments but I want to know previous payments   based on a single invoice so take a look at this  this is invoice id2 she made three different   payments one for 1502 one for 100 and one for 50.  so what I want to do is I want to know all of the   payments for invoice number two and I want to sum  those up so the best way to do that is to use a   sum if formula because I need to know and that's  what we're going to have here this is our previous   payments here and so the best way to do that is  to use some name branches so if we look inside   the payment I've created some name ranges that  are going to help us sum those payment items so   we're going to look into the formulas name manager  and we're going to go down here and we're going to   look for pay item ID so we're going to have a  few of us so we have pay item customer we have   pay item amounts and we have to pay item invoice  ID so let's look at pay item amount so that's the   name range based on the amount that they paid this  is a pay item based on the customer this is a pay   item based on the D the pay ID and this is the  one called based on the invoice so if I want to   sum all the payments made by a single invoice I'm  going to use some if to do that and I'm going to   place that formula right here so I'm going to have  a formula here this is the formula here actually   the particular total payments here is inside  the balance here particularly the balance here   this formula gets created every time we create a  brand new invoice so if there's an error so what   I want to know is the total invoice amount H3  minus sumif pay item invoice ID so anything for   that invoice ID this is the range invoice ID what  is the criteria that criteria is what's in A3 and   that is that invoice ID there so we're totaling up  all the pay items toting up all the amounts based   on that specific ID so we're going to track  all the payments and this is how you have an   accurate balance so the balance is basically the  total amount of invoice minus the total number of   payments for that specific invoice so that's as we  create that balance and this is what we create so   we have this total invoice here so we can create  that automatically so we know the total here based   on the balance here so if they've made payments  we can add that here so the results are invoice   date invoice ID total here so we know the total  payments how many previous payments they've made   and then the balance ingly so that way we  can get that so we see that on invoice number two   there have been a total of 1652 payments the total  is 653 and there's a balance of one dollar on that   okay so what I'm going to do is I'm going to bring  all that information over and I'm going to bring   it directly into the payments here in the same  day invoice date so all of this information is   going to be added here bringing that in here so I  know the total and then then we can put the amount   apply here so let's take a look how we would do  that inside VBA so the first thing we want to do   inside of here is I want to clear out anything  that was here before so all starting it with d11   here and going actually what we're going to do is  we're going to put some information in here in C11   so I'm going to clear out column c as well I'm  going to bring it all the way over to J so from   C to J we're going to clear that out so our sheets  called payments so payments range C 11 all the way   through J and a large row 99 and we're going to  clear that out dot clear contents okay so once I   clear that out I also want to make sure let's  just put in clear any previous data so that's   what we're going to be doing on there now what I  want to do is I want to set payments to true to   low remember we're loading those payments we put  that in B2 payments.range that's going to be in B2   we're setting that to True dot value equals true  so set payment load to True okay I also want to   set it to false at the very end so we can simply  copy this paste it down here and change this value   to false so when we make changes we want to make  sure it goes to true and then before the end we're   going to set up the false now most of the work  we're going to be doing is on that invoice list   so with invoice list okay that's the sheet that  we're going to be focused on I want to determine   the last row so what is that last row the last row  is simply equal to range and a 99 and XLR that's   going to give us our last row okay what is the  last row is less than four then we have no data   the last row is less than four then we're going to  exit the sub nothing else we can do assuming that   we do have data we're going to run an advanced  filter now that advanced filter is going to be   based on the Range so if we take a look inside  our invoice list our results have an empty column   notice that why does our results have an empty  column and that is because this column doesn't   exist if we look in here we see we have previous  payments well this is calculated our previous   payments are calculated so when we have so we  don't have that right because we don't have a   previous payments inside our invoice list so we're  leaving it blank blanket simply calculated our   previous payments are basically our total minus  our three total minus the balance right so that's   all we need to do minus T3 simply the balance okay  so we know the total invoice minus the pound so we   know how many total payments they've paid but this  isn't part of our original data so when we have   results that are not part of our original data we  need to include a blank column so what I'm going   to do is I'm going to set an advanced filter our  original data is going to be A2 all the way way   through K2 I'm including K2 because our results  also include a blank header row so we're going   to do that A2 here's A2 all the way through K2  so that's our original so we want A2 all the way   through K2 now our criteria what's our criteria is  going to be automated we already have set that up   it's going to be from L2 all the way through M3 so  L2 through M3 that's right here L2 through M3 now   where do we want those results to go I want those  results to go here from P2 all the way through T2   P2 through U2 or T2 in this case how do we do  that we just simply update that P2 and also I   use Auto hotkey that automates this code for me  it helps it's free software okay and we'll just   make this T2 okay so now what I want to do is  I want to determine what's the last row of our   results so the last row of our results we're not  going to base it on column A we're going to base   it on column P because that's the required field  so what is the last row here P99 that's going to   be the last row of our results when they come in  what if the last results row are less than three   then we want to exit the sub out so if our last  results are less than three we're going to exit   the sub now actually we can probably not exit  the sub let's go let's just move this down here   so right I don't want to make B2 true until we we  have data so that's fine so we're going to move   that down there okay that means B2 doesn't go to  True until we actually make changes that's fine   now that we have data I want to actually create  that formula so if we notice here this is our   total payments formula our total payments form is  located up here it's basically our total invoice   minus the total balance and we know how many total  payments they've made so knowing that all we need   to do is just bring down this formula all the  data now that we know the last row in this case   it's six I'm going to bring this formula down in  one line of code it's stored in S1 so to do that   we're going to do dot range S3 which is our first  line through S and our last result row dot formula   equals dot range S1 S1 formula and this is our  total payments formula okay so now that we got   our total payments formula located in here I also  want to bring over the data so we've got all the   data here so it's going to be in our payments so  let's take a look here so I'm ready to bring over   the data starting from P all the way through T  and I'm going to bring it directly over inside   our payments from E which is our invoice date all  the way through I so let's bring that data over   using this payments.range E11 which is our first  column 11 our first row all the way through I and   our last result row now notice that our first row  here starts in row 11 but our data here starts in   row three so we need to compensate our results  start in row three so we need to compensate for   that Difference by adding a plus eight dot value  equals or is our resulted range it's located in P3   all the way through T through T and the last  result row dot value okay that's going to bring   over all the data okay we're going to set B to  false that is going to be it I'm going to just   clear the spaces out we're going to check it  and so when do we want this to happen I want   to load payments when the user makes a change so  I'm going to save our results when do we want to   happen we want that to happen on change of F3  but only when the user makes a change first of   all I want to make sure this is not blank when  they make a change to F3 and I also want to make   sure that the payment row is empty so that's going  to be a change event on the sheet so we're going   to go back into the payments here I'm going to  focus on the change event so that's here if we   look under change event so we make a change event  now what kind of change event so first of all I   want to make sure that there's a value in here so  let's take a look at this put some notes Here on   customer change but not on payment load now when  we're loading an existing payment on payment load   and only let's put an only me on New payments  okay so if not intersection so what which one   we're going to make the change of course that  change is going to be to F3 and we want some   conditions on that so the conditions are and range  B2 must be false that's of course the payment load   and what I also want to make sure that B4 equals  FD range B4 which is our row dot value equals   empty and the reason is that is that's going to  be a new transaction then what then we're going   to do something then I want to Simply load in  the payment options all right so we don't need   the F in because it's just a single line right if  we're doing more actions then we can add more in   here we don't need to end if all we need to do is  just attach paste in that particular macro here so   that's all we're going to be doing again saving  our work so that's all we need to do we'll check   for anything else so we're going to make sure  again B4 is empty and we want to make sure that   of course actually one more thing I want to make  sure that F3 is not empty so let's do range so   that means we want to range F3 dot value does not  equal empty okay so that means there's actually a   customer right if they clear it out nothing's  going to happen Okay so let's go in down here   and double clicking here and we're going to load  it okay let's take a look so it cleared it all out   which is what I want now we see invoice date we've  got the invoice number here I'm gonna go ahead and   send that here go to home and Center that we've  got invoice amount we've got previous payments   we've got balance good I like that so now when  I make a selection change here we see that we've   applied the balance okay good I like that but what  I want is I want to know that total plot how many   are being applied well basically I'm going to do  a sum if I want not just sum everything in here   but I want to make sure that it's checked now we  could do probably could do equal sum that's fine   we could just simply sum a column we can also sum  it by check marks but almost generally it's always   the same what I mean is are we only checking those  that are checked and we can do that too so there's   two ways we can do it we can simply sum the column  which is relatively easy all we can do is sum if   so if we do sum if I want to base it only on those  checks home so what is the range that range that   we're going to be focusing on is the plot here  so that means only those checks are going to   be applied so that's an easy way to skip it so now  what is the criteria it's basically character 252.   only then are we going to sum it so that's another  way to do that so that means when we unselect   something automatically gets unselected of course  it gets cleared out automatically but our total   plot also goes so that's another way of doing  it I'm going to italicize that I'm going to left   justify that so this so now we have the total plot  which is nice and this way we can do that okay so   this is looking really good now I like the way  that that looks we're going to save our work so   our first macro is done we're loading the customer  in and we're only loading it onto now what I'm   going to do is we need to save this payment I want  to save it I want to make those changes and save   everything ingly so how are we going to do  that okay so the first thing what I want to do is   I want to make sure that we understand has it been  saved before or not so in other words payment row   if there's a row we know it's been saved before  there's no row it has has not yet been saved so   that's going to be our differentiator and I'm  just going to make adjustment I think I'm going   to put the column I also want to know if it's  save where's that row that it's located in what   do I mean by that notice these payment items have  already been saved here there's a row associated   with that I want to place that row directly inside  here and I want it hidden here so actually I'm   going to clear out instead of C I'm going to clear  out K I'm going to use it I'm just going to make   that adjustment here inside our macros I want to  clear out let's say d right and K so basically   I want to put that in here so when we clear it  out we're going to use previous data I want to   put the columns Associated and that way I know if  there's a value here this has already been saved   and there's a database row inside the payment  items that's associated with them so let's go   ahead and write the macro that we're going to save  and now when I save it we need to make sure that   there's a payment date I need to make sure that  there's a payment amount that there's a customer   payment type and notes may not be required and I  also need to make sure that the payment amount is   equal to the total applied if there's a difference  we need to let the user know so that's the next   macro we're going to write called payments save  and update we're using the same macro for both   the saving new ones and updating existing ones  so the first thing we want to do is check to   make sure that we have some value so check for  those required Fields we're going to focus on   with payments and I always like to add a DOT to  make sure I got the sheet right intellisense pops   up I'm going to check for required Fields making  sure that those values so if F3 j3 or J5 are empty   then we need to let the user know again F3 j3 or  J5 if those are empty letting you know they need   to make sure that there's values here so if dot  rangef3 dot value equals empty or so we're going   to put the or now we can just copy this and  change it to j3 and J5 so copy that then do   something else so F3 j3 or J5 here we need to let  the user know to make sure those fields around we   can do that with a message box please make sure to  add in a customer we need to have a a payment date   and payment amount before saving okay we're going  to exit the sub if they don't have those required   Fields we're going to exit next up I want to check  to make sure check to make sure payment amount   equals applied amount and so our payment amount is  located in J5 our applied amount is located right   here inside j9 so I must make sure that J5 and  j9 is equal if dot range J5 dot value does not   equal dot range j9 value then let the user know  what the message but it says box please make sure   payment amount is equal to applied amount and then  we're going to exit the sub next up now we know   everything's accurate ready to save it now what I  want to do is I want to know if we have a new one   if we're dealing with a new payment or an existing  payment so we can do that here how do we know that   it's going to be based on V for if dot range B4  dot value B4 is the row equals empty you can do   empty or double quotes either one will work then  it's a new payment else and if it's an existing   payment so else it's an existing payment there's  some things we want to do for the new payment if   you've seen my videos before you're familiar  with this that repetition's certainly going to   help you and make these a lot faster so if it's  a new payment we need to determine the payment ID   the next payment ID is located here I'm going to  place that directly in B3 I also want to take that   a payment ID and I'll put it directly inside the  first available row located in column A so that's   exactly what we are going to do right here okay  so the first thing the first row we'll call this   payroll that's a long variable is equal to payment  list the first available row so it's going to be   ranged Excel so that's going to be our first  available route plus one that is our first row   that's available call it first available row okay  so once we have that I want to take that payment   ID that brand new payment ID from B5 and put it  directly into B3 value equals dot range B5 that   is our call it next payment ID I also want to take  that next payment idea I want to put it directly   inside our payment list and the associated pay  row so and pay row dot value equals whatever's   in B3 so that's going to be also that pay ID and  I'm going to place that directly in here okay so   that's it if it's an existing all we need to do is  just take whatever's in B4 and that is going to be   our payment row it's going to be located there so  existing we're going to do pay row equals B4 very   good so now everything else is going to be used  just for regardless if it's a new or an existing   now notice I've got some data mapping here  basically I've mapped payday to j3 customer to F3   pay typed F5 J5 so using this I can then basically  loop from column 2 to what is the last column here   you see column f is column six so from two to six  or simply going to map that data there so we can   do that for the call for pay columns equal  to two because we already have column one two in   this case six okay and then next a call okay so  inside that column what I'm going to do is simply   map the data so the payment list dot cells we're  using cells because both the row and the column   are Dynamic here that pay row comma pay column dot  value is equal to what it's equal to dot range Now   where's that Range located it's located in row one  so payment list dot cells Row one based on the pay   column dot value right here this is the range  like j3 F3 and so on and so forth dot values so   this is going to be save form data so we're just  going to save it in that so that's it so one Loop   that's data mapping that's going to cover all that  so that's all we have to save or update the data   okay once we have that now what we need to do is  save or update those pay items so all we've done   up to so far let's go ahead and put some notes  in here so now we're ready to save the pay item   so save pay items so all we've done here is pretty  much save any data located up here so these Fields   right here and I've made sure that we're saving  them into this payment list here either updating   the existing or saving them so now what I want to  do is I want to go through the payment items so   that means I want to save all these payment items  to another database and the reason is because we   have one to many that's why we have two different  databases one for this and one for this and the   reason is because we don't know how many pay  items so there's unlimited we can't have unlimited   columns in this so we create a secondary database  we make sure that they're tied by the payment ID   that is why we create this payment ID because I  need to know how many items are associated with   payment id1 so that means I know that two items  are associated with pay ID one just like that and   that's creates a one payment list to many payment  items one too many we use two different databases   to do that and so what I want to do is I want to  determine the last row this case 15 I'm going to   Loop through them from 11 through 15. I'm going  to look to see if it's been previously saved that   database row will be located here in column K if  it has not been saved what I'm going to do then   and is I'm going to save it to the first available  row here I'm going to put in the payment ID I'm   going to put in the invoice customer payday  payment on and then the row associated with   this now the pay ID in the row those are only for  brand new items so we can add those into new items   so the first thing we need to do is determine the  last row so the last pay item row now we have that   variable up here called last pay item row this  is the one that we're going to use so that last   payment I'm just going to be based on column e  that's the date come last pay item okay so now   that we have that we're going to then move forward  we can simply run a loop for the pay item row   equals 11 to the last pay item row and we're going  to close our Loop next pay item row okay inside   this this is where we're going to check the first  thing what I want to do is I want to check to see   if it's been applied right I really only want to  focus on those particular payments that have been   applied with that check mark so the first thing I  want to do is only those items with a check mark   so to check to see if column D equals if dot  range what is it called d d and the pay item   row dot value equals character 252 only then are  we going to move forwar