Collaborate on Excel Invoice Template with Database for Customer Service with Ease Using airSlate SignNow

Excel invoice template with database for Customer Service

hello this is Randy with Excel for freelancers  and in this week's training we're gonna build an   amazing customer manager and it's can combine all  the customer information invoices work orders and   contacts into a single screen we're gonna build  this live right in front of you every formula   every line of code you're gonna see everything I  can't wait so let's get started all right thanks   so much for joining me today I really appreciate  it we bring you these videos free each and every   Tuesday so if you have not subscribed yet now is  the best time to do that and make sure you click   on the notifications bell so that way you're  gonna be alerted when we get brand new videos   each and every Tuesday I appreciate you this time  we're going to be building a customer manager this   customer manager is going to be based on lots  of customer information we're gonna be able to   add and update contacts we're gonna be able  to combine work orders and invoicing so you   get an idea of just how we can create a customer  manager hopefully within an hour we'll see how   long it takes I'm not sure but I'm gonna try to  do my best to get this to you quickly but I want   to make sure to get it to you completely so we're  gonna go through every step I've done a few things   just to make things a little bit quicker I've got  a customer list you can see I've got some buttons   on here so today I'm just gonna be bringing over  the buttons that might save us a little time we   create buttons so often so the least I can do  just to save a bit of time is we're gonna copy   over some of these buttons so we have a list  of customers we have a list of contacts that's   gonna help us move things forward we have a list  of invoices just some invoice data and we have a   list of work orders and I'm gonna show you how  do we bring all of that combine all that into a   customer manager now this training is going to  be unique because what we're gonna do is we're   gonna show you how to create a many-to-one and  what do I mean by many do one we have a single   customer but we have many contacts per customer  we have many work orders per customer and we have   many invoices per customers so how do we create a  single record on display but have them many-to-one   many of those files to one and I'm going to show  you just how we do that with separate database   of separate sheets so that's what we have here  so let's get started right away on this and but   before I do I want to make sure if you have not  joined yet our mentorship is on fire so now is   the time to get in we're gonna building an entire  accounting application and that includes invoicing   items inventory full accounting purchase orders a  full dashboard share in sync so you can share this   application my goal is to get you completely free  of the freelance trap not enough money at the end   of the month I want it show you how you can create  your own applications and sell them for passive   income that's what the mentorship is all about  to get you completely independent and financially   free taking those excel skills and using them and  I don't want to get you there my goal is to get   you there so the mentorship program is all about  that so I hope you'll join us there I'll include   the links down below alright let's get started on  this week's project first two columns what we're   going to do let's go all the way to say Row 31  I'm gonna call this this is gonna be far admin   so we're gonna color this gray I want to make  sure we'll hide these columns eventually I've   just set some rows up some sizes so to make it a  little bit quicker for the development so we're   gonna call this customer manager and then I'm  gonna bring this all the way over to let's say   L and then merge the center that and then I'll  give it a color also I want to make sure let's   go all the way to are on this all the way to our  because we've got a lot to cover on this and then   I'll give it a just a unique color format those  cells we won't spend too much time on working   on the format and then a fill effects I'm going to  give it this medium blue and then lighter blue and   this is basically all I did was just switch themes  so that we can get different colors feel free to   switch themes on your own applications as well and  I'll just transition this to a little bit lighter   color so that we can get a nice fade effect again  what's the fill effect going from the medium and   then a little bit to the lighter okay that gives  us an idea and I'm gonna format this top let's   give it I like the Arial rounded it's kind of  a nice clear font so let's go with that and   then up to maybe just so enough big enough okay  that's good and also what we'll do is we'll just   color all the fields here and then we'll make them  white as we go down so we'll go down to about 31   and then give it that blue code that's gonna give  us that fade effect that we really want so let's   get up so the first thing what I want to do is I  want to make sure that we have a selected customer   sewing in I want the user to be able to select a  specific customer from a drop-down list so select   customer and then I'll have this field props it'll  be a little bit bigger so let's use two fields   merge the center align the left and color it white  and okay so that's gonna select the customer will   right justify that I can merge and center this  one as well and then right justified get okay so   basically I want to have fields let's say call it  name and then I want to have skip and go type I'm   gonna have a customer type also want to have a  status and an address so we can have a customer   address we'll use a long full name address and  then CD down here and then what else do we want   perhaps in column H I want additional fields I  want number like office number and then maybe   another mobile number so we can keep track of two  different numbers per customer and we're gonna   also have different contacts too so each customer  can have its multiple contexts I can't wait to   show that to you so email and then a website and  then we'll round this out with I want a one I want   this whole line I want the address to be this long  so let's merge the center this and left justify   then we'll have city here I'll put state here  and then I'm gonna put zip code here okay that   rounds it out for our fields and then we just need  actually we need a notes field so let's put notes   down here I'm gonna create notes up here and then  I'll put the larger field so let's go with 13 to   16 here and then all the way across so I'm gonna  merge and center that and then left justify upper   and then color that white okay so now what we can  do is we can take our fields we have our customer   name here but I want to merge the center this I  want to make a larger field merge the center again   selecting him and then we'll do City down here  office I guess we can color that white selecting   them I got a misspelled zip so I'm gonna fix that  okay so now but what else I want to format those   so and then I want to put a border around it I'm  gonna use the same thing color but just a little   bit darker I'm gonna go with that blue giving that  the route all the way around except I'll change   the left one to the dotted line okay and let's  fix that and then I'll write justify these I'll   write justify these I can hold down the control  right justify these here and so that way we get   all of our fields in and then there we go so  they're right justified now now address is going   to be much longer color that white it's getting a  together so now we can see what our forms coming   about now let's put borders around the rest of our  labels here so you can see how that would work and   then right justify format the cells and let's  go ahead and put the border around there we need   to increase that this border color here but this  time we're going to go all the way on the dotted   line on the right side and then solid on the top  left and bottom and then actually I'm gonna make   these a little bit longer email and both website  would be longer field so let's merge the send of   those left justify right click and reset those  just to reset those then we're gonna fill that   fills out our form pretty well okay so that gives  us a good idea of what we want now what I want to   do is I want to put in voice workers in contacts  also on this screen I'm trying to fit it in all in   so that you can see everything here so I want to  put something like invoices here we can capitalize   that sure and then on this one I want to put work  borders and then context I want to have a contact   list I want to put like something up here maybe  so I call this customer contacts customer contacts   and it's gonna be in double row so down here I  want to put let's say the first first name and   then last name because we're gonna have multiple  ones you're gonna be able to select between those   contacts so I'll have a table below phone one and  then phone two and then what we'll do is we'll put   an email right here okay so we have our customer  contacts here but I want to select from a table   down below so basically what I want to do is I  want to have a space for the buttons a little   space with buttons and then here I want to put  contact lists so a list of all the contacts per   customer I'll call it contact list and then firt  will put a first name and then a last name so and   then starting it probably sixteen will go all the  way down to let's say twenty nine okay so let's   highlight all them down to twenty nine and then  work orders I'm going to start actually probably   an eye through K and then invoices we're going  to go from D through G so they all go down to   29 so I'm gonna format these tables I'm gonna  I'm scrolling down you can see it's a little   bit off the screen and I'm going to format put  the same border around that we just use all the   way around and then I'll put a dotted line just  in there so we can see that and then I'm gonna   give it a fill of white so we can see everything  okay that's really what I want pretty good but   I do want to put some headers here and headers  here and I want to match those headers here so   basically format those cells and then I'm gonna  go just something like the medium so go down to   the fill effects use the same theme the same theme  go this this medium to this lighter and then we're   gonna color the header above a little bit darker  okay and then this work orders I'm gonna put this   I'm gonna drag this here because I want the work  orders and so what we'll do is we'll merge the   center this merge and center this merge and center  this all the way across we'll drop this down merge   and center all those fields and then right click  I'm gonna format those cells just going down to   format fill but this is going to be a fill effects  we're gonna change just a little bit darker medium   to medium light that gives us the same theme as  the rest of it and this is not consistent so this   capitalize this as as it is with the rest of  them alright good I like that and now we just   need to set up the border so we can see it come  together already format listen we'll just go with   the same color and then border all the way around  with that using that same consistent color inside   and outside bring it up all right it looks good  let's put some information on here for theirs   I want to keep track of the invoice date and  probably the invoice number because we have all   that information the status of the invoice and  the amount next up on the work order I want the   work order date and also on the work order number  keep track of that just like we do the invoice and   then also the status of the left okay so we have  everything nice and let's just Center everything   here ingly and then what we're going to do  is I also want to create some fields for the name   last name the phone the phone - and the email so  I'm going to right-click does format those again   just same as we did fill of white border of the  same blue color that we're using and also we're   going to set all the way around except the dotted  line is going to be on the left and I'm going to   do just the opposite for this for these fields  here holding down the control I'm going to format   the cells we want to put that border all the way  around except this thick border on the top left   and the bottom and then I want to alignment I'm  going to right justify that right with indent   that's okay and then we go let's take a look at  that that looks really good that's pretty much   what I want let's add some fade out to this merge  and center this so it's consistent with everything   else format those cells give it that same fill  that we have been doing everywhere else fill   effects see how quickly this can come together if  we have a good plan it's basically on the design   and I want to see you create these two in fact  you can tie these into specific invoices if you   want and specific work orders and we'll do that  with context but if you have if you combine my   invoicing training my work order training you  can create an incredible CRM application just   with the training that I've given you let's put a  box all the way around here so it ties everything   in together and then put a border around here  and then we can bring in our buttons you'll see   it's a little bit quicker since we're okay so we  have the idea I want the contacts to come in here   but I want to put some conditional formatting so  that makes it look a little bit nice alternating   row conditional formatting with the mod formula so  what we're gonna do is to go into home conditional   formatting manage rules I'm gonna create a new  rule using a formula and I'm gonna use mod 2   equals mod I already have this automated but mod  Row 2 equals zero this is gonna be four even rows   equals zero that's gonna get us and we'll set a  fill format that means every even row I want to   use something like this color but not that dark  so I'm gonna go into more colors and just go a   little bit lighter on that that's good and then  click OK all right so that's gonna color area now   it's really nice it's coming together here we need  to set the same formats for here so let's format   those cells put the border all the way around  it border all the way around it click OK and   then I want to right click just on just so we are  consistent with our theme and then the dotted line   on the right okay so looks like we have everything  I'm gonna put some buttons up here I'm gonna put   some buttons here and then let's format this  cells again with the same border just so where   everything is set consistently good so we've built  that out about 15 minutes we've built out this   whole design now we have it so let's bring in the  buttons and then I'm gonna show you how to create   this and put an icon here so again I created some  previously so just a I'm gonna use the selection   tool and I'm gonna click all them select all of  them copy ctrl seat and we can unselect it now   and then the custom manager I'm gonna paste it  now I'm just gonna drag them all the way up here   because I kind of got them positioned a little bit  and we can see just how we would work like right   about here that's kind of good okay I'm gonna  bring this icon over here alright so well here's   what I have let's take a look at the buttons  that I previously created and we know how to   create buttons if not these are just basic shapes  you can check any one of my videos if you want to   know how to create these because I did so much ok  so I have a button set called existing customer   group what is that that's a group of buttons add  customer update customer and delete customer and   I also have another group of buttons and this  one's called new customer group two button sets   each for different purposes when a new customer  if I click Add customer I want only these two   buttons only want these two options available  for the user so what I'm gonna do is I'm gonna   hide this group of buttons and I'm gonna display  this group of buttons so when they're entering a   new customer they have two choices only two they  could save the customer or they can cancel the new   those are the only two options that I really want  to give them otherwise if they cancel new what's   gonna happen I'm gonna want to select an existing  customer any existing customer I'm gonna hide this   button set and then I want to show this button  set so and I want these on top of each other   because only one of them is gonna show at the same  time so that way they can be on top of each other   because you'll only see one at the same time  all right what are we have here in the contact   let's take a look at this I have ability to save  contact add a contact and delete a contact okay   so the idea is this when user selects a contact  from one of the Roasters I want that contact to   display here when they click Add contact I want  all this to clear out and I want them to enter   contact information and when they click add  content I want this Add button to disappear I   want this delete button to disappear and I only  want to show this save contact so that's pretty   end-of-course when they select any other contact  that will automatically go away so that's I want   to list all the contacts here I'm gonna list all  the workers and all the invoices here so how do   we do that let's set up some formulas so we can  make all of that happen of course when they select   a customer from the drop-down list here I want  all that customer information to fill out here   I want all the invoices all the work orders all  the contacts to fit her so again let's go over   this many to one many invoices many work orders  many contacts for one customer that's what our   focus is on this training many-to-one because  it's something that you'll use quite a bit when   you're creating your own applications alright  let's get to it let's start creating some of   the more important information that we have that  is going to be our hidden section we want to know   when a customer's gonna load that's gonna help us  so load customer I need to know if that's true or   false so let's just set it to false now we'll  put that there and I want to know the customer   ID of course each customer has their own ID so  it's very important that we tried the customer   ID next up I need to know the customer road what  is the customer bro the row of whatever customer   what row that would be this row row okay we can  delete these now you don't need those buttons   anymore the row five row for whatever the customer  row is based on the customer ID I'm gonna create   two named ranges I'll do that right now while I'm  in here formulas named range we're gonna create a   dynamic named range first one's gonna be called  customer ID we don't need a space there customer   ID and what does that customer ID it's gonna be  based on the offset formula so equals offset why   are we using offset reason offset because as our  list grows so does our named range so I'm gonna   include the header in this because I want to  make sure if there's no data there's no air so   we can include the header but we need to skip one  row down so we're gonna start one row down then   comma comma and then what I want to do is I want  to count all the names in the rain so count a is   gonna do that for us what do I want to count all  my count pretty much everything up to nine nine   nine nine nine nine whatever okay so really count  all those but since we're including the header we   do need to subtract one we include the header why  again because when there's no data at all in this   list it could prevent an error if we don't include  the header so we include the header there is no   error so minus one is gonna do that and then comma  one that's the column so when we tab out of it   tap back into it we look at the dancing ants and  we see that they're completely covering all the   data so we know it's right so good I'm gonna copy  this formula right here I'm gonna click okay then   I create one more I'm gonna call it customer name  customer name but in this case whatever reason I'm   gonna paste it in here except all I'm gonna do is  I'm gonna change a to b because now we're counting   the customers b and a customer name is a required  field so that's okay we can count as long as it's   required it is required so no problem so again  we tab over and we see the customer name those   are the only two named ranges that we're gonna  create customer name customer ID okay good so now   that we've created those we can go back into the  customer manager we can continue to work on our   fields so what else do we need other than customer  oh I need to know if it's a new customer customer   new it's gonna be true or false leads call this  false for now and I also need to know what the   next idea is that's important next ID what would  be the next idea how do we figure that out well we   can use in Max and then we can wrap that inside  and if air equals let's type it up here it's a   little bit easier equals if if air max what do we  what's the max the customer ID that's the one we   just created it's not just the max I want to add  one because I want to know the maximum of all the   IDS make sure those IDs are numerical if they're  text or there's any text in them this formula   will work okay so plus one but what if there's  an error if there's an Erik's one defaulted to   one why would there be an error would be in here  if there's no data so that way just gonna default   the first customer ID to one and as you can see  our next customer ID is 24 and if we look at our   customers we see the last ones 23 so the next  one will be 24 that's gonna keep track so what   I'm gonna do is every new customer I'm going to  take this new ID I'm gonna place it right here   in b2 that's gonna set us up for the customer ID  okay next up what else do I want to add I want   to add in a search customer so this customer ID is  gonna be based on this let's merge and center this   I forgot to do that immersion center and the left  let's check the other fields make sure they're all   merged in center I want to merge and center DS  all here holding the control and I think this   one - we should merge the son of those and left  justify them make it sure because when we clear   the contents and good we're set up everything is  right okay so what we want to do basically is when   we have a new customer I want to search customer  I want to know the customer row based on what they   selected here that's really important and I also  want to know the customer role based on the ID so   it's kind of two different searches so we want  to know the real customer row one time based on   whatever they select here and one time when they  selected based on this ID here so we'll show you   that I'll work through that with you so but big  keep in mind I want customer row there so let's   call this the search customer and then I'll put  that in here and then of course I'm gonna put that   what else am I gonna do I also want to know the  selected contact I don't want another slide I want   to know what they select if there's a contact here  and I select it I want to know what road is I'm   gonna use conditional formatting to highlight it  it's gonna be really cool okay so what next I also   want to know if the contact is loaded in contact  load what is the contact load me when they select   something here it's gonna take whatever contact  information it's gonna put it up here it's gonna   be really nice and if they make the change it's  gonna be saved very very convenient let's color   all this so we know what what we're looking at  here we'll put some borders around it here just   to signify that this is important for our purposes  okay so let's put some formulas in what is the   customer row well if that customer ID is one we  know that the customer row must be four right so   we can use the customer ID and use that using an  if error and then we'll use the match so equals   let's type it up here if they're always like e  affair because I don't want heirs when we run VBA   code match what are we matching we're gonna look  up the customer ID what are we looking up we're   looking up this customer ID right here and what  is the array customer ID and then we want an exact   match now remember we need to add three because we  want row four right but if we don't add three it's   just gonna return one I don't want one I want the  row so that's gonna be plus three what if there's   in here empty all right that's gonna get us four  and that's just what we want row four is what we   want what else okay so let's take a look at the  selected contact let's add we have data validation   here we could add that in right now so when we  go into data data validation and it can be a   list and remember the name range we created equals  customer name perfect so the idea is when I select   something I want that customer information to load  perfect we're almost ready we'll move this over a   little bit to close for that and of course when  the customer ID changes let's say 2 3 it's gonna   change the row we don't have it to actually okay  so let's go I also want another selected contact   so let's just say it's 20 I want to put some  conditional formatting this gonna be true or false   so I want to put some conditional formatting so we  select the customer it's automatically highlighted   so let's highlight the rows we're gonna go into  home and then conditional formatting and new rule   and we're going to use a for most can be a very  basic formula equals this which is b12 equals row   and then in parentheses and what is the color  that I want to give it I want to give it just   a contrasting color based on the same theme  so we're gonna go into the fill effects and   then I'll give it this blue and then a darker  blue and then of course we need up a font that   is contrasting which would be a bold and white  that's going to give us a contracting so what are   we gonna contrast so what we're going to do is  we're going to let's just make sure the font is   correct yes that's what I want so we're gonna do  is have as we add VBA code we select a contact the   line gets highlighted and then the contact loads  here so excellent that's the way I want let's make   these bold here controlled beef to make them bold  to stand it out it looks good what's next up now   we got a now we're almost ready to write our macro  so what's the first thing I want to do I want to   be able to load the customer when I select the  customer I want that customer to load notice how   when we change this we also want to row we want to  put that customer row so to do that we need to get   the row in here equals if air match right what are  we matching we're matching this customer name and   we're basing it on the customer name named range  that we created comma zero that's going to create   it but what if there's an error if there's no I  wanted to blank so five so good so now all I need   to do is as we change a customer I need to look at  seven and then what I'll do is I'll go into row 7   and then I'm gonna add load all this information  in and we can use data mapping what's data mapping   if you're not familiar with that data mapping  is the ability to map specific cells with the   data in the file and I've added them here to make  it a little bit quicker but let me show you what   that might be in case you're not familiar with  it just yet I'm gonna go home I'm gonna take a   screenshot simply by using my software Snagit and  I'm gonna take a screenshot of this and then I'm   gonna copy that and then what I'm gonna do from my  software and then what I'm gonna do is I'm gonna   go into the customer file and I'm gonna paste it  just temporarily now we can get a good look at   the data and we have this makes it a lot faster  so what I want to do I've reserved the top row   for data mapping so if I want to if I know the  customer name is in e4 and I know the customer   name is stored here I'm gonna put efore here our  customer ID if you remember is located in b2 our   office number here is located in i4 so I'm gonna  put I 4 here so this helps us quickly having this   screen shot here helps us quickly map all the  data so I've done that with every single field   all right so then now we can delete that so you  get the idea every single field is mapped based on   the cell here so then all I'd want to do in VBA is  simply run a code what kind of code am I gonna run   I'm gonna run a code from column one or possibly  column 2 depending upon whether it's load or   save if it's save we just need to use column two  because a will already be there but if it's load   we're gonna load all the information so what do we  do so then I'm gonna run a loop from a all the way   to call on M what is column in equals column it's  call 13 so we're gonna run a loop from either 1 to   13 or 2 to 13 we're gonna loop through these and  if I'm loading it I'm gonna take this information   whatever we know the row whatever is in let's  say row 7 column 2 and put it in a for whatever   is in row 7 column 3 and put it in I 4 of course  it's I 4 on this sheet and so we're gonna do that   with every we're gonna run that loop and that's  how we do it loads so let's program that inside   the VBA and we'll go into the VBA code inside the  developers if you don't have this developer sheet   open of course you can get it through file options  and then just click the customize ribbon and make   sure a developer is selected you can use this  shortcut alt f11 we'll get you there and I've just   created four modules to make these go a little bit  quicker there's nothing here except some titles of   some to give us some guidance there's some macro  name so I've done that try to make a little things   a little bit quicker and still show you every  step so I want to make sure to do that what   are we doing we're gonna load the customer so we  have customer load here and that's the code that   I want to write that's the macro that right all  that so let's do that right now let's load let's   create the macro for that so we need to dimension  some things dimension only to the customer row as   long as long I also want to know the last invoice  row why do we need to know the last invoice row   because I'm gonna run an advanced filter I'm gonna  determine what invoices are only for this customer   and then I'm we're gonna need that so we need  the last invoice Road as long we also need to   know the last work order row as long - so we need  to know all that and of course what I'm gonna do   is I'm going to do don't last actually what I'm  gonna do is I'm going to load the context in a   separate macro and I'll tell you why a little  bit later on but I'm not gonna put this inside   I'm gonna use a separate Macker and then I'm gonna  call it and I'll show you why it's shortly alright   and also I still need to know also the customer  column as long a number I'm going to loop through   all those columns so that's probably need to  know that ok so that's good so now we have to   mentioned all the parameters that we need we're  gonna be focused on with sheet one sheet one is   our customer managers sheet so that's what we're  gonna focus on if we're going to load we need to   make sure remember if I as we change the customer  here as we change the customer we want to load it   right so it's gonna be always based on this bro we  need this road remember this searches our customer   here so if there's an incorrect value here if  there's an incorrect value it's not gonna let   us but it's this is going to be empty so let's  say if we remove this we need to make sure that   b10 is not empty if it is we can't load anything  so that's the first check we're going to do inside   our code so let's write that right now if doc  range d-10 value equals empty then what do we   want to do message box let the user know please  select a correct customer name from the list okay   and then exit sub we we can't do anything else  unless we have an accurate customer assuming that   we do have an accurate customer now we can assign  that customer so the customer row is equal to be   ten valued so we can just copy that back it up  copy that here and then use it down here as low   so we can set the customer all automatically to  be ten I also want to make sure that we clear some   fields and I want to clear all the fields now I  created this before so let me just type in so what   I want to do is dot range I want to clear all the  fields in there and dot it's a lot of fields so   I'm gonna stop this video and I'll show you once  I get it copied in what's all about so basically   what I've done here is I've created a lot of  fields basically all of the fields inside this   I want to clear when I load data I want to make  sure everything is clear previously so all these   fields now keep in mind that when you have a merge  cell we need to include all I can't just use efore   when we clear cannot it's gonna create an issue  so if you're having an issue and you're trying   to clear it for it's because it's merged when we  clear efore it must be efore through g4 it must be   e 6 through g6 and of course in this case it would  be e 10 through k 10 so we've clear constants but   when we place a value in e 4 when we place a value  we only need to eat four we don't need to place a   value value equals e four through G four we just  need one cell so keep that in mind when clearing   we need to encompass all the cells when placing a  value we only need the first cell so that's what I   went for through g8 in fact I covered them all and  D 20 this is gonna clear all the fields so all I   do is add those in and use clear contest this will  cover but there's no reason for you to watch me   type even those in these videos are long enough as  it is okay we've defined the customer row already   so now all we need to do is run our loop what  is that loop that's what I spank you before for   customer column equals 1 to 13 remember em there's  the last row close our loop always get the closer   lip the first thing next customer column we've  defined that so what do we do so again let's   just review it real quick basically what I want to  do is I want to say efore is equal to what d4 is   equal to whatever is in B and the contact row but  how do we know it's a neat how do we know II for a   for is up here so all we need to do is write some  code from row one get whatever's in the current   column in this case two and put whatever is in  let's say seven years so how do we do that let   me write that code for you you can see dot range  remember we're focused on sheet 1 here what is the   dot range in this case it is sheet 2 we're basing  we need to get the cell I need to get that cell   where's that cell located so looking at in Row 1  or sheet 2 so sheet 2 dot cells we're using cells   because both the column and the row are dynamic  and close cells what is the row we know the row is   1 remember that's where it is it's always located  in Row 1 and the customer column which is the one   that's going to be auctioning so this right here  is our cell is our cell address whether it's efore   c4 whatever it is so what do we do with that so  now dot value equals what is it getting equal it's   gonna equal based on the sheet 2 dot cells again  our customer row and our customer column that's   the actual roll and you have to a call dot value  that's it that's all of our loop that's gonna load   all of our data all those fields it's gonna load  in just three lines that's called data mapping and   that's gonna insert our data the next thing I want  to do is remember we talked about updating our   shapes right if it's an existing customer we just  loaded it we know that this this one right here   this new customer which is the save and cancel  we know that shouldn't be visible that should   be hidden and we want to make sure that this  button set the existing is display because it's   an existing customer we want to make sure that the  existing customer customer group is displayed so   to do that we can just write two lines of code we  also have button sets here too we need to update   as well so let's do that so dot shapes existing  customer group we want to make sure remember   that's the one since this cuz we want to make sure  that one's visible equals and also true but we   want to make sure the new customer group is hidden  so new customer group dot visible equals and their   so false right that's gonna be false because we  want that hidden and then also what about the   context what about our context I want to make  sure that we our context are said just in case   they had different context I want to make sure  that the same contact that the existing contact   group is also displayed because it's an existing  contact and I'll make sure the save contact button   this one right here save contact button is hidden  because that save it's only when you're adding a   new one so just to double-check those we can do  those two dot shapes and then here save contact   button dot dot visible equals and we're so false  and existing dot shapes the existing contact think   it's group I'll double check that group dot  visible equals and was so true okay let's just   double check those names to make sure we have them  right so save contact button and we have existing   contact grip good so that's what we want so those  are what those hidden okay it looks good let's   take a look here and okay we so soon as we loaded  we wanted to happen let's take this three whitney   ten this should be remember plus three we need to  make sure this is the right row plus three because   our air at is on customer let's take a look at  that here at customer that is located right here   on row six so we want to make sure we add three  just as we just as we did up here and customers   so add three alright let's take a quick look at  that we can load that we still have more to do we   have to load the invoices the work orders in the  context but let's take a look I'm gonna run that   macro take a look okay it looks good let's left  justify this here and take a look state Wyoming   zip we'll left justify that here okay so this  macro is not done and I got some miscellaneous   notes we can format these I'm gonna hold down the  control and go into the more number formats and   we're gonna go into special and then I'll change  this to phone number of course you get formatted   okay save alright it looks good it looks like  everything's up so now what I want to do is I want   to add invoices work orders and then a separate  macro for context so how do we do that okay so the   idea is this let's go into our invoices and what I  want to do is I want to figure out the last row of   the invoice and then I want to run an advanced  filter based on the customer ID putting the   criteria here then the results so for example if  we put in a might be one or customer ID - I want   all of those invoices to come here and then what I  wanna do is I wanna take all those invoices and I   want to bring them here so how do we do that well  the first thing I want to do is I want to make   sure that we have a specific customer ID in fact  let's make sure we put a customer ID I'm gonna run   that Macker just gonna run it here to me I want  to make sure that we have a customer list our   customer ID is one because I've got lots of data  here for a customer ID one okay so what I want to   do is I want to put the customer ID here where  is that ID equals I want to put it right here   customer IDs writer equals this that is our fixed  criteria now it is set our criteria is set so we   can set that it's not gonna change and so what  I want to do is I want to look at this criteria   and I want the results only those invoices with  a customer ID of one I want them to appear here   and when I when they appear here I want to bring  them right back and put them right in here all the   way from aa D 20 through G 29 gonna be the same  exact thing with work orders so again I'm gonna   take the work order again equals customer manager  and then again ID be two same exact thing with   context I've got context let's do that equals the  only difference with context is I'm gonna create a   separate macro for that and I'll show you why okay  so now we have a criteria set for both invoices   work orders and context so let's run let's run  the macro and let's get invoices and work orders   inside that and then we're gonna add the contacts  in so let's write some code to do just that now   that we have the shapes all set let's continue on  with our code okay so let's load the invoices load   invoices so how do we do that well first thing we  need to last invoice row so the last invoice rows   equal to invoices we're on sheet three sheet 3  dot range a and then we'll just go use the large   number 9 9.0 so that's gonna get us our last  embrace oh now we're ready to run our advanced   filter so what is our advanced filter it's going  to be basically sheet 3 dot range what is it   a let's bring this down so we can see everything  just as we're gonna just as we're entering it that   we can see both the code the sheet at the same  time okay so and let's switch it over to sheet   the invoices cuz them what I want to do is I want  to start here a to all the way through II using   the criteria g2 g3 and bringing the results over 2  I 2 through l2 all right so let's write that code   in C 3 range what is it a 2 through e and the last  invoice row and the last invoice row dot advanced   filter Excel copy and we're gonna we're gonna  set the criteria criteria range : equal to what   sheet 3 dot r h g2 through G 3G to through g3 good  now what about the copy to range copy to ranch :   equals again sheet 3 dot range this time we're  gonna be focused on I to through l2 i2 through   l2 good then then all we need is unique equals  false it's fine for this : actually : equals   false ok so now we have it set up now we have our  advanced filter setup let's run that just so we   can see what it looks like and make sure we add  in IA misspelled that ok so good that's what we   want now we have our criteria look all only only  invoices we don't remember we don't need to bring   the customer idea I only need the data so I'm  gonna take now all I need to do is take this data   and bring it right into the customer manager so I  can do that so I can say in this case D 20 through   G 29 equals basically all I can just equal we can  equal all of this whatever whatever however many   rows so for example if we know we have a specific  food we know we have 10 rows here all I need to do   is go into the invoice and go ten rows down here  and just so in this case it would be I 3 through   L 12 so we can do that and we can write that with  a line but I want to make sure that we're gonna   clear usually running advanced filter clears out  the native but let's add a line of code before the   advanced filter just to clear out this data I want  to make sure that we're clearing it out just in   case so before the advanced filter so sheet three  it's a good practice to do that range I three   three through out and then we'll just use a large  number nine nine nine dot clear contents okay   that's going to clear any previous results okay  very good so now we've gonna run that and now what   I need to do is the next hundred orders dot range  I want to bring the data in what do I want to do   what's the range again just as the invoice range  that's we're gonna D twenty is where we spoke of   D 20 through G 29 that is the that is the area  of the invoice you want equals what is it equal   we're gonna just use it we're using ten rows so if  we know we're using nine or ten ten rows in this   case we just need to bring in ten rows from the  results so to do that we can just write in sheet   3 dot range and the results of course start and I  three and then they go over to L twelve so we can   use that all the way to L twelve dot value that's  gonna bring it all the bring in all invoice data   okay so we're good to go we're good to go with  you voice let's run this code take a look back   in our custom manager great now we have all of the  information we can format this as a currency and   then we can Center this that's gonna look nice  Center this as well and alright good now it's   coming together now we just need to do the same  exact thing for the workers relatively simple so   skip a line and call this load work orders almost  the same exact thing so let's switch to the work   order sheet here and then we're gonna start typing  in our code again I need the last in pressure oh   the last work order well last recorder row what is  that going to equal sheet in this case work orders   look at on sheet four dot range will use a again  a nine nine nine dot and X Allah that's gonna get   us a run it's gonna get so last row again I also  want to clear the contents let's just copy this   and adjust the range ingly so we want to end  of course the sheet number two this is gonna be   shooting for and in this case the range is gonna  be H through J so let's update that H through J   perfect and of course we could probably type in  the advanced filter because if I type it in it'll   show you a little bit sheet for dot range what's  the advanced filter in this case again is starting   at a two through D and the last work order Rho  dot advanced filter we're gonna copy to another   area and we're gonna use the criteria range it's  gonna be equal to again sheet 4 dot range f2 to f3   f2 through f3 what's next copy to range copy  to range colon equals what's the range we're   copying to sheet for range in this case it's  h2 through J 2 h2 through J 2 okay and again   unique : equals false it's fine false or true is  okay that's not important for the necessary and   then again we're gonna copy it over then all we  need to do is copy it over in this case what are   we gonna bring it over again dot range where is it  coming from let's take a look at that range real   quick so we know dollar range in this case we're  gonna start at it I 20 all the way over through   teh 29 I 20 through K 29 so let's do that I 20  through K 29 dot value equals sheet 4 dot range got range and then what is the results our results  start of course on h3h3 so let's H 3 through J so   let's do that H 3 through J and if we're starting  on 3 we're going 10 rows we know that we want that   over to j12 J twelve dot value okay that's gonna  bring in bring in all work order data okay good   let's save the code and let's run that I was like  to run it automatically it's run that and take a   look at that now we have our work order dates  perfect I really like that that's looking very   good let's left justify these just for the sake  of being the same okay good so now we have a work   order so now I'm gonna write a separate macro on  our context I'll show you because the reason I'm   writing a separate macros there's two instances  in which I want to load context I want to load   context when I load the customer and I want to  load context when I save or updated content a   customer contact if I add a new and I also want  to slow so there's a few instances where we want   to run the same macro for that reason we're gonna  use a separate macro for this so let's go ahead   and do that it's a few little cleanup things we  need to do on this work on this macro do yet I   also want to make sure that we're gonna set b6 to  false because this is no longer a new customer for   sure b6 must make sure to go to false so dot range  b6 value equals false set new customer to false   good so we've got that covered now let's continue  with our next marker which is the load context and   it's almost gonna be exactly the same as what we  did almost no different except that I just wanted   in a separate macro so that's all so in this case  again with sheet 1 we're still focused on sheet 1   because that's the primary sheet we want dimension  the last contact grow as long and I need to know   of course the contexts are located in cheat 5 so  we can say last contact row equals sheet v dot   range a9 nine dot end Excel up doc bro okay good  that's getting us the last contact bro okay good   so let's clear the contents I want to clear any  results why do we unclear just to be safe so in   our contacts again I'm gonna take this information  and I'm gonna clear it all the way from l3 and Q   and all the way down so sheet five range l3 dot  q : q 9 9 9 9 dot clear contents clear previous   results okay now we're ready to run on advanced  filters so with sheet 5 range a3 is gonna be our   first Road in this case it's a 3 through G and  the last contact Rho dot advanced filter Excel   copy comma criteria range what does that colon  equals on criteria of course is J 2 through J 3   equals sheet v dot range J 2 through J 3 that's  gonna get us our criteria and the next up is our   copy 2-inch copy to range colon equals what are  we copying it over to we're going to copy it over   to l2 through Q 2 qi v dot range l2 through q2  alright good now we've got that advanced filter   and now we're just going to send unique on equals  false okay so now we have our now we have all of   our contact loads and I'm gonna bring the contacts  over here now what I'm gonna do what I'm going to   do is I'm gonna bring all the contacts over and  I'm bringing it all over here right but what I   want to do is I'm gonna hide I'm gonna hide this  right we're bringing it over and then eventually   we'll hide it will give it the same color I really  want all the data here but I don't want it all the   data displayed so I'm gonna bring it all the way  through our but I'm gonna bring it all the way m16   all the way down to our 29 brilliant and then hide  the data eventually so it'll look nice and what is   the reason we want it all in so let's bring that  in dot range m16 through our $29 equals sheet v   dot range l3 through let's put that pull that  up so we know where we're getting it from you   can bring this up now and then the bringing the  contacts here l2 through Q al through Q is where   we have it so l3 through Q and then of course we  want how many rows we're gonna put in 13 rows so Q   16 dot value and that's gonna bring it over that's  gonna copy over the contact copy over context okay   let's run that and I'm just gonna run this macro  here and see where we're at and make sure we're on   the same page here run that okay we got our we  should bring in three contacts now let's check   over in our custom manager good that's just what  I want so then the idea is basically to change   this font to blue and then you don't see that  but that's what it really what I want I'll keep   it before we're finished hopefully if I remember  we're gonna make this blue so we can see what it   looks like on the finished so the idea now is to  select the contact okay so there's one more thing   we have to do I need to take this macro here  load contacts and then to bring it up and I'll   just drop it in here and then I'm gonna paste it  in here and just call it in load context so now   all we need to do is run this one macro here and  still load everything in so when do we want this   throat I want to run this macro when F to change  so we can write that code right now that's going   to be based on when we make a change to the sheet  so that's a worksheet change so if we go into the   customer manager and we go into the worksheet  and the change event not selection change the   change event that we want to write some code in  here what is that code that we want to write if   not intersection that's autohotkey that I used to  automatically type this f2 is nothing and I want   to make sure in there's a few things hand range  and the target we can do range f2 actually we   can use the customer row I want to make sure  this customer rows not blank right so let's   just double check that that customer wrote F b10  cannot be blank right because that's gonna load   our customer and so let's do that b10 dot value  does not equal empty then what do you want to   do is then load actually customer load I'm gonna  run that then what I want to run that Mac or what   macros at this one right here customer load so we  can copy this macro go back into customer manager   and now every time we make a change to f2 assuming  that b10 does not equal hemp T it will load it so   let's take a look at that let's load the next one  perfect that's what I want these buttons are too   close so I'll move them over a little bit now  what I want to do is when I select a contact I   want to make sure that contact information loads  appears how can we do that how can we get all that   information to load up here again we can use the  data mapping for this basically what I want to   know is this column here first name should go into  and for this column should go in two and six and   so on so let's use data mapping and we'll use it  down here and I'm going to map those columns to   those fields and 4 and 6 and 8 and 10 and n 12  take what columns to those let's just look at   those columns so we know what we're looking at  what columns we're going to run through we want   to run through columns 13 all the way to 17 so we  can map those let's make that set of that so we   can see so I'm gonna run a loop from 13 through 17  when I load that contact I'm gonna take whatever's   in the row that adduce is selected I'm gonna  place this in n4 I'm going to place this in n   6 and 8 and 10 and then 12 so let's do that let's  write some code that we can do it and I also want   to know when we select this row I want that row  automatically to go to select a contact row here   be 12 so how do we do that we're gonna call that  contact load and let's write some code and I've   got a module already for that here under the  contact macros we're going to call that contact   load so let's do that now into the contact load I  want to dimension I need to know the contact row   dimension the contact row as long and since we're  looking through calls we also need to know that   call them so contact column has long also I'm  gonna switch sheet 1 we're focused of course on   sheet 1 and then what I wanted to dot range I want  to make sure b13 is true I want to set a contact   although because I need to know when a user makes  a change here I want to save that change but when   we're loading a contact from the list that's also  a change so I need to differentiate between those   two changes we're gonna use this b13 true or false  to differentiate between that and let's do that so   b13 is gonna go to true and then it's gonna go  to false equals true and then I'm gonna change   it to false once we get done so let's write that  now and then we'll put the code in between those   false set contact set contact load to false and  then to true here set contact load to true ok so   then our so then our codes gonna run in between  here what do we want to do again we can just get   a run first we need to set the contact row that's  gonna be important to contact row we know that's   the selected row it's going to be located in b12  equals dot range b12 does our contact row next up   we're ready to run our little loop for contact  column equals again remember what it was 13 to   17 was that what it is less double check here 13  to 17 correct I'm gonna run that loop so we can   pull in these ranges here I need to pull in all  those ranges so we can do that with this equals   13 to 17 and close our loop next contact column  okay next so what are we going to do again dot   range what is the range that we're focused on  here the dot range is located in dot sells what   calm what rose it where are we gonna pull it I'm  gonna pull it from they're all gonna be in row 30   but the column is gonna change so 30 is the row  the column is going to be contacting the column   so inside this range is going to be our cell so  then we can close that dot value equals what's   it going to equal it's getting equal dot cells dot  dot cells contact row contact column value contact   details all right that's perfect so now all we  need to do is set run the macro that's gonna   one put the only thing we're missing so far is I  need to know what row right we have to put that   room we select something I want to put that row  right here and to select a column b12 so let's do   that that's on selection change so let's run that  macro and let's add that actually we're gonna take   that contact what I'm gonna copy that I'm going to  go into the custom manager and this time it's on   selection change selecting what well if the user  makes the selection anywhere from m16 all the way   over to n 29 and 20 m16 through n 29 I'll so you  want to make sure that the least there's a value   in the first name so let's do that inside the  code if not intersection m16 m16 through n 29 is   nothing and I want to make sure well you just then  we'll do that here I want to set the target run   no matter what it depends range let's do this and  range we want to make sure m m and the target row and what then what first of all I want to set the  row range b12 b12 dot value equals the target row   that's important you know the row and then what  I want to I want to run the macro so good now   when we select this it's gonna load our bike  all we need to do is format these cells into   the general again we're gonna set format those  s phone number so more and then special and then   phone number here and then I'll left justify that  here all right good that's looking really good so   actually we could probably not run the macro on a  blank right probably not so let's do that so what   we want to do