I want to make sure that before we do get started that you have subscribed i do create these videos each and every tuesday for absolutely free and of course they're complete with a free download you can find the download in the links down in the description without your email or facebook messenger just click the links follow the steps and we'll get you your workbook to you absolutely free all i ask is just three things if you could subscribe that would really help us out click like on the.
Video and of course comment below that's going to help us out i respond to each and every comment i always want to make sure that you get the best we do create these videos absolutely free for you every week and i'm really happy to do that however if you would like to support us a great way to do that is with our mentorship program in this program i'm going to help you every step of the process from concept to cash and how to put your excel skills to work to create that passive income stream that you desire to reach your goals so the mentorship program is a great way to do that i take.
You every step in a weekly step video while i create an incredible accounting application so i hope you'll join us there let's get to this training we've got a lot to cover i don't want to take too much time the first thing i'm going to do is format let's just go ahead and give it a top row format and this one what we're going to do is we're going to create a lot of sections right so multiple sections four different sections five if you count the actual home screen so we're going to take that and we're going to go over a lot of rows i'm going to give this the just to fill a basic fill effect we'll.
Go with the standard blue theme i'm going to go from a medium blue to a light blue and then all i'm going to do is just going to take the rest go all the way down here and then just highlight everything else in there just the standard blue that's enough for us to get started we're not going to go too much in the concept it's here just the colors basically all right so now that we've got everything colored what i want to do is i want to create a home menu here i want to give it put some nice shapes so that the home menu is actually in the last and then it's going to cover.
One through four sections and i want that to be four sections so let's give it a title i'm to give it a title and the reason i'm going to put this title in a shape because i want this shape as part of a group so this title we're going to call this mini crm application that's fine and of course i'm going to format that differently so we don't need to fill in that we'll just use whatever background fill is on there and also we don't need a border on that too so no outline on that i'm going to then go into the home and then center this and then bring this.
Up to about 30 and then i'm going to choose our standard color and then our font which we'll probably choose for this we'll go with the arial rounded bold and we'll close that bold okay mini crm application that'll cover it now we're going to get some icons and shapes in here so what i would like to do is actually create some shapes that i want to put those icons on so we're going to click insert and i want to create like four quadrants so the best way to do that is probably with this tool here quadrant and so what i want to do is.
Create a round shape let's say two inches by two inches but then i only want a quadrant up i only want a quarter of that so the best way to do that is just to reduce it so let's go with the upper let's uh create that nicely like that and then i want to just duplicate that so i'm going to use control d and i'm going to duplicate that and then i'm going to basically rotate it i'm going to flip it horizontal like that now what i'm going to do is i'm going to take both of these i'm holding down the control and i'm going to do the same thing i'm going to duplicate it and then i'm going to flip.
These so i'm going to rotate those and i'm going to flip those vertical just like that now that's what i want so i want kind of like a mid pi and i can line those up so i'm going to align them to the left and then align these to the left here so i'm holding down the control aligning that's left now i have to do is bring this up a little bit holding on the control and bring it up here so basically what i want to do is i want to put an icon on each one of them and then i want to put in i'll probably even a larger box so let's do let's do another box and i want.
To do a larger shape this time we'll do a square shape because i want to put like a title in here something nice so something like about like that perhaps about two inches let's say two inches and then i want it bigger by 3.2 and then i'll line this up so i want a nice big quadrant right here and then we're going to move all those to the back in the circles up to the front but this one i want to format give it a nice format so i'll use the outline as our standard color which we're going to use this color and then i'm going to use a shape fill we'll use a gradient fill on this.
Something nice like this gradient that should be sufficient and then i'm going to duplicate this ctrl d ctrl d ctrl v actually four times right total four so i want them all lined up so i want these box and these are all going to be in the background actually so we're going to line them up here just like that and then i'm going to hold down the control and i'm going to move them all to the back send to back okay that's what i want and then we'll just align this better okay so what i want to do is create these and i'm going to put an icon in those that looks pretty good i'll.
Fine tune it in a little bit okay so what i want to do now is insert some icons and then i'm going to bring
These down here so i'm going to hold my selection tool and i'm going to highlight all of these and then i'm going to bring them all down here and then i'm just going to center that over here like this so each one is going to be a clickable icon so that's what i want a clickable so that we can get to each section automatically so i've got some icons lined up that is going to help us let's just line the tops here.So everything looks a little bit even okay so let's insert some icons we'll undo the selection tool and insert the icons i'm going to insert some pictures and i've got some pictures stored up here there's actually some white icons we're going to use so in fact i'm going to probably select everything on here i'm going to insert everything because at some point i'm going to need everything okay so it's kind of a big mess so let's just reset the sizes to something smaller for now so we can work with them and i want i want to use the white icon so i'm going to use this for the home we're going to.
Need that home button because that's going to go on the other screens and then i'm going to use these two for documents here like something like that i want to use email here on the right and then i want to use the contacts here in the upper left that's going to be the first one also want to use reminders here i want to set the reminders now these are a little bit small so we'll increase them okay so now just control hold down on the control on all of them and then we'll set them a little bit bigger so we'll do 0.5 good so that's pretty much it let's.
Color these let's color let's color all these a little bit darker blue something a little bit so it stands out so i'm going to do is i'm going to go into format and i'm going to go into the shape fill and i'm just going to give it something a little bit darker that it stands out like that that looks good because the white will stand out better and then we'll just line these up that's fine okay so now what i want to do is i want to click on each one of these and have them go to a specific area so we can do that let's just line these up it doesn't have to be perfect but you get the idea here somewhere in the middle okay so i want.
To give these a name and then i want to put the larger so now i want to have a larger blue just something here and then i want to do the calendar here so something nice and large so we can see it so i have a really nice menu here okay so these of course are going to be much larger and we'll make these about probably one inch or something like that so let's take a look at one inch that looks just right okay so now we've got a nice little menu quadrant that's going to tell us but we do need some names on here so let's go ahead and add some names in here i'm going to call this context i'm just typing in context and then what i'll do is i'm.
Going to format these but i'm going to format all of them at the same so i'm going to format those and then i'm going to color that and then increase the font like that and then we do need those in capital letters okay that looks good and i'll make it bold but i just want to do these in capital letters so contacts contact okay so and then also i think the italicize would be nice that's going to give us a really nice menu and allow the user to really look okay so now all we have to do is name these and i'm going to left justify.
These which are already and then the ones on the right are going to be right justified so this one we're going to call let's select out of there and select just one we're going to call this documents because i want to be able to add documents for those contacts and then we're going to call this reminders and alerts and then i'll right justify that as well and i'll do the same thing here email manage let me check my spelling because usually it's wrong so hold down there and then i'm going to right justify those email management reminders and alerts.
Documents and contacts usually i don't catch my spelling until after an hour that's really embarrassing okay so what i want to do basically is just to select on one of these and then go to the area that's it now all we need is just an icon here that's nice icon here okay good so now we've got that all out of shadow onto that so it looks a little bit picture effects and then just add the basic shadow on here and we can do the same thing with font so that it matches so format and that would be the header and then text effects and then shadow and put a.
Shadow on that okay so now we've got mini now we've got so what i want to do is now i want to group everything everything we just had here i want to group let me just line it up so it looks perfect this one needs to be lined up on the right format on the line and then right okay so now we've got everything lined up really the way we want it and we can now move ahead with the functionality of this but the first thing i want to do is i want to group everything because we're going to have to hide it and once we do group one important step so i've selected everything and now what i want to do is i want to group it and then name it and then the third step home we'll call this home group and then.
Again i want the for the third step we want to go to properties size and properties go into the properties that's off the screen here
Move but don't size with cells that's very important move but don't size cells because we want it to move but not size okay everything else i want to create a home button i think that's important because when we're in another section i want to create a button so let's insert shapes and i'm just going to create a square shape here and then i'll have to move that to the back and then what i'll do is just create use this and i'm going to bring this it got created after so we need to bring.It forward bring it to the front and place this right on top that's going to be our home button so again i'm going to run my selection here center everything center to the bottom and middle group it and we're going to call this home button home this is the button that's going to allow this going to be available on the other screens when we're in the contacts and it's going to allow us to return to this section okay so now we've named it home button we're going to use everything else for that okay so let's take a look let's zoom out a little bit see what we have here good that's just what i want that's exactly what i want that's home but.
But let's bring these where we need them let's start to build out some of the sections so we can see exactly what this is going to look like all right zoom down what i want to do is i'm just going to bring these all the way over here we don't need them on the left side but i want to bring them so i'm going to bring them at the beginning and now we're going to start to build out our individual sections so let's zoom back into the control scrolling up we're going to build out the first thing i want to do is build out my contacts section so i'm going to put it right here now what i want to do is i want to have the home here in each section so each section is going to contain that home.
And then we'll use that to get back to the main menu so let's go ahead and create some of that right now first thing i want to do is call this contacts give it a name and notice some of it's already formatted notice this is already merged in center it just helps us move things a little bit quicker my videos tend to be long so i try to shorten them up as much as i can but i still want to make sure you get to get you tons of value so the first thing what i want you to do is select a contact i want to have two ways we're going to say two ways to select the contact oh i already have a drop down list here that's cool.
Oh let me show you this i've got a list of contacts here in the data that's going to help us it's a list of documents here we're going to keep track of the data list of emails that are sent and a list of reminders with a summary so that's going to help us out i already have some named ranges let's just go over them just a few to like to help us move things along named ranges this contact name now each name range is based on dynamic and we're going to use offset and what that means is that as i create the list the list grows the name range grows we use offset each one is.
Going to start out with the header row and that's because when all the data is deleted it's not going to return an error notice that it uses a3 notice three is the header and as long as we add one row as long as we start one row below with this one here that's fine we can do that no columns we're going to i mean no columns left or right that's fine we're going to use the existing column and then we're going to count a i want to know all the contents of a all the cells so that's going to count as to let us know how many rows that this particular named range is.
Going to contain and of course this also contains a header so we have to subtract one and then a single column we only want a single column so when we use the tab key to tab out and we use the shift tab key to tab back in you'll see the dancing ants around it that lets us know that it's correct i've done the same exact thing with documents emails and reminders so basically emails here all the same everything's pretty much the same and then i have a reminder summary this is going to go over this is going to be a little summary to help us select reminders so again let's go back.
So we understand that we're going to that we're going to have all of these contacts documents emails and reminders they're all going to be centered on this area here so the first thing we're going to do is build out the contacts then i'm going to move over to the right and i'm going to build out the next one which is going to be documents then emails and then reminders so that's how it's going to work so the first thing i want is a drop down list i've already got it here that's a data validation based on the named range so if we look at that we see it's contact names you could just write an equals that's going to help us and if we select over.
We see it's the dynamic list of all the contact names so what i'm going to do is i'm just going to call this white and then i'm going to do the same thing what else do i want i want the user to actually put in the contact name with that contact i want to contact name and then i want the phone number so we'll put the phone number here go to rows i'm going to skip a row and put in the email address this might be a longer field so i'll allow that to use it and then basically i want notes so the notes is going to be this merge and center cell as you can see it's already.
Merged in center saving us a little bit of time so is the email address phone number this is going to help us okay so what i'm going to do is i'm going to format these cells so we'll go into the format and then i'm going to do a fill we want a white fill we want to border i'm going to use this blue border color here i'm going to wrap it all the way around and then i'm going to on the left side i want a dotted line that's going to denote the field then what i want to do is i want to hold down the control select all of my labels and then click each one of them.
In this case and i want to right click format the cells in this case i want to also do the same thing border give it the blue color in this case we can do this this and this and then make sure the dotted line is on the right side here that's it that's all we need to do except we've got to do this one here format the cells forgot that okay so we're good to go on that next up all right so now we've got the context i want to create some buttons so let's take a look so let's insert some buttons i'm going to insert.
Some shapes here just going to use this angle all we're going to keep this as basic as possible because this has a lot of functionality if i add too much this video is going to take way too long but i want to give you as many features as you can with of course getting in uh some brand new things save contact okay so we've got that i'm going to right justify that because i want the icon on the right side and a lot of you ask where do i get my icons i'm going to include a link down below so if you like these icons feel free and then i'm going to format that so don't worry about the icons.
It's some really cool links i'll give you that so save i'm going to use this check box to save right here that's way too big though all right so let's let's size these up a little bit there i'm going to format those and we'll just go to 0.22 that should be sufficient and i'm going to bring them to the top notice the buttons got created so let's do this let's duplicate this button we're going to use that for new context so i'm going to call that new contact then what i'm going to do is i'm going to.
Then what i'm going to do is i'm going to bring those notice these buttons got created second these got created first so you want to place these on the top so format and then bring to the front bring to the front that's going to place them on top not going to use delete right now maybe a little bit later on so save the contact and new contact there are two buttons here so that's what i want to do now i want to that's what i want to do all i want to do is line those up so i'm going to format those and line them up that's it that's that's all i need to do.
And now what i want to do is i want to create i want to be able to select a contact with a list how can we do that i want to so two ways one we select a con let's get out of this select let's get out of this selection change one i want to select and have that contact load automatically two is i want to select from a list here and also have that so how can we do that well we can insert and what i want to do is i want to insert a list box here list box let's insert this list box right here and i'm going to give it a name so we're going to drop it down about right here and i also want to give it a name let's.
Call it contacts obviously context good so that's pretty much what i want and uh we'll format this cells and we're going to copy them over so we can format the others much quicker so that's going to be easy give it a fill effects and then use the same pretty much the same thing we're going to use this one and then we'll use this one here okay that's going to give us a nice little theme and then i want to put a border around this and a border around this so i'm going to select this all the way down maybe not down to there all the way down.
To here and then here and then here format those cells again with the same color border around it it's going to give us a nice look here so the border and then we'll go into this border and then give it an outline that's going to be sufficient okay so now we got so what i want to do is i want to populate this list box with all the context and then what i want to do is i want to basically select on any contact in the list and have it show so how do we do that well we've already created a named range called context right if you remember.
Here formulas name manager notice we have contact names that's the one we're going to use so all we need to do is edit it ctrl c copy that clear close that out right click here format control and input range is going to be pasted in your contact names and we also need a cell link i've got a cell set up for it it's going to be called selected contact located in b3 this is going to be our hidden columns a and b and we're going to use that so i'm going to use b3 so all i need to do is select in here click b3 that's going to be okay notice.
As soon as we do that it populates with the list now what i want to do is when i select it with a macro it's going to load that that's going to look really nice okay it's going to be really easy and we'll be able to scroll that list so it's going to be really good all right so that's pretty much it as far as the context as far as that and then i want this home this is going to allow us to return back to that home that we designed originally so that's going to be just nice okay so we've got contacts the only other thing we want to do is we want to group this right when we're showing the other ones or we're showing the hub we want all this.
To be hidden so how do we do that well the best thing to do is just simply hold the control group them together holding the control let's put this line this up a little bit and reduce the size of that button make it look nice okay it looks pretty good so all i want to do is highlight everything i'm going to exclude the home because the home is going to be used for every single one we don't so i've highlighted everything and now i'm just going to use my grouping tool here group so then what i'm going to do is just write it i'm going to call this contact group okay great so i'm going to pretty.
Much do the same thing for everything else so all i want to do now is pretty much copy this and paste it into the next one so it's super simple at this point the next one that i want to create is the email so let's do that copy that make sure we're copied go over here slide over here and then maybe right around o i'm going to paste everything and paste it all in now all i need to do is just update it for that update it for our email so i'm going to type in emails here.
Emails okay we're going to call this send email in this case send email and then we'll add this remove this icon and we're going to call this new email okay so the new email like icon everything stays the same and that looks good okay so now what we just need is the email icon we can borrow that email we have the amount icon right here i want to use the white one so i'm going to copy this and then what i'm going to do is i'm going to just paste it in right here and paste it into the button here change.
That to the buttons our icon size which is 0.22 and then bring it over here now already now we've got the send email now what do i want so now basically i want to change this to send to of course we're going to keep the contacts because we want to send it to whatever context we'll change this to the email address email and this will be the subject this will be the attachment i want to add attachments.
And it will be the messages since we're going to be taking documents and recording documents i want to be able to send those documents really easy how can i do that if i've got a bunch of documents i want to select on one of those documents and have them added so how can i do that well what i can do is use something like this but for documents so i'm going to control d duplicate that i'm going to bring it over here and we're going to call this one i'm going to copy this and i'm going to paste it right here we're going to call this documents documents or select to.
Tab i like let's put select to attach okay that's probably better so now we know of course we got to change that now we have a named range already of course if we look in our formulas name manager it's called documents so all we need to do is edit it copy that just easier than retyping and that way we know we've got it right right click here format the comments again change it right it was contact now and now of course we don't want that i've got one already an area already here selected document into the let's.
See in emails we've got selected attachments here in b13 that's what i want let's highlight all this get rid of that and click b13 that's what i want the selected attachments select attachments i want to know that okay so we've got that and we do need to update here we need to update the contacts here because this is for email right so i want this for email i want to see my email so that's all we don't want that this is going to be recent emails just re so that way we get a nice idea of recent emails.
So again again i've got got it already formulas name manager if we look emails tab over we see that's a recent email sent to all we need to do is edit of course copy we can retype that in no problem do the same thing here right click format control change this to emails and then change the cell link and this one i've already set it up if we look under emails i know that we've got selected email and b14 it should be sel b14 so i'm going to select that and click ok so that's our selected email.
So now once we select it we're going to see it's going to change of course they're to the same person but we see that it changes this goes to one and if we change this to one it's also going to change to one the selected attachment now they're both one so we see whichever one if we select the second one it's going to show up that's going to help us add it okay great so we've got emails we've got recent emails we've got the message the attachments what i want to do is i want to be able to select on the attachments and then have them load up into the email so we can easily send an email attachment so we're gonna bring.
This over make it the same way that's it that's all we pretty much need to do as far as the design portion of the email section next up why don't we put in let's put in our document details so i'm gonna put in documents again i'm just going to copy this here it's going to be pretty much the same we can probably do this documents and then we can move on over we'll slide on over here let's say aaa i'm going to paste it right in here there we go now what i want to do is just change this word to documents so this one we're going to call documents okay and of course we're going to change.