Track Your Invoices Efficiently with Our Invoice Tracker Template for Management

Invoice tracker template for Management

hello this is randy with excel for freelancers  and welcome to the account payable manager and   this week i'm going to show you how to create  this incredible accounts payable manager   complete with hover menu bills  and payments where you can create   unlimited bills and payments that track along  with a fantastic bill schedule in which you can   display the bills by bill date due date or  pay date on just a change it's going to be   an incredible training i've got so much  to share with you so let's get started all right thanks so much for joining us i've got a  really packed training for you this week bills and   payments along with the accounts payable manager  tracking all of your bills for any type of company   or personal use we're going to be able to track  it all add receipts of course a full on dashboard   which you just saw scheduling so we can see those  bills on the schedule a very very cool hover menu   vendors a complete customizable admin screen and  a whole lot more you will not want to miss this   training i've got so much to share with you this  week i hope you do appreciate that of course i do   create these trainings each and every tuesday this  template is absolutely free all you need to do is   click on the links down below and either with your  facebook messenger or along with email we will get   that sent over right away to you however if you  do like to support us there are some incredible   ways to do that in fact if you like accounting and  you want to ever create your own entire accounting   application and also learn how you can create  these applications to create your own financial   independence with reoccurring income i show you  how to do that in my mentorship course full-on   mentorship course it is a 12-phase program where  i'm going to show you every part of defining   designing developing and deploying your own  excel-based applications for passive income   so i hope you'll join us in the mentorship program  i'll include the links down below we've got some   really cool promotions going on with that all  right let's get started right on this training   because i've got a ton to show you don't forget  to smash that like button subscribe and click the   notification icon bell i'll make sure that this  training gets ranked high up and i appreciate that   also don't forget to comment i reply to each and  every comment okay so what do we have here well   i've got this really cool hover menu right how do  we display hover is it's one of those few things   in excel that allow us to hover over the bill  and have something happen on hover so i'm going   to show you how to do that we create this hover  menu and builds and payments right when we add   in bills and payments we've got a specific vendor  we may want to search for individual bills we can   search by vendor bill id status reference so if we  want to search for a status and we want to search   only closed we can do that and just click and  it'll show you the three closed jobs we can   clear it out so we'll be able to select and load  a specific build we're also going to be able to   display the receipt here if we see we can have  different receipts displayed very very easily   saving and adding new bills printing them deleting  them will be able to save and update the vendor   on that we'll attach different bills or we can  clear a bill out okay so we've got that that's   going to be able to add unlimited bills so  very very cool we'll be able to add a status   to each individual bill a reference how much  the bill amount if it was paid or not so if   we decide we were going to market paid we just  market paid let's go or unpaid partially paid   might be partially paid or notice how the status  change to partially paid or when it's completely   paid we can also show that right completely  paid without do when we save that that's going   to become fully paid now automatically  so it's going to reflect on the schedule   on that schedule we want to do some additional  settings so we want to have some customizations   right we want to have certain icons appear for  our on our schedule and we want to have certain   colors we also want to set some payment terms  maybe certain vendors on our bills and payments   maybe we want to have a do net 30. so we want that  due date automatically changed we're going to have   a paid date we can set up a pay date what date  we've paid it and a payment type so we've got a   lot of different views on that a lot of different  updates simply saving the bill is going to update   that inside the database right so we're going to  be able to do that inside the admin screen we're   going to have a set uh scheduling weekday and  we're going to be able to also set some dynamic   payment types we're going to have attachments  right we know that inside the builds we may want   to attach a screenshot of the attachment right  so you'll see some of these have attachments   like this so if we want to attach bill all we need  to do is just select this and attach any type of   bill so that's called we have to set a folder for  that so we're going to set a default folder here   and we have an icon folder because on our  scheduling screen right here we've got icons these   icons denote whether they've been paid or whether  they're open or whether they're closed right so   we can have different colors and different icons  if we have bill date we can set the display so i   want to know all the bills when was the bill date  or maybe i want to show when their due date that   might be kind of important when do they do i've  not got some due dates so we're going to have that   i've also got a list of basic list of vendors  which we can show and update and of course the   admin screen so this is a five part schedule along  with the hover menu so we've got a lot to cover   in this training right we're going to start off  and of course of course completed by a dashboard   where we're going to show you some timelines  we're going to show you some of course slicers   and we've got a whole lot more to show you in that  so some really really cool effects that we have on   our dashboard so it's a patch training if i move  too fast i'll try to keep it slow you can also   slow down this recording of the video you can slow  down the playing of it as i record it in a certain   speed but youtube allows that and most other  players allow you to slow down the speed if i   tend to move a little quickly because we do have  a lot to cover so grab your drink of beverage   and we'll get started right away the first thing  is this very very cool hover menu and i have the   same one on each screen so if we go to the bills  and payments you see that we have a vertical hover   menu so as i bring my cursor over here that we  see the hover right here and we can hover over   it so how do we do that well the best way to do  that these are individual shapes right so there's   no when i click a shape there's no way to create  some kind of a macro on hover however we do have   some types of shapes inside our developer if we  look in the developer right and you see insert   we have some active x controls now these generally  don't all work on all macs but they do on pcs so   an active x control allows us to for us to program  something to happen when we have a hover over one   of these shapes so if i were to place these shapes  over these other icons we could actually make   something to happen so what i mean by so let's  just take a look at this label right if i create   a label here we've got a label here we have a  label here we can assign something to happen   uh when the user hovers over that now nothing's  going to happen let's go let's go ahead and pin   this down here so we can see nothing's going to  happen alma is if we're in design mode okay keep   that in mind if i click now that we're in design  mode when i hover over these nothing's going to   happen okay keep that in mind as soon as i exit  out of design mode everything becomes live okay   so while we're in design mode it is what we want  to do so what i want to do is basically create   a macro i want something to happen when i hover  over this okay so if we double click on here it's   going to take us into vba under the bills and  payments right here and we also see that we have   label one which is the one i just created and we  have the action click well when we click it we   want some we can have something happen but in this  case i really want to focus on when we uh move our   mouse over the shape and that's called mouse move  mouse move so it is this here we can clear that   out now it is this here that something's going  to happen so if i do message box and then test   as soon as i exit out of the design mode it's  going to become active so nothing's going to   happen as i hover over it as soon as i exit out of  design mode you're going to see as soon as i hover   over we're going to get that message box test okay  so keep that in mind so we can perform any action   and so what we want to do is but we really don't  want to look we really we really don't want this   to look like this so what we can do is go into the  properties here and we can then clear that let's   bring in the properties over so you can see them  and i'll bring them down a little bit here so you   can see them okay so we've got some properties but  what we don't want to do is we don't want the user   to see that right it's not it's not very pleasant  so all we need to do is just make it transparent   so if we have a border style we've got none which  is what we want and we have the back style if we   click here and we go transparent we're not going  to see that and we remove the label we have to   give it a name that's critical but we don't have  to give it a caption if i clear out this caption   here then we have basically an invisible box right  so again same thing getting out of design mode   as soon as i as soon as i hover over that we're  going to get this test here all right great so we   see how we can perform actions on that now what  if i were to create different ones some of them   that when we hover over and some of them  make them disappear so what do i mean by that   if i close that here let's close out of this one  and go back into design mode here and keep the   properties open and now what you'll see if i if i  click on here we see we've got some bars here here   here and here and here so so i've got five  different horizontal these are all of course text   boxes these are all as we saw here in the indies  insert right here we saw we've got different i've   got all the label they're activex label controls  activex label controls there's no border and   there's no filled right but so they're completely  invisible however when we hovered over them we   want something to happen so if i double click this  here or any one of these let's go ahead and and   show that double clicking it and what that's going  to do is going to see hide menu so i've got one   hide menu one two one through five for each one  of those horizontal bars i've got a five okay so   what that's going to do is run a macro called menu  hide and that macro is basically going to hide   all of our shapes so let's take a look at some of  the shapes we're going to go out of design mode   and what we're going to do is we see we've got to  shape it this is called menu 1. and if we hover   over this one this is called menu 2. so we get you  get the idea of what they're all there's five year   menus and they all have the same written and this  last one's called menu five so what i want to do   is when somebody hovers over one of these these  bars here these invisible bars you can see it   there if i click on it if when somebody hovers  over that i want to hide all the menus however   that's great but how do i get them to show up  well i've got some other ones some other activex   controls and there if i click on the design mode  here you can i will be able to zoom in and let's   go ahead and select one right a little bit hard to  find but if you ever want to know how to find them   these are the icon shapes right so that's the  icon that's not it but they're hidden right but   but how can you find them well all we need to do  is go into the selection here and look them up so   we're going to use our selection tool we can  close out the properties now and we go we see   we have one that's called hover five hover one  number three two and one okay so that's how we're   going to get them once notice we can't select them  but as soon as we go into design mode keep in mind   design mode is how everything happens so now when  we select hover one here it is here is our hover   this is our activation control so it's right  here under hover one so here it is right here   this is our label shape so that's it we can move  it anywhere we want and so here it is this is the   shape what's this called this is called hover one  and we have hover two which there so basically   this is a label without the border without filling  and then there's a macro that's assigned to the   hover of that so when we go back into the sheet  bills and payments here we see we have now these   shapes here all the way down here called hover  three hover four hover five hover two and over   one so it's these specific shapes and now for  each one of these i'm going to do something for   example on hover one what is hover one hover one  is right here if i select it we can see keep in   mind that you cannot select it unless we're out of  designer as soon as i exit out of design mode that   selection goes away design mode is critical for  us so this is called hover one i want something   to happen when we hover over that what do i want  to happen well i want to take this shape and i   want to display it let's go ahead and i want to  take this shape and i want to display it menu one   so that's just what we're going to do inside here  so as soon as we on hover one as soon as we mouse   move over it we want something to happen and what  that is is basically width we're going to focus on   that menu one shape and i want to change the menu  if i want to give it a little bit of a slide out   look it's very fast i almost can't see it for the  menu width we're going to start it off at 33 which   is just like a square basically and we're going  to go to 133 and we're going to step point five   right so we can step it up and then what we're  going to do is we're going to adjust the width of   that width is going to grow until it becomes full  and that's just what we happen to happen as we go   over it so when i slide over here you can see it  goes but when i slide back right it disappears   why is it disappearing because we're sliding  over this bar so the first thing we do is hide   it so that's it that's all we have to do and the  slide out is very quick you might want to make it   a little bit slower you can adjust this this step  up if you want or you can slow it down a little   bit but i kept fast i kind of wanted it fast okay  so that's how we do so we hover over one to show   it and then over this bar where it's going to  hide them all then again the same thing here   here showing it bar doing it so this of course is  called hover 2. the same thing here go back into   the design mode clicking on hover 2 we could see  that hover 2 and hover 2 is the same except this   time we are focusing on menu item number 2 and  we're increasing the whip so that is all we have   to do then all i did was duplicate this code here  this code all the way from the hovers in the menus   everything like here all the way from so we have  five for the menus and we have five different ones   for the basically hiding the menu then all  we have to do is simply copy and paste that   on each of the sheets that we want to so we  have six different sheets we've got the admin   we've got the bills we have the the dashboard  we have the schedule and then that's it so   so we have that so that's all we need to do  five sheets actually so that's all we need to   do to create this very very cool hover effect  keep in mind that we're using of course labels   activex labels other shapes a button would work a  few other ones would work but we're going to use   labels because the other ones have hover effects  too but labels is the easiest one so that's what   we're going to that allows us to run a macro on  hover keep in mind that if something doesn't work   it's probably because you're you're inside or  outside of design mode right if you can't select   something it's because you're not in design mode  if it's not working if the hover is not working   it's because you are in design mode so keep that  in mind this can be a little bit confusing so keep   that in mind all right so all i did was just  basically duplicate that on every single zip   and then what i did is i also assigned a macro  to the button right i assigned a macro here   so that macro is also if we select on this and we  click assign macro we see that we have something   called menu icon go to sheet so i have a specific  macro that will allow us to go to the sheet   now we notice that the name of this is called  dashboard we notice that the name of this is   called bills and payments the name of this is  called build schedule right so if we assign a   specific name and that name is exactly the  same name as these sheet names down here   right build schedule then all we need to do is  just run a little bit of a macro that allows us   to select any shape and go to the page based on  the name of the button right it goes to that page   all right great so how do we do that well of  course that's with a macro if we right click okay   and click assign macro we see that it's called  menu go to sheet okay we edit that it's going   to take us to a module called application macros  application macros very very simple some things   first of all we have menu hide this is the macro  that we saw recently remember when we look in here   and we saw hide menus remember those horizontal  bars that's called menu hide so how do we get them   to hide well back inside the module here it's very  easy we're going to set the menu number is long   we're going to run the menu number from one  through five because we have five different menus   and if you remember those menu shapes there  they're named very specific menu one menu two   menu three menu four and menu five okay so if  we've named them all consistently like that we   can do that with just the blue just in case  there's an error we've wrapped it in on our   resume next and on our go to zero and basically  we're going to say use the active sheet because   we want this action to perform regardless  of the sheet we've got five different sheets   that we're going to be putting it on so we want  to make sure it runs on any sheet that it has   so shapes menu and menu number one through five  the width just goes back to 33. basically we   could hide it or unhide it but all we're doing is  setting the width back to 33 and what that's going   to do is going to basically hide it under this  bar so it's going to be hidden under this so if   we move it over we see that they're all there but  hidden right so if they all if we run that macro   they all get reduced you see they're all under  there but they're just simply under that bar   and they're hidden there so as soon as we as  soon as we highlight over they are sliding   out very very quickly very very easily like that  okay so it is that macro that gets tied to these   height shapes and we're going to just simply run  this loop and then hide all the shapes ingly   but it's actually just getting reduced in width  and then they're simply hidden behind that bar   okay great then you saw another macro where we're  automatically when we actually select on one of   those shapes those icons that we're actually  going to there's that test again we can remove   that going back into design mode right we want to  find it so we're going to select all the area and   we want to know where it is so if you don't know  where it is just use this or of course we can use   the last one label one that's going to be up here  if we can't find it label one make sure you're   in design mode selecting on label one that's  gonna select it right there and then we find it   and then all we just need to do is just delete  and it's gonna be gone that was the test one   so when we assign particular macros two buttons  and we name them very specifically we can then   automatically go to that sheet with a macro so it  is that macro that we're going to go to now so we   can see here the macro of course is called go to  sheet and we edit that it's going to take us right   back to the application macro this one here menu  go to sheet right that's the one the first thing   what i want to do is i want to run the macro the  one above that we're going to hide all the menus   the next step we're going to take the application  column this is the name of the button that called   it and the name of the button is exactly that so  sheets application caller activate that's all we   need to do calling out that sheet it's going to  activate that very very simple so all we need to   do is click on the individual icons and it's going  to go directly to whatever sheets the name of it   just make sure that we have the name of our shape  the name of those icons is exactly the name of the   sheet whether it's dashboard or whether it's bill  schedule exactly as it and that's it that's how we   create a really really cool very very simple  hover menu we just need to hover over it and   as we move down they all get hidden or shown or  hidden and shown just like that and clicking on   them is going to allow us to go to that individual  sheet all right great i'm glad i got to show that   part to you it's really really important because  we've got a lot to cover next up what i want to   do is i want to focus on this area this is the  bills and payments right let's reduce that we   don't need it back to 100 percent and we want to  be able to create unlimited bills and then also   show them as paid or mark them as paid as they  come up and of course be able to attach a document   for that so when we assign particular macros  to buttons and we name them very specifically   we can then automatically go to that sheet with a  macro so it is that macro that we're going to go   to now so we can see here the macro of course it's  called go to sheet and we edit that it's going to   take us right back to the application macros this  one here menu go to sheet right that's the one   the first thing what i want to do is i want to run  the macro the one above that we're going to hide   all the menus the next step we're going to take  the application column this is the name of the   button that called it and the name of the button  is exactly that so sheets application caller   activate that's all we need to do calling out that  sheet it's going to activate that very very simple   so all we need to do is click on the individual  icons and it's going to go directly to whatever   sheets the name of it just make sure that we have  the name of our shape the name of those icons   is exactly the name of the sheet whether  it's dashboard or whether it's build schedule   exactly as it and that's it that's how we create  a really really cool very very simple hover menu   we just need to hover over it and as we move down  they all get hidden or shown or hidden and shown   just like that and clicking on them is going to  allow us to go to that individual sheet all right   great i'm glad i got to show that part to you it's  really really important because we've got a lot to   cover next up what i want to do is i want to focus  on this area this is the bills and payments right   let's reduce that we don't need it back back to a  hundred percent and we want to be able to create   unlimited bills and then also show them as paid or  mark them as paid as they come up and of course be   able to attach receipts or bills just with a click  we can also attach bill so we're gonna go over   to that now how do we get that in there how do we  create it how do we create this really cool filter   and how do we get them to display all right so  that's what we're going to get to well of course   everything you see here is stored inside our  builds database which is located right here   and it's basically a table we've got the id  the vendor bill date status receipt name all   the fields associated with that including the  paid amount payment type notes and a formula   associated with the amount due and the amount due  is simply the bill amount minus the amount paid   so that's where everything's going to be stored  we have a unique bill id so back inside the bill   payment screen along we've got some hidden columns  as you may have seen i want to know if we're going   to include the paid or clone this particular  true or false is going to be helpful for when we   want to search bills right when we include we can  include or exclude paid and close so if we decide   we're going to exclude paid and close we're only  going to show the open bills here otherwise we can   select on this it's going to show all the bills so  that's the true or false option and that's going   to be located in b2 i want to know the selected  bill id that's going to be based on the selected   if i select a different bill i want that bill  id to change i also want to know the row that's   associated with that bill that is the database  row that associated with that and it's located   here so that means that bill id number three is  located in row number six as you can see right   here and in order to get that we need to create  some named ranges some dynamic name branches that   will grow as our data grows so if we look in the  formulas and name manager and we see that we have   bill id we're going to use the offset formula  starting in the header a3 and then offsetting   one row below because we don't want to include the  header we're going to count all the values from 3   all the way to the last possible row we're going  to subtract 1 because we don't want to include the   header row that's going to create a dynamic named  range and we're including the header row because   we don't want any errors in case there is no data  so including the header row is important for that   case so along with that we also have a few others  while we're here we have bill amount due i've got   a name range for the amount due i've got build  the entire it's going to encompass the entire data   that build due date we're not going to use the  data in this training so we're going to use the   bill due date i want to know one for the due dates  i also want to know one for the vendor name okay   and also i think that's it for the bill so as far  as the bill in this table that's it that's all   we're going to be focusing on here so if i want to  extract that row i can use the match so back into   that i want to extract that raw i want to know  that it is on row 6. to do that we're going to use   the match formula we're going to match whatever is  based in b3 which is the id we're going to run a   match based on the named range bill id we're going  to add 3 because we want the actual row number   our first result row 1 that we want is going to be  on row 4 so we want to make sure to add 3 meaning   our first one is on row 4 bill id one is on row  four i don't wanna return one i wanna return the   row number so we're adding three if you've been  with me for a while you know this the next bill   id i also wanna know the next available one we're  gonna use the max formula based on all the bill   ids as long as our bill ids are numerical we  can extract the max and then basically i want   to add one to that that is going to let us know  our next available id if there's no data at all   and there's could create an error and therefore  we want to set the default to sit bill id number   one and we can use that on if air so that's why  if there's an error that means there's no data   and therefore we're going to set it to one i also  want to know the selected bill rule we've got some   conditional formatting here and i want to make  sure that that format shows so the selected road   10 or 9 i want that displayed right here located  in b6 if we highlight these and we go into the   home and then we go into conditional formatting  we're going to find that we have three different   conditional formattings the first is based on  that selected row where we're going to get it that   dark green white font in bold then we're going to  color some rows based on making sure that there's   a value in e7 and then we're going to give it  that white color for even rows and for odd rows   we're going to give it that color green that's  how we're going to get this alternating row look   this professional look great so next up i want to  know if it's going to be loading or not right when   we load it that's going to go to true and back to  false i also want to know the vendor row what is   the vendor row well we have a list of vendors here  i want to know the row that's associated 4 5 or 6   right so to do that we of course we need the named  range so we look in the formulas back in here and   now we have something called vendor id and vendor  name right so if i'm going to be looking based on   a vendor name we have the name range based on  that vendor name and we have one for vendor id   because for example i want to make sure that we're  using a vendor that's on our list so if i create   a new bill and i select a vendor from our drop  down list of vendor names that you saw this is   a data validation based on our vendor name so we  look under data and we see that we see that that's   based on the vendor names that named range we just  saw so what i want to do is i want to know what   role i want to make sure it's the correct vendor  so to do that what i'm going to do is i'm going to   run a match based on the vendor row that vendor  row is based on a match this is our next vendor   id we'll get to that next i want to know the row  that is associated with that vendor we're running   a match based on what is in j4 and i want to know  the vendor name right so the vendor name so that's   all we have to do based on the say we can change  the name the row wants in because this is where   we're changing the name in j if we j5 however  we're simply going to look up the one in j4   we're going to add three of course because our  vendors also start on row four and that is it   that's all we have to do to make sure that we  here's our vendor row starting on row four okay   i think we need a name and title here don't we yes  we do let's go ahead and copy this group here this   is our name and i'm going to put that over into  the vendor list at least we have something there   we go we know what we're dealing with all right  so we've got a list of vendors here and we know   we can extract a row directly from here to make  sure that we have a correct vendor and i also want   to know the next vendor idea if we want to add a  new vendor id we want to be able to do that so we   new bill and we can add a new vendor just simply  by saving this vendor saving it so if i want to   add in frida fred's wife freda fretters and then i  want to do give an address one two three four five   six that's the long address main street i  want to be able to save this vendor right so   all i need to do is just click save vendor and  we know the next vendor id that we've got a   fade out that's gonna say vendor save so we  know it's been saved and then we'll see back   in the vendor list that that's now been saved we  now have frida freder's here okay great so we're   saving the vendor so we can save the vendors  we can also save new bills we can create brand   new bills we can re-enter different vendors and  we can also enter a bill date we've got a pop   up calendar shape based pop-up calendar we can set  the terms based on the bill date which is going to   automate the due date we can set the pay date if  we want we can set the payment type if we make   a payment type a reference number if we like  we can add a bill amount how much the amount   that's important and then also the paid amount and  then we can of course as mentioned we can attach   a receipt if we want okay great and adding  some notes so all we need to do then is just   add some notes save that bill it's going to  save that bill it's going to automatically   add that bill to the list and we can display  that okay great so how do we do all of that   well that's going to come in a module so let's  go over the module that's the one i'm going to   focus on is the bill macros module so that's the  one we want to focus on and of course we've got   some variables here we'll be going over those  as we'd have the first one i want to show you is   this list load remember we had some filters here  and we'll be able to load that list dynamically   we're able to search on status right so if we want  to search for status or maybe we want to search   for a specific vendor maybe we only want to show  lisa here so we only want to show lisa perkinson   clearing that out or maybe we want to search by  notes right if we want to search only by notes   i want to search anywhere by notes it's going to  show only those of course bills that have tests   in the notes so we have a dynamic search filter  and we're able to clear the filter along with   of course adding only those bills without that so  we've got a lot of different filters here so we   can do a whole lot with that all right so how we  going to perform this well the first of all this   is of course based on an advanced filter we want  to filter these records dynamically we're going   to we also want to have that criteria what are we  searching by bill id vendor status reference or   any of those so how are we going to do that well  the best way to do that of course like i mentioned   with advanced filter and it's going to be based  on our original database located in the bills   database here and we're going to set some criteria  now we have more than one criteria possibly why   is that because we're going to be filtering based  on certain status if we uncheck this we are going   to exclude anything that's paid and we're going to  execute anything that's closed and that's going to   be based on our admin screen so if we look on our  admin screen we're going to be excluding anything   that's either here paid in full right fully  paid or closed so those are the ones we want to   exclude there so how we going to do that well  that's going to be in our bills database so   here was here's our criteria right here we have a  vendor of course it's not empty and but maybe we   want to search for a specific vendor so back into  the bills and payment if i want to search for only   nancy right and i only want to include so that's  going to be only nancy and only those that are   not paid and not closed so that's going to  be set some criteria so let's take a look   inside of radius now we have some criteria here we  have a vendor and it's nancy smith both the same   and we have does not equal fully paid and does not  equal close so that is our criteria that we are   setting up this time and it is that criteria and  we can link that so all we need to do is simply   link it with the original data however we notice  that also our criteria type is also dynamic this   is linked to bills and payments f4 meaning  it's dynamic sometimes we're searching for id   maybe we're searching for notes so on and so forth  so when we link this cell to f4 whatever we put in   f4 is automatically going to be set so back into  the bills and payments if i clear it right and i   show let's say bill id or anything like that and  i show paid and close here with now we're going   to be searching for a different bill id so if i'm  going to search only for a bill id let's say bill   number one i only want to locate that bill based  on the id and we're going to include any paid or   any closer any status so now we take a look now  it says bill id now we're only for showing and of   course status does not equal empty that's it okay  so this is dynamic our criteria header is dynamic   based on whatever the user enters we're going to  put the same value in here as long as it doesn't   equal enter search why is that important because  when i clear this the default text is called enter   search right here i want to make sure that i  exclude anything that says enter search because   i don't want that part that's default and for the  user's purposes and we don't want to be searching   based on that so we want to make sure inside this  if the f5 equals enter search then just put in   nothing otherwise bill and payments we're going  to show what's in there and we're going to do   that the same exact formula for both of these this  however is a little bit different this status is   if the user has selected this is going to be based  on our option this option right here include pager   code remember when that is checked this is true  when it is unchecked this is false so we want   to create a formula based on that true or false  and to do that the best way to do that is just   create an if then so back in the bit if bills and  payments equals true which it does then what we   want to do is put does not equal otherwise if it's  false meaning we want to exclude then we're going   to say does not equal and whatever's in the admin  c13 which is fully paid and the same thing for the   one lower than that except this time we're going  to add in admin and settings c14 which is the   closed status so that means we want to show all  of our let's say bills this should say job bills   not just conversion bills okay bills database so  we want to show all of our bills except for those   that have been fully paid and except for those  that are closed so we want to show that and what   that's going to do is get us those results those  results going to come here and then we're simply   going to bring those all those results including  the bill id and we're going to bring them directly   inside the bill and payments now you say well all  those results i don't see the bill id the bill id   is actually right here if we see this one two  three look in the look in the function bar here   you see that right so how do we do that well what  i did is simply format there's two ways to format   that we can of course color the cell if i drop  this down here and we show that we can also give   it a font color or we can give it a custom font  so we've did we've done that right here so if i   were to change the font color to red you would see  that it would show up but if i give it the same   font color as the background here it's going to be  hidden another way to do that if we just another   way to do it is going to be using our general here  we are custom number formats is to use custom here   and then we can use three different semicolons  one two three that also will make it disappear   so we have two different choices which we can  use all right great so but i really want that   idea i want that because i want to make sure that  we going to take whatever's id is in here inside   column d in the selected row when i load it and i  want to put that id in this case 2. i want to put   it directly inside b3 so that's going to do it  so i want to bring in bill numbers the due date   the vendor and the amount due so that's where our  results are going to come directly in here bill   id due date vendor and amount due and bringing  them all into here and we can do that with the   macro of course so it is that macro that is tied  to a few different things that macro is tied to   two different things so if we take a look inside  the bills and payments the macro is tied to this   button here so when we assign the macro we see  it is that same macro that builds load list   that macro is also tied to a change event here f5  and that means when i enter something like 1 or 2   i want that to load up i want to run that macro so  it's also tied to f5 so if we take a look inside   here our bills here bills and payments here and  we look up here on worksheet change we're focused   on worksheet change when we make a change to f5  then we're going to run the macro bills load list   that's the macro that we're going to go over right  now because of the first macro inside this module   first thing what i want to do is  i want to clear any selected row   and that select row is going to be located notice  that we have conditional formatting based on that   and then that's going to be right here inside of  b6 so b6 is going to look so i want to clear that   out when i clear it i'm going to clear that and i  also want to clear out any of the data all the way   from d all the way through g so d through g is  going to do that so excuse me let's make that d   d through g yeah i like that better d through g  because i want to clear out those numbers all the   way clearing those contents out i want to get the  last row of the original data and that that data   of course based on bill's database we're going to  run an advanced filter based on that last row if   of course it's less than three we're going to exit  the sub right it should be less than four right   because our headers are on of course row three  all right so what i want to do is i want to run an   advanced filter now that advanced filter is going  to be based on the original data from columns a3   all the way to n so it's all the way to n based on  the row the header row 3 that criteria is going to   be p2 through q4 and the results are going to come  directly into from r to u so that's just what we   have here p2 through q that's where our criteria  range and we're going to copy it to r through u   then what i want to do is i want to determine the  last row based on column r so this last row is   of course 22. so i want to extract that and we're  going to do that inside this variable the last row   based on column r and we're going to get the last  row if it's less than 3 we can exit the sub out   however then if it's not we're going to simply  going to bring it over in a value to value change   so we're taking all the values from r u3 through  our last row and just bringing it over into column   d through g that's going to bring everything  over great so also we notice we have the clear   filter now remember when we clear the filter  all we need to do is make a change to f5 that's   going to trigger it if that changes of course  enter search what's that going to do that is   automatically going to clear out our criteria why  is that because inside our builds database notice   here if it's enter search we're just going  to put does not equal if it's enter search   does not equal so all we need to do is simply  take that the inter search when i clear the filter   let's do that let's add something in here so we  can put something different then when i clear the   filter all i need to do is put inter-searcher it's  going to automatically re-run that advanced filter   and of course there's going to be no criteria and  therefore all the results are going to show up   okay great so we've got that and that's how clear  filter is a single line of code it is that macro   that's assigned to the button how about if i want  to save and update the bill right it is the same   macro where we're going to be both saving and  updating the bill and within that save macro   certainly i want to make sure that we have all  the required fields they need to make sure that   we actually have a vendor i need to make sure we  have a bill date i want to make sure there's a   status of course we need to have a bill amount and  due date those are all critical so the first thing   we're going to do is check on those required  fields and so the best way to do that i want   to make sure that we actually have a vendor right  vendor's located in j5 if there's nothing there if   it's empty we need to let the user know to add  a vendor before saving the bill i also want to   make sure that there's required fields now there's  multiple required fields as i just mentioned bill   date status and a few other things so we can check  each one but another way to do that is simply   count the number of cells that are required and  make sure so the required fields i have four   required fields so we can use the formula count  a 10 j10 j12 l12 and l11 so that's bill date the   status we have the due date and we have the bill  amount those four are required so that means when   we use count a if this is anything other than four  for example if we decide we're going to delete   the status right and i try to save this we see  that this is now let's go ahead and delete that   here if i delete that we see that the required  field is now three if i try to save that bill it's   gonna let us know please make sure to add in bill  date due date status and bill amount before saving   one of those is blank so that's going to be  a problem right so to do that we want to make   sure that if this number is anything other than 4  and less than 4 then we need to let the user know   that they have not filled in the required fields  so it's a little bit easier to code for that   so here we do if l if b11 is less than 4 then  then let them know with the message box and exit   the sub up i also want to make sure that they  actually haven't paid more than of course the   bill amount if they've paid them 500 and the bill  amount is 450 and i try to save that bill i want   to let the user know to make sure the amount due  is not less than zero right so so we want to make   sure that that doesn't happen okay so to do that  we just ensure a little we just double check that   l 14 which is our amount due is not less than of  course zero so we just check that if l14 is equal   to zero or i should say less than zero all right  if equal to zero l14 is less than zero that's what   i want less than zero then so if l14 is less than  zero then please make sure the amount due is zero   perfect but however what if it is zero if it is  zero i want to make sure that this automatically   gets marked as fully paid okay so the best way  to do that is just to check so if l14 equals   zero then all we're gonna mark fully paid on zero  balance and where is that fully paid that fully   paid is located inside our admin right here under  c13 so we want to see 13 is where we want to do it   so that's exactly l10 becomes c13 all right great  so now all we need to do we've set all that up to   marcus fully paid we could actually add a partial  pay but i left it empty for now okay so what do   i want to do when we're saving those bills we  need to do whether we're updating an existing   bill or saving a new bill i need to differentiate  between those two and we know that because a new   bill is going to have a empty row no row has been  assigned to that right no id has been assigned   and therefore no row is calculated however if it's  an existing bill we see that b4 contains a row so   b4 is going to let us know whether it's existing  so if b4 equals empty we know it's a new bill   else it's existing so if it's a new bill i want to  add a new database row based on the bills database   i also want to set the next bill id that next  bill at least using the max formula based on bills   right here inside b5 so we're going to do that  right here and then what i want to do is i want to   set that bill id that brand new bill id and column  a and then also what i want to do is i want to set   in i don't often use formulas but in this case it  was kind of important meaning i do it was also a   simple formula just a subtraction i want to set  that amount due formula based on both on the bill   amount and the amount paid setting that formula in  column n we only need to set it once and we only   need to set it for of course new bills so we can  do that right here bills database and then also   we have the n in the bill row simply equal to  i and the bill row minus k in the bill row and   basically that is the amount due formula which  is simply the bill amount minus the paid amount   this these four lines of code are only four new  bills however for existing bills we ain't gonna   set the build row based on b4 then all we need  to do is run data mapping data mapping meaning   we have these particular cells up here in row  one these cells addresses pertain to the cell   addresses located in build payments so we see  that j5 is our vendor name we see that j10 is our   bill date so when we look down here and we see our  bill date here let's scroll over to the left here   our build date is located at j10 our vendor is j5  so if i map these fields all i need to do is loop   from the first column or actually in this case  the second column because we've already set the id   starting with the vendor name all the way to  the last column and then just add in the data   and that's what we've done here 2 to 13 all  13 columns right i'm going to take whatever is   in that row one whatever cell is in that row one  and i want to extract whatever value that is and i   want to put it directly inside the bill row and  the bill column that's it that's data mapping   we're going to reload the list if we've added a  new one or maybe we've changed the name i want   to reload the list and then what i want to do is i  want to select set the selected row i want to know   that selected row for the conditional formatting  and to do that we just need to set it up and i   actually need to make change i need to change  this to d right why is that well what i'm going   to do is i'm going to look for b3 so notice it  wasn't selected so now let's go ahead and go into   the bill payments here and we're going to save  that bill and we see that we now have a selected   row we see that bill id 2 has been selected if we  select 3 and because when we save that bell i want   to make sure that the same row is selected here so  what i'm going to do is i'm going to look for that   id in call in column d if the id is found here and  then i know that to load it up okay so the id here   is 5. so i'm going to look in column d here i'm  going to look to see if it's found i'm going to   find that if it's found then i know to simply make  sure that 10 is the selected bill row and place   that build row inside b6 so how do we do that  well that whatever's in b3 this is our bill id   so i'm going to look for that i'm going to look  for that in column d if it's found using the find   i want to extract that row and i want to place  that row directly inside b6 if it's not found it   could create an error and therefore we're going to  wrap it in on-air resume next and on-air go to 0.   that's how we quickly quickly set the highlighted  row and why is that important because if we change   anything it's automatically going to be selected  and then i'm just going to run that bill saved   message that is the fade out message that we  use as soon as it's saved if we scroll over here   and we look to the right a little bit we see that  we're going to have that bill saved that basically   fade out message it's going to take that shape  and slowly fade it out it's a little bit slow   but it'll work okay so that's the message right  here it's just delayed i'll probably make that   200. we're just going to run that delay and then  that delay is going to slowly fade out that button   through this macro great so now what i want to do  is i want to add a picture remember adding that   receipt picture that's important attaching the  bill and we can attach it right here so if it's   carl's jr or whatever it is we can add that  receipt here so how are we going to do that we   want to display well the first thing is that macro  of course is assigned to this attached bill so if   we right click assign macro browse build browse  picture we edit that we're going to send right   back to the macro we just were first thing what i  want to do is i want to make sure that all those   receipts get saved in a specific folder that's  going to help us keep us organized and we're   going to set that folder based on what's in the  admin screen so i want that folder i'm going to   set a bill attachment folder it's going to be in  something called sample bills in this folder here   so that is also a name range called bill received  folder inside our admin screen so wherever we find   them they're going to copy those bills i'm going  to put them all directly inside this folder and   to do that all we need to do is just copy it over  so first thing what i want to do is make sure that   we actually have a correct folder if it's empty  or the file path is not correct then let the user   know to please select a folder for all bills in  fact and in fact what i can do is assign a macro   so we have a macro that is here called browse for  build folder picture so this is the macro that's   going to run when we actually click this button  inside the addon screen this button here is going   to browse for that folder okay so it is this macro  that i'm going to ask them to run when they have   someone close this out that they need to bear so  i'm going to return so this is the macro run macro   to browse for folder in case the folder is not  existing or it's incorrect in case that path is   incorrect right and then we can exit the sub we  don't need to move on they can run this again   and it'll work just fine but if it is an accurate  folder we're going to set that into a named range   a string named branch called build folder try not  to make sure that your named ranges here are not   the same as your variable it can sometimes create  issues that is the bill picture folder where we're   going to put in a picture of those bills also what  i want to do is i want to then browse for it okay   so we're going to set the build picture based on  the file picker right file picker we're going to   give it a title please select the bill or receipt  picture we're going to give it some filters right   in this case only jpegs png just you can add  additional for pdfs but not in this training   but we're just going to focus on pictures allowing  multi-select no if it's not if they don't pick   something we need to if they cancel that out we  need to go to no selection just skip everything   else if they don't cancel what i'm going to do  is i'm going to check are they browsing in the   current folder in other words let's say we browse  in the folder that where the we're getting it from   what i mean by that is let's say we're pulling it  from the same folder that we want to put it into   there's no need to copy it over to this folder if  that's where we're browsing from we don't need to   move it we don't need to copy it we don't need  to do anything we just keep it right where it is   so what i want to check is this file path this the  same exact the same place where we want to put it   so this line of code is going to do that and that  basically means if the selected escape pattern   there if the selected item that means the full  file path of whatever item they've selected is   exactly the same with the file path of the build  folder and the directory of the selectedness then   this is the name of the file if it's exactly the  same place we don't need to move it we don't need   to copy it anywhere else so we can simply skip the  copy go to skip copy and it's going to go right   down here also what i want okay so let's say it's  not in the folder let's say we want to browse for   from a different place and we do want to put it  in the folder but what if that file name already   exists in the folder in that case what i want  to do is i want to delete that file so we can do   that here if the directory of the bill folder and  the direct director the select items vb directory   does not equal empty what this means is the file  already exists in the same name and the same so i   want to remove it right i can't replace one file  to another if they have the exact same name so we   need to kill the current one meaning delete it  if it already exists once we do delete it if it   exists we can then move on okay so now all we're  going to do is copy it from its current location   into its new location we can use file copy for  that from its current location which is here   dot selected items into its new location build  folder and the director the sim select items   that's going to copy the picture into the  folder copy picture into the that's called   the bill folder right the bill folder and that's  all we have to do relatively simple build folder   okay good so once we have that all we need to do  is i want to take the file name of that and i want   to put it somewhere i want to save it right if we  take a look at our build payments build database   we see that we have a reference let's take a look  at this this particular name of the file here this   is the name of the file rent whatever it is sample  bill 2 sample 3. whatever we have the name of the   file i want to put it inside that database and  it's going to be located inside b10 and meaning   when we go back into the build payments b10 is  going to take on the name of that that's the one   so here this carl's jr receipt is located in b10 i  want to place the name of that file so that's the   last thing b10 is simply equal to the directory of  the select item so it's going to extract the name   from the full file path and it's going to place  the name directly in place file name and that   way when as soon as we save it it's automatically  going to save it to the database and then we're   going to run a macro that's going to show that  [ __ ] a separate macro that's actually going   to show that build so that macro that is going  to then display that directly in here and it's   automatically going to be centered based on both  vertically and horizontally based on those cells   so we've got a macro that's going to do just that  we want to center it vertically between rows 12   and 19 and we also want to center it horizontally  based on column n so we want to put equal distance   spacing both on the left and right and the top  and bottom and we want to insert it based on this   and we can do that because now we know where  it's located because we know it's located   directly inside our build received folder and we  also know the name of it because it's the name is   located right here in b10 so when we combine the  name along with the folder we get a full file an   accurate path we can then insert it into that  and we do that with just the next macro that   we're going to go over so it is the show build  picture here okay we also ha