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 accordingly 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.