How To Create A Construction Project Manager FROM SCRATCH + FREE DOWNLOAD

managing construction projects has never been easy there's projects jobs tasks materials items inventory staff and vendors all to keep track of hi this is Randy with Excel for Freelancers and today that headache is going to go all the way with the construction project manager I'm going to show you every step of the process even designing an application like this into Excel and when this training gets done you're going to know everything about it it's going to be incredible training I cannot wait so let's get started all right thank you so much for joining me today I've got a fantastic application to share with you and of course we are going to be creating it together this construction project manager combines projects jobs details tasks combines it into an incredible skler complete with tons of filters we're going to be able to add items and details to this and going to show you every step of the way so you won't want to miss a moment of this incredible training I am super happy to bring these to you I bring these applications to you each and every week and Tuesdays is where we do the comprehensive applications Saturdays is where we do the basic VBA training so whether you're a beginner or Advanced there is something for you on this Excel for Freelancers Channel Make sure you do get subscribed and also click the notification icon Bell that's going to ensure that you get the trainings sent alerted to you of course if you want this template as always it is absolutely free all you need to do is look for the word download down in the description click that add your name and email and I'm going to get that s right over to you of course this training the template are free but if you do want to support this channel so that these trainings can continue to be free there's some really great ways to do that and I've got some incredible products and courses for you starting with the AI tool pack it's got six different features including the AI assistant here where you can simply bring it up and enter your question some if samples and just hit enter twice and it's going to automatically come up for you or any other samples you have so we've got some great features on that we can clear it copy to the clipboard close it the AI tool pack also includes fix my formula so if you have an issue with a formula you can just place it directly over the formula and then just ask your question it's going to give you some advice or any issues that you have it'll do that fix my VBA code if you want want to add it in there all you need to do is just copy your VBA code directly in here and it's going to fix it up for you also we have inside the get table data this will get any table data we can write VBA code describe what you want and it's going to automatically write your Macro for you so that's a pretty cool feature let me show you how that will work you can just say something like print current region and just hit request code and it's going to automatically share that code with you just like that along with an explanation so that's kind of helpful and of course we have a chat GPT function if we were to equals chat GPT and it can actually inset any function you want right in there so that's a cool feature that is the AI tool pack so I hope you'll pick that up include the link down below all right let's get started on this training I want to show you some information about this construction project manager it is broken down into three levels the largest being the project so a project could be a new house or house remodel or something large now this particular training you can apply to anything not just construction projects any kind of large project that you want to manage that wants to be breaken down into smaller you can use this or you can modify it once you go through this training we're going to show you everything so you can customize it to your liking so it starts out with something like a new house a larger project and then it's broken down into individual jobs such as the foundation or the kitchen remodel or something smaller inside that it's broken down into smaller tasks so each one of these tasks can be on the schedule so these are the tasks so these are individual items that you can put on the schedule when we click on a task it is going to display that task we can make any changes to that task if I decide I want to make this task a little bit longer all I need to do is just click it and you see that the task is now extended if I were to change the status of this task to in progress and save it you're going to see how the color automatically changes on this those colors are full fully customizable inside the admin screen where we have all of our statuses in fact we've got a long list available we can just simply change the color if we want to change it to a brighter yellow we can do that and then all we need to do is just update the schedule simply saving a task is going to update that and we see how everything got updated so it's kind of a nice way to automatically make updates on that and so each individual task is tracked we can track the start date and the end date of the task we can know the sales price of how much we're selling it our budgeted cost and our actual cost of the task and then we have the estimated profit and the actual profit of the individual task now that gets all combined into a single job so if we have a job for ceramic flooring so that particular job let's say we have here our ceramic floor install so we can have floor removal floor prep so different parts of the job are all individual tasks those get totaled up into the total budgeted costs and the total actual costs and then the profit individually if we have that and then obviously we would increase this to a larger sales price that we're selling the floor at so that it's more beneficial and so we can save those individual job details each one of those can have information now we can add attachments to these individual jobs so we can select on job maybe we have a blueprint ad so we can do that too once we have those pictures and documents we can remove them or we can open them up for viewing so it's kind of a nice feat feature that we have there available if we need any equipment items or quantity we can simply just put in here and so we could just put one floor sealant so any materials that we might need for an individual task we can simply add them on to the task and they're going to be available we can then remove them if we want to so it's very easy to add equipment needed we have our overall cost versus profit based on here we're going to be creating that and so we've got the project details here we can show individual projects or we can show all the projects if I only want to show one project I can do just that or if I want to show just individual jobs within that I can do that as well so let's choose something that has more than one job so this one here Lisa sync we have foundation so maybe we only want to show just a foundation we can do that too or individual tasks so a whole lot of ability to filter we can select individual dates on this so we can choose a specific month or we can use our navigation here to the previous month or next month we can also filter by status if I only want to show individual status I can do that so maybe I only want to show those items that are to do so we can do that of course we can print the schedule and we can do a whole lot more all right so this is exactly what we're going to be creating I've got another workbook and that what I'm going to do is I'm going to slide this over to my other screen and I've got another workbook that we're going to be working on now keep in mind that I just put a little bit of formatting in that's going to help move things a little bit quicker on this training just the basic formatting because I know these trainings can tend to be long but but don't worry you'll be learning so much we still have our admin screen intact I've got some databases of our projects databases of our jobs a list of jobs a list of tasks that we're going to be having a vendor list documents which we going to be used for every individual job equipment which are going to be used for individual tasks and we have a calendar popup which we won't really need much here on that so that's what we're going to be doing and let's get started on this training we got an admin so we just got some formats first thing what we're going to do is I'm going to put that title there so I'm just going to take this one right here cuz it's already formatted and we're just going to paste it up here and I'm going to call this our construction project manager so in capital letters construction project manager and then that should be sufficient enough we have our logo here and then I'll just bring this over and we'll make it a little bit smaller on this one and I'll also make the font a little bit smaller on this one since we're doing formatting a little bit I'm going to show those tabs and commands and then go into the home and just reduce this a little bit here something a little bit more manageable here so we'll do go about 30 on that since we don't have a lot of room we want this screen so the first thing what we want to do is I want to know that project management that's our first set of columns so project management is here then we're going to have our job management so we'll just call this job management put all capital letters consistent and then next up lastly we have our task management okay so the first thing what I want to do in here is I want to be able to have a button set so we're going to keep this area for our button sets here is going to be a drop- down list I've got some named ranges I'm going to be going over with you so we'll go in that select project so if we have an individual project that we already created we can select it and it will load up once I select a project I want to know all of the jobs within that project so that's a dependent dropdown list so we're going to select a job inside each individual job we have a number of tasks and so we want to select a task task within those jobs so select task we're going to put there so here we're going to put in something called project details as we want all the details of that project next up is the job details and then lastly I also want the task details we're going to be able to save update and delete and add information on those job details we're going to put in our first detail so I want to know the project name we must have a name for the project so we're going to project name we'll abbreviate that since we have limited I also want to know the project number or maybe there's a code that's associated with that so I'm going to put that in code and next up after that I want to put in the customer who's a customer that and then the location also after the location I want to know the project status now we have some status that are saved inside our admin screen which we'll be going over and I also want to know the type what is the type of projects we have individual project types so we can put in something called project type here that should be sufficient I do want to know the project contact who's the contact for that individual project project project contact next up after the contact we want to know the start and end date of the individual project so this is the entire project start and then we'll do the project end date so project start and project end so those are going to be date fields next up I want the sales price now this is going to be user entered the total sales price the next four are going to be calculated so I want to know the budgeted cost now the budgeted costs of an entire project is based on all the budgeted cost of the job detail so it's based on that I also want to know the actual costs of that next up I want to know the estimated profits now the estimated profit is simply our sales price minus our budgeted cost that's going to determine what our estimated profit is after that I want to know the actual profit what is the actual profit now the actual profit is simply our sales price minus the actual cost so we've got that there what I'm going to do is I'm going to copy this and I'm simply going to paste here and I'm hold down the control and I'm going to paste the values and then we're going to make updates accordingly so obviously this is going to be called job name and not our project name but they're very similar so we can do that next up there our job number we'll change this to job number en code here so I'm going to put this job number and I'm going to put a space right here between the number so we have the job number instead of customer what i' would like is the vendor or maybe employee so we'll put vendor and I'll put back slash and then o r we'll do EMP could be an employee that you're going to have or maybe a vendor so we have a list of those I also want to know the location we'll keep that on it and this one instead of the project status we're going to call this job status so I want to know the job status of this next up after the job status I also want to have inside our job type so what is the type of that job I can keep that as type we'll keep that there and then also I want to know the job contact the individual contact for that job which might be different than the project this is going to be the date that we actually start that individual job and I also want to know the end date of that job so we need to put in there job end so we'll keep that just like that after that I also want to know the sales price we'll keep that the same budgeted cost so these are all okay I think that's correct I think we're good to go on that so the budgeted cost we're going to keep calculated I have these in blue because these are going to be formulas and not user Ed so we're keeping them those are going to be formulas based on all the tasks very good so next up let's focus on the task so we're going to call this task name name next up I want the task number if we keep individual task numbers we're going to need that so task number or code that has to do only with that individual task after that I also want to know let's say assigned to maybe we're assigning this to some individual so we want to put that task who it's assigned to the location can stay the same we'll put this as our task status so this is an individual task status and we'll keep this just as tight and then we're going to put this of course as our task contact individual contact for the task askk lastly I want to know the task start and the task end so those are the individual task start dates and our task end dates so these are always a subset of jobs and jobs are a subset of our projects sales price budgeted cost those can all stay the same and these are going to be user entered these are going to be calculated so that's good let's save our work so far and then what we want to have let's move a little bit over down here as we're moving on I want to know some notes and details so each one has their own notes and details this is for the indiv ual project and then I'm simply going to copy this then hold down the control and then paste those values the formats are already set so each one will have individual notes and details on that which is fine so this area will be for notes and details now this one I would like to have job and profit details and I'll have a donut chart below that I want to know the pictures and documents so pictures and documents that we'll be adding there next up I would like to have equipment needed so we need individual equipment needed for the job so we can put down here and then a little bit more of an explanation down here so this is going to be our added on date that's the date that our attachment or file or picture was added on next up I want to know the document name document or picture name next up some more details here so we can just put equipment and then we'll just put name and quantity very good so we see we've got that formatted and we'll keep that available down there so we've got the left section let's work on this information here so what I'm going to do is I'm going to reduce this a little bit here this is a drop- down list of projects so I want to know the individual project we can just put project I think it's fine next up I want to know the job and actually I want to know the task so that way we can filter those here what I want to do is I want to be able to filter based on year select year and we're going to have a drop- down list there and I also want to select the individual month so select month will be here moving on over there I also want to have filter by task type and then lastly on the filters I want to move over and I also want to filter by the status so each individual tasks have status filter status so we can do that next up what I would like to have is I'd like to have the month and our first date right here so the date and the month will go here and then we'll have a button set in here for our navigation so what we want to do is we want to fill in some years and we want to fill in some months so I've got some section here that can do that so that's exactly what I want to have here we'll put inside our months here so I'm going to do months and then years and our months are going to start off in January and then go down from there so all we need to do is just simply drag and drop for all of our months and that's going to keep our months next up let's start with the years 2023 and then we're going to go 2024 so then the Excel will take care of the rest for it and we'll just drag this down here so we have a set of years now I've created a named range here already which is going to help it we're calling that months I've created a named range here already called years now all you need to do if that named range doesn't exist just highlight that and just type in years and then your name name range is created so now that we have that I want this to be our years so we're going to go into the data validation and it's going to be already there to help us and it says years all I would do is add a list here and then just change this to years so that's going to make things go a little bit quicker and we're going to select the current year which is 2024 also want the months again date of validation inside the date of validation list months so that's already there to help us move things along again all you need to do is add that in here that'll help make this video a little quicker now we're going to set April that's the current month so now if I know the current year and I know the current month what I want to do is I want to get that first day of the month in here so we're going to use the date function to do just that so I'm going to equals date and what is the year well the year is going to be located here and I want the month number but how do I know the month number I need the month number I don't need the month date so how do we do that we can use the match formula match what am I looking up I'm looking up April and I want to look it up where in our month named range so that we created our month range and I want an exact match very good and so what that's going to do is going to get us the month number which is four perfect then what do I want I want the day the day is going to be one so what that's going to do is going to return one now why didn't it return the day because I've already formatted it how do we do that so notice it has a custom formula already if it has a short date would look like this if we expand it we see that it's a short date however keeping the column small what I want to do is I want to give it a custom date so how do we do that we just go into more number format and then we go into custom and all I want is a single day so just D is sufficient for us and click okay and that's going to show the one very good so next up what I'd like to do is simply equals this date plus one and we're going to call this month start so I've given that a name range called month start plus one how do I know that if I select on here let me add that in here month start not times one plus one so how do I know that this is month start again I've just selected the cell and type in month start that's going to give us our named range cuz that's going to use the formula so now that I have this the next day all I need to do is drag this all the way over there and then I need to make a quick update on the formula so what we want to do is notice that it says month start plus one what we need is not the month start I need the actual cell so how do we do that we just select on the cell but we want to use the cell if we're going to formula that so in this case it is T5 so we just put T5 here + one now all we need to do again is drag that over here and we see that we now have all the dates however I really don't want the next day of the month so how can we avoid that we also know that February sometimes goes until only the 28th so this might be empty I don't want to show the next day of the month so in this case what do we want to do I want to write in a formula so we can do something like if we can check the month the month of au5 + 1 does not equal the month of au5 then what are we going to show then nothing otherwise we simply want to show au5 + 1 so we're checking to see if the month is the same if it is not we're going to show blank if it is we're simply going to add one now if it's 29 we do need to make one more caveat here and that is simply checking the month in fact not this one but this one right here so what I want to do is equals if I want to check to see if the cell before is blank so in this case a V5 equal empty then show empty otherwise continue on with the formula so now what we can do is enter that now we can just simply move this over to one cell okay great so now if I were to change this to February we want to show that these are two blank and that's exactly what I want perfect so it's looking really good let's bring that back to April so things are looking nice I like the way that we have the months there and we got the filter by now let's focus on some of the named ranges so inside our admin screen I've got some named ranges that are going to help us move things along we've got a document folder which is where our documents are going to be located pictures or documents we've got statuses and I've got a named range for those statuses so what we're going to do is we're going to go into the formulas we're going to go into the name manager and we're going to look up something called status and we're going to tab over now I've used offset on almost every single one of my named ranges so we don't need to repeat it but that's very important because offset's going to allow us to increase or decrease our named range as we add or remove items within the list so that's why I've used offset so we see that status is going to be contained in our status so we know that I've also have project types so we go down here and project types is here so we've got a named range for project types I've got another named range for job types so if we select here job types down here and I've got another name range for task types so each one of those individual can be updated so task types is here so that's it for the named ranges one more named range which would be our document folder so I think we've got it all the way up here document folder here is simply our D3 we'll be using that in the code it's called document folder that's it for the admin screen nothing else on that so moving back into the construction knowing that we've created some named range we've got some project types here so I've got a named range already set here if we go into the data data validation we see that we have a list called project types likewise we have inside let's just make this job type I think it's kind of important then we are going to again data validation job types and lastly our task type here so we're going to put in our task since it is individual and that again is our task types so we can simply select from the drop-down list of what our job types or our task type is which is kind of nice our status project status job status and task status are all exactly we only have one named range for status so each one of them has the same drop down list and that drop- down list is also status which is the one we went over so each one of those exactly has our name range very good now our project start and our project end dates are all date formatted field so we want to make sure we use the control here home and make sure that we've set those to a date field so short date or date is fine and then of course our sale press and our budgeted cost all of these here we're going to hold down the control and make sure that those get formatted to currencies we want to format those to currency everything else is texed on that very good so we see how we got that we've also got some drop- down list here let's go ahead and add our button sets now and then we'll work on the admin information we've got a number of buttons to add so let's go ahead and insert shape and we'll use a rectangular shape here and these are button sets we don't have a lot of space for our button set so we're going to keep them relatively small and we'll go to0 2 on the height and we don't need any outline on that so we'll see no outline once we get the first button just the way we like it we can then set the rest as we wish and then we'll set it up about like that okay so perfect let's go ahead and choose some theme colors for that use control one what that's going to do is going to launch the format we're going to go to the fill we'll use the gradient fill we'll just use two colors so I'm going to remove one and what we're going to do is we're going to set the first color as a light color something a little bit closer to our theme and then the second color a little bit darker also closer to the theme that looks good so now that we got that formatted what I would like to do is I'd like to put it in the middle of the text and WR justify it so we're going to call this add project it's going to be our first button I'm going to go to the text options make sure that we go into the text box and the margin so I'm going to zero out the left margin 0.3 on the right margin Zer on the top and zero on the bottom very good so now all we need to extend that button so the width will do something like 0.9 we also want to make sure that we have enough room for an icon so 0.95 or something like that or let's just do one so now that we have that and it looks pretty good what I'm going to do is I'm going to duplicate that using controll d and we're just going to call this save we have very limited space here for our individual projects so what we want to do is just keep the width as small as possible and to make sure that we have enough for that okay so save will go right here and then what we want to do is one more and we're going to call that delete so it's going to give us a delete very good so now I'd like to use a font that's similar so I'm going to use a theme font color we'll do the text fill and I'll choose this color right here so things are looking pretty good the save button a little bit too big we can always zoom in if we want to fine-tune the side of any button in this case what I'm going to do is I'm going to make this a little bit smaller and I've got our button sets up here so I like that let's remove the border on those we don't necessarily need the Border I'm going to go into the shape format and then we'll just use no outline on that that let's keep the Border actually and then I'll just make it a little bit lighter on the border so that we can kind of keep an eye on it close to what our theme is we'll use this color here so that's kind of nice all right now what we want to do is I want to create all of the buttons before I add any of my icons so we have ADD project delete so what I'm going to do is just simply make sure that these are lined up and then centered here and then what I'm do is I'm going to use control D and we're going to use this one for job so I'm just going to bring this right over here and then what we're going to do is we're going to call this job so we only need to change this to job and then we can of course change the button width so we'll set the width to8 on that we don't need as much space on that and I think all these we're very limited in space so what I'm going to do is just to make sure that they're all set to about 0 2 on the height next up once we have that save and delete we can bring these over here we'll line them up I'm again highlight those and I'm use contrl D these are going to be for our tasks so I'm going to bring these over to our tasks this one's going to be called add task very good so we've got all of that ready to go now what we want to do is I want to add some icons for our scheduling and we also need some for our equipment so let's take a look at this I'm going to click on controll D once again I'm going to bring it down here I want to be able to add pictures and documents here so all we need to do is just call this add and we'll put the icon there and I'm going to bring it right about down here and we can make it slightly smaller and then what we want to do is duplicate it so we'll do this 0 55 I'm going to use contrl D we're going to use this one let's do this once again it went somewhere wherever it went I don't know so we'll move it right about here so we need to add equipment so I like that let's continue on we can close this out with a little bit more space we also want to create buttons for this month so again what we can do is just use a save button use controll D here selecting outside not the text inside and then bringing it over here now we need buttons for our arrows so I'm going to remove the text on here and I want this almost relatively same so we'll do Point 2 two on the width and do contrl D so this is going to be for our navigation to previous and next month now what we want to do is we want to have one for this month so I'm just going to take this one contrl D and we're going to change the text to this month so this month and that's going to be with an icon so we'll need to make space for the icon we'll bring this right about here and we'll make the width about one on that one very good so we can line these up a little bit now that they're Center and of course again we're going to add those icons put those in the middle and maybe distribute them horizontally so that there's enough we can also group them once we add the icons all right very good so we've got this month next up I simply want to be able to print the schedule so I'm going to hit contrl D and we're going to print the schedule so we're just going to call this print schedule and we'll add in that text there okay very good so the button sets are almost done and then I'll just set the width on This 1.2 Saving Room for the icon all right things are looking very good now what I would like to have in here is I would like to have the month and the year so how do I get that I want that to display properly so simply we need to set a format and the date so we know the date we're just going to put equals month start now we already have that named range and look it's already formatted for us how do we do that so we go into the custom manage formats and basically we're showing the full month which is 4 m a comma and the four y's that's going to get us that format now if we wanted a shorter month we could do that but we're good to go on that so now we've got the month scheduled and of course as we change the month here it's going to automatically change the dates and that's exactly what we like very good so we've got that set up and now what we're do is we are ready to add in the icons let's just check for the button sets we'll Zoom back out here back to 100% we'll add in our icons and then complete our button set so pictures place over CES this device and I've got some saved up of course if you want those icons or you want to create additional features that you want we'll make sure to have those for you inside my patreon so make sure you join us on patreon I'll be completing additional features for you on our patreon so that's a really great platform plus whatever you want I'll be adding inside there make sure you join us that way you can get all the attachments and everything else so now we've got add a task so this plus I'm going to use it for every time so I need 1 2 3 four five I believe about one for the project one for the job one for our pictures and documents another one for our equipment needed okay so I think that's it for the ads can delete that one next up for our this month we can use this one here this one here for our next month our navigation this month for the calendar I need this for the save so I'm going to need three of those cuz I got three so duplicating that using control d one more time and then bringing it over to the save I'll zoom in line everything up our folders that one I've already got inside our admin screen so I think I've got that in a hidden shape there and then the printer I think uh printer is going to go over here and then we need to delete of course we need to delete tasks jobs and projects so that's going to have to be duplicated as well and we'll move that over here of course we'll update that all right so things are looking much better now what we do is we're going to zoom in line everything up make make sure it looks good and then start grouping things together so this button size looks good here so I'm going to make sure that they're in the middle having the quick access is quite helpful we're going to group that individually next up to save again I'm going to hold down the control put those in the middle delete I think we need a little more space on this button here and then sizing it up and then putting it in the middle here and then grouping it together so we have limited space for these so when we move these we want to make sure that we have equal distance between all the buttons so I'm going to hold down the control and I'm going to make sure they're all in the middle and then they're distribute horizontally once I like the way it is I'm going to group them together whoops I need to group this together first notice that one didn't go there so grouping these together okay perfect so I like that let's move this over now we're going to group them together otherwise that icon is going to be gone holding down the control here again just to make sure we're spacing them and then grouping them together so this keeps things really nice very good so we see that's grouped together we're going to duplicate this action for our job management so we need to make sure sure that we can be able to add a job save a job okay so once we've lined them up and grouped them together having the quick access tools here at your disposal especially when you're designing these applications can save you a lot of time so I do urge you to set up your workspace so that you can quickly create these applications and that's why I can do these in just a few hours with you guys here so it helps of course it takes me a long time I spend a few days on each one of these until I get them ready for you all right so again once we have everything lined up we want to make sure that in the middle distributed horizontally and group them together there is a last step once we group we must also move pult size with cells so that'll be the last step we'll do those all together increasing the delete a little bit adding more space here so lining up the buttons will really help give this a nice professional look which is exactly what we want then we'll be assigning the macros and we'll also be going over the macros so you can understand exactly how these things work of course if you need help on this don't forget we have an Excel for Freelancers group that I've had for almost 7 years now and it's up to almost uh 70,000 members I think we have 66,000 members something like that so that's a great group make sure you do get into that group if you need any additional assistance very good now what we're going to do is we've got those group I'm going to then line these up if the buttons are really really small you can use your selection tool right here I've added that to my quick I want to select both the square and the icon and I want to make sure they're lined up together then group so our selection does a great job with that so we can do that here lineing them both up in the middle and then grouping them and then also doing the same thing here in the middle grouping them and then all together so we group them individually we line them up we distribute them and then we group them together now the print button and then we want to make sure that that's all lined up here very good so things are looking really good here we've got our button sets here and then what we'll do is we need the add here and I don't think we need this folder button if we do we'll bring it back later and then uh putting that in the middle here grouping them together lastly the same thing here we want to make sure they're all equal distance and then grouping them and then these two should be in the same level so we're going to make sure that those are so things are looking good the last thing is very important if we decide to change any of our column moves we don't want we spend a lot of time on those buttons so I'm going to use my selection tool I'm going to go over all these buttons here just like that making sure that they're all selected here and then what I'm going to do is I'm going to use control one and take the selection off here when you go into the properties here and I'm going to make sure they're set but move but don't size with cells so that's an important factor once we create those shapes okay saving our work so far let's take a look filter by task and filter by status we have some name range here now if we take a look inside the admin here we have something called all types all types all types we have a named range that I showed you but I also want an additional named range that includes all types so that's going to really help us when we want to add those so how do we do that so we can create a formula and the name range and we have let's say job types with all so job type with all it's a different named range and includes the all types I've also got task types with all so if we take a look into here task types with all we've got that and of course project types with all so each one individually also has project types with all so I've got named ranges that exclude this which we need for here when we have a task type we don't want the all types here but when we want to filter by a certain task type we do want it here notice that we're filtering by task type or filtering by status we also want to make sure that we have those so I've included those named range so we take a look here in the data validation we see that just that named range and just click yes to just a merge cell that's why task types with all status types with all so let's take a look in the data validation here status with all very good so we have that it's looking very good and now what else do we want to have I've got some named ranges that are really going to help us determine if we take a look inside our database here let's start out with projects projects have an individual ID and then all the information for every individual project jobs have a job ID and also I need to know what project they're assigned to so we've got a project ID and then a job name and all the rest of the information for jobs tasks let's move this over here tasks have an individual task ID they have a project that they've been assigned to and they have a job ID that they've been assigned to so we also need to create named ranges for all those which I have done to make things a little bit faster so if we look into the formulas name manager we have one for each one of those so starting out a project I've got one for project ID if we look into jobs I've got one for job ID also using the offset and lastly I've got one for task ID so each individual one has an ID so now let's move into the admin knowing that and we can focus on some of the things that we're going to need for this task this section I want to devote four projects this section I want to devote four jobs and this section I want to devote four tasks that's what they color differently and that's going to help us understand so the first thing what I want to know is when I select an individual project I want to know which project is selected and I want to put that project ID right here so let's say it's project ID one so what we're going to do is we're going to put the selected project ID so I want to know that ID the next done I want to know the selected project database row what row is associated with this now we know we've got a named range here so I'm going to use a formula equals and if a I'm going to wrap it in I want to match what am I looking up I'm looking up the project ID which is located here I'm looking it up inside the named range called project ID I want an exact match I want to return the Row the first one starts in row four so I'm going to add three if it's an error I'm going to show empty next up when we add a new project I need the next project ID so the next project ID again we're going to use if air and the reason we use if a is because if there's no data at all it's going to create an issue I'm going to use max formula on the project ID so the maximum of all the IDS as long as they're numerical plus one is the next available one if we have no data at all I want to default it to one so three so if we take a look inside our projects we see the next available one would be three very good also when I look up a project here I've got a list of project names here so if I look at the name manager notice we have project IDs but we also have another one for project names it's listed basically names so so I have that there so what I want to do is create a drop- down list of all the project names that I have so if we go into the data and data validation and list and we see its project name great but when the user selects a project name here I want to know what id I want to extract the ID for this why is that important if I take the ID and I put it directly in here and then I know the database row that gets calculated it's going to load all the project details so I really need that if it shows nothing that the user has not entered anything correctly so what we're going to do is we're going to put entered we'll call it project ID so it's the one they entered how do we get that project ID well first I'm going to use if aor next I want to index that project ID and so we're going to use project ID and how do I know what the row numbers we're going to use the match on that and what am I looking up I'm going to look up this project and I'm looking it up inside the project name and I want an exact match and now what I want to do is I want to return the column which is one if there's an air I'm going to show empty so that's going to tell me that Lisa's new house is project id2 so if I look in here and I see Lisa's new house it's project id2 so when the user has searched something if they clear it out it's going to show empty which is correct not an error so that's really all I need for project now we're going to do exactly the same thing for jobs so this kind of repetition is going to help you learn block so let's move a little bit quicker so we're going to do selected job ID next up I want to selected job database row just as we did with projects and I want to know the next job ID and lastly I want to know the entered job ID same thing for task selected task ID selected task database row and our next task ID and lastly our entered task ID great so now that we have that let's put in a one here and then let's put I want to know that job row again equals if air I'm use the ma match I'm looking up the job ID I'm looking up inside the named range called job ID I want an exact match I want the row number so I'm going to add three to that if it's an a I'm going to show empty that's correct our job number one is on row four that's what I want next up again the max formula for the next available one equals if air maximum of all our job IDs plus one if there's no data we're just going to return one I want to make sure that I got next which is one correct and lastly if I've selected job from here I've got some information that we're going to go on so select the sync replacement again I want to know the ID of the sync replacement and all of our jobs so we see our syn replacement it's job ID one that's exactly what I want to return so our entered job ID again equals if air we're going use the index this time I'm indexing the job ID here there going to be match we're looking up the selected job here we're looking it up in our job name that's where the name range we're looking it up I want an exact match column one if there's an air I want to put empty so that's it so we enter job ID as one for the sync replacement if I change this to ceramic flooring it is going to be enter job ID3 and we take a look inside here ceramic flooring is job ID 3 perfect very good next up task ID again if we enter task ID one that's going to be row four equals if air using match looking up that task ID inside our task ID dropping it down here this is our task ID exact Ma I don't want to return the row so adding three again if there's an air just show empty four is perfect our next task ID equals if air maximum on our task ID notice how things are you know always the same so it makes learning a lot easier plus one and if there's an error we'll just default it to one so our next task ID is 18 if we look inside our tasks we see our next one's 18 perfect next up the enter task if I entered a specific task here I'll be going over these data validations very very soon and so flow removal I want to know the ID if I look inside our tasks and I see something called floor removal right is right here we see that that's task ID 6 that's what I want to return so again equals if a I'm indexing our task ID that's what I want to return and I want to look up using the match using our selected task name and I'm going to look it up inside our task name named range which is here exact match single column and if there's an air show empty six is correct okay very good so saving our work so far we've got our formulas done here next up I'm just going to focus on our filtering section so let's put in a title here called filter criteria ID numbers and the reason why I want the ID numbers is I'm going to be able to select projects jobs or tasks here so we've got some data validation here as you can see and I'll go over that with you take a look inside here we've got project names sorted with all and I'll be going over that VBA is going to be used to create these so we want to make sure that we have that so it's basically all the jobs plus the word show all and we also want here all the tasks plus the show all so as we create a new task we'll have that so for example let's take a look at the jobs database if we move on over here we have something called show all and then all the list so every time we create a new job where we update a job name we have the list of all the jobs here along with the show all it's going to create that dropdown list that includes that show all the same thing for projects here if we take a look at the projects and we slide on over we also have the same thing and lastly for tasks we have something similar so it helps us with that named range so what I would like to do is you see if they select a particular task or they select a particular job or they select a particular project what I want to know is I want to extract that project number or the job number or the task number and the reason is this inside our schedule which is where all the data is going to show it's going to come from our task so all of our tasks going to show up on the schedule now if we take a look inside the task database we don't have the project names here I don't have the job names I only have the project ID the task I do have a task name so what I would like to do is when I create that filter I want to filter by a project ID a task ID or a job ID however in here we only have the project name the job name and the task name so I need to convert the name to an ID so how do we do that so if I've selected a project I want that project ID to be returned so we can do that right here so what I'm going to write is call this filter project ID and we can call this uh filter job ID and lastly I want to do filter task ID however if it show all I just want to show something like this so let's say they show all I want to show something like does not equal so let's write in a little bit of a formula that's going to help us with that inside the first one this is going to be based for our project so we're going to do equals if eror and then what I want to do if I want to look inside here S2 if it equals show all so let's write an equals and then we'll do exactly as it is show all then what do I want to show then I want to show it does not equal so we'll put in quotes does not equal so does not equal is going to show every single one right doesn't equal empty is basically what that is however what if it's false then what I want to do is remember I want extract the ID number so how do we get the ID number just like the index we created before we're indexing here this time I'm indexing our project IDs and I want to look it up where just like we did before I want to look up whatever is located here and I want to look it up inside a project name so here we have a project name one exact match and it's a single column all right now if it's an a we're just going to show empty so that's how we're going to do it let's fix that right there just add additional parentheses here Perfect all right so now it's showing one which is what I want I want that project ID however if they've selected show all I want to show basically does not equal this is going to translate into our criteria which we'll be going over great so what I'm going to do is I'm just going to copy this formula and then we're going to make adjustments for our filter job so our filter job is going based on what's located in S3 so we're going to do this S3 and then if it's equal to show all instead of indexing project IDs I'm indexing our job IDs so I want to return that job ID and I'm looking for what I'm looking for our job name so we're going to change this to job perfect so that's going to be a job name so now if I've selected a job here I want that job ID to show here excellent so now what do we have lastly we're just going to use the same thing for our tasks so again pasting that in here we're going to change that to S4 here in both instances once again instead of the project ID we are going to change this to our task ID each individual task has an ID and we're going to change this to task name looking up that task name okay making sure that that's perfect looking good and then what we're going to do is just going to hit enter and then of course if we select a specific task I want to make sure the task ID here let's check that out first floor joist we want to make sure that's task ID 15 we're going to look at our task database here we're going to look at 15 and we want to make sure that it is first floor joist which it is perfect all right so we've got that completed let's move on I want to have some information I want to know the selected document row I want to add some conditional formatting I do have some conditional formatting already here so let's take a look at the conditional formatting and I've got a few rules so we're going to use manage rule I've got one rule that's for the selected row that selected row I want to show in the dark blue with the white bold font if B25 equals the row and then I have formulas for alternating rows even rows and odd rows making sure that there's a value in h and if it's an odd row I'm going to give it this light color if it's an even row we're going to give it this blue color this is for odd rows equal to one and this is for even rows so knowing that this is going to be our selected document row so let's call this selected doc row I also want another one for the selected equipment I spelled equipment wrong here that's just unlike me of course every video I spell something wrong sometimes I spell this entire title wrong and I don't even notice so this at least it's small so just an extra oh I didn't need that okay equipment needed I'm sure there's more so so basically when I change this 27 let's put in a row number there it's automatically that's exactly what I want and we've done exactly the same conditional formatting for this a so you see when I change this to let's say 28 we see that so basically when I make a selection on one of these assuming that there's a value in here I want that row to go here that's a selection change of vent so we know that that's working correctly next up I also want to know the profit percentage that's going to be helpful the profit percentage we're going to need that I'm going to put that here so let's focus on some of these formulas here first thing is let's start out with tasks here and move to jobs and then to projects so our estimated profit is simply equal to let's put in some values here let's say we have a sales price of $2,000 we have budgeted costs of let's say $1,200 and our actual costs are let's say a little bit over $1250 okay so what is our estimated profit it's simply equal to the sales price minus our budgeted cost so that's our estimated profit what is our actual profit our actual profit is equals our sales price minus our actual cost so we have in our actual profit so these aren't going to change those are formulas and so for each one we have that now likely what I've done here is I want to know all of the budgeted costs for every single task inside this so if I have job one I want to know all of the budgeted cost so here's a budgeted cost for let's say Flor m or something so I want to know all the budgeted cost so how do we know that I've got some named ranges that are going to help us so let's look inside the tasks and we see that we have budgeted costs we have the sales price and we have actual cost so I've created some named range to help us with this so that we can quickly add that up so we're going to go into the formulas name manager and we're going to go into task so we have a named range for actual cost task actual cost we see the dancing amps around the actual cost we have another one for task budgeted cost so that's going to help us so knowing those two we also have more information so let's look at tasks job ID so this is Task job ID and we have another one for task project ID very very important here so knowing those we can determine all the budgeted cost for a given job or for a given project so let's do that right now let's put in that we can use sum if it's a very simple sum if formula so equals sum if and what I wants the range our range of course is our task here and I want job ID so job ID and we're going to look where's our job ID located our job ID is located right here and what is that sum range since we're on budgeted here our sum range is Task budgeted cost so that means again let's take a look at this looking at all of the tasks in which the job ID is B6 I want to know the budgeted cost so basically it's going to look inside this list and if our job ID is one it's going to add up everything that's a one and it's adding up our budgeted cost for any job ID that's a one here so I also want to know the actual cost very very similar so let's go back in here I'm simply going to copy this and instead of budgeted cost it's going to be our actual cost so all I need to do is just update and our actual cost is here very good and what is our estimated profit so let's say we're going to sell this at let's say 4900 here and then what we want to know is what our estimated profit our estimated profit is simply equal to our sales price just like we did before minus our budgeted cost so our estimated profit is 2111 and our actual profit is also equal to our sales price minus our actual cost perfect so we have all that information next up budgeted costs these are for our entire project so that means all the jobs that are associated with this so now what we're going to do inside a task I'm going to look for projects so every project that's one we're going to add to every project that's two we're adding up all the budgeted or all the actual cost so to do that again we're going to use equals sum if this time what is the range we're going to use we're using task but this time we're using our project ID and where's that project ID located it's located inside here and what is the sum range we're going to use again task here our budgeted cost so these are all the budgeted costs for an entire project the actual cost is very simple again copying this moving down instead of our budgeted cost we're using our actual cost so actual cost here and likely our estimated profit let's say we bid this at $15,000 and then we can see our estimated profit is equal again our sales price minus our budgeted costs and then likewise our actual profit is our sales price minus our actual cost here great so we've got all formulas we're going to save that now what I would like to know inside the next one here our profit percentage is what it's simply our actual profit divided by our sales price so to knowing that we'll just use equals and what we're going to do is we're going to write our actual profit divided by our sales price and it should be already formatted as a percentage so we see that our profit is 47% which is correct very good I also want to add in a donut chart here inside this information so to do that it's going to of course be based on both our actual profit and our actual cost so I want to know both both of those so I'm going to highlight those and I'm going to insert I'm going to go here and we're going to insert a donut and it's going to look something like this we're going to bring it down here we're going to size it accordingly to make sure that it fits within the given area like that okay so we have our donut chart and we're going to increase that giving it a title I want cost versus profit so we're going to call this costs versus profit I'm going to select on the entire one and I want to make sure that all the text is given our default text color which is going to be this color right here so we have our text color and I'll make it all bold very good so now what we want to do is we want to add some information into this donut I'm going to increase it a little bit and I want to change the colors I can get rid of this Legend but I do want to add inside here some information selecting on here and what do I want to show well let's take a quick look inside here I want to show those data labels and I also want to show the categories here so we're going to click on the more options and I'm going to click on the category names cuz I want to show those so we're going to move those into some little bit of a better area here I'm going to select on this we're going to go into our border I don't want to show any lines on that and I also want to set some fill unique to that so if we see our actual profits to here I'm going to select on our actual profit and I want to give this a gradient fill here we're going to give it a little bit of a darker color here so I'm going to use it considering which is our theme here we're going to use this darker color here and then a little bit of darker color here something like this cuz I want to show this one here I want to show this color here so I like that that's going to be theme I also want to show our cost in a different color we'll use a light red again our gradient fill here and then we'll be selecting some red colors oops sorry that's off the screen a little bit here I can drag this over here so you guys can see what I'm doing here there we go so let's take a quick look in here I'm going to select on this is our cost color we'll use this and then this color here and I'll use this light red right about here so I like the way that that looks now what we're going to do is I'm going to select out and back inside it again and I'm going to focus on the dnut hole side so I'm going to redu that a little bit and things are looking pretty good here now what I would like to have is give it a little bit more of a nicer look so we're going to add in a bit of a shadow on here so we'll use the lower left here and I'll bring it not too much of a shadow next up I would also like to have a 3D format so we're going to add a little bit of a 3D format slight so this bevel here and then we can just lower that I don't want too much of a bevel actually I want to select this here make sure they both selected this the one where I want to make sure that we add that 3D here this bevel here so this is the one we're going to add so let's do this one here I like that one better so that's looking good we don't need that one it's almost no difference just a slight bevel in here bringing this down here and bringing this one down next up let's take a look at this in the donut we'll make it a little bit bigger here so we can see it not too big cuz we need some space for our labels there and also we need information I want to put a text inside there so we need space for that to show our profit percentage so we're going to increase the font on these bottles 11 to make sure if they can fit I'll go back to 10 it's a little bit big for that things are looking good now what I would like to do is I want to select inside ear and I'm going to insert we're going to use this text box here and I'm just going to put it right up here now inside this text box I'm going to put in our profit percentage and inside this text box I want to make sure that there's no spacing here so what I'm going to do is go into the text options here and I'm going to make sure that we're zeroing out the margin so 0 0 0 0 I'm going to Center that accordingly give it our text default and and uh make sure it's bold and increase it as much as it can go while it's visible so our profit percentage is here and in fact I'm just going to make sure that we have all the font colored I think that percentage is not and I'm going to then make sure that they all have the same font size next up I'm going to duplicate this using contrl D so I'm going to select on ittr D I want to put that actual percentage and I'm going to bring it right about here so this one's going to be linked to our profit percentage so I'm simply going to use equals and our profit percentage is located right here here and then I want to reformat that once again so 13 select on that twice now we want to again set our color and bold so we have our profit percentage things are looking pretty good I like the way that that looks next up what else would I like to do I want to add a background so page layout I've got a nice background that I've completed for this so let's go ahead and add that background I created that with mid journey and it's going to look like this so we have a nice background here that we've done we can then go ahead and add that back in there next up as we create these task we're going to be creating a bunch of shapes that go in here but what we need is a sample shape in order to duplicate so actually I've got one in the other template here so I'm just going to paste that one in I've copied it CU it's already formatted nice it's got a name called sample task shape it's formatted just the way I want it's just simply a rounded rectangle corner but I really haven't used the rounded too much so I kind of kept a square on this one now as far as the width and the height and everything like that that's going to be automated based on the distance so basically if I have a task that's going to you know go for 9 days or 5 days it's going to be however long it is and the height will be the same height as the given row so we don't really need to adjust the height but I just want to make sure that the text is there already so we've got that that's going to be duplicated we're ready to assign our macros to the shape so we've got a lot of macros so let's start assigning these macros to that so first one we've got add project now when we have both we have everything grouped we want to do is we want to hold down the control for both the button and the icon itself we're going to right click that and then we'll assign the macro I've got two open workbooks so I'm just going to set this workbook and this one's going to be called project ad so everything starts with project and then it's the project delete so this one's add new so we going to click okay next up is save that means save and update so again doing the same thing here also project and then this one's going to be called our SA or update here so we're looking for saor update next up is our project delete we need to be able to delete a project I've written the macros already otherwise this video would be way too long we'll be going over those macros and then next next up what I would like to do is add this going to be called project delete so that's the one likewise the same exact thing for job management here so adding those macros again you can quickly type in job and then this is going to be the add new this one will be used for Save and update it is the same macro that we'll be using for both save and update lastly is the job delete we need to be able to delete now keep in mind that when we delete a job we must delete all the tasks and the jobs associated with that so that's important when I delete a project I must delete all of the individual jobs and all the individual tasks associated with that so that's going to be super important so now we're on tasks so this is going to be our task add new clicking okay next up task save and update so keep that in mind then we'll be going over that inside the database and also how relative they are to each other meaning when I select a job I want to make sure that we are having all the tasks associated with that job so that's going to be a save or update next up and lastly for this section is going to be the delete so we want to be able to do task and then delete there's a lot of macros Associated that I created the last two days so see what I do for you go ahead and smash that like button if you do appreciate these I also appreciate you sticking with me on these long trainings there're so much to learn and not just to learn but you can make a lot of money if you do want additional help on these make sure you join our patreon or YouTube members platforms each week I create an additional video and an additional downloaded workbook on those plus PDF F codebooks and a whole lot else okay so we've got our macros assigned to all these buttons here associated in the icons we also want to be able to add pictures and documents to Any Given job so again I'm going to hold down adding the macro so this is going to be our job I think I've got job doc add picks and documents this is the one we want to focus on here and then I've got another one for add equipment so again assigning the macro you got to get the idea right now so right click assign the macro and then this is going to be for our tasks and we want to be able to add equipment needed on the individual task so task equipment add new next up as we're assigning these macers we want to be able to navigate through the month so we've got some buttons here so just going to zoom in here I want to make sure to select both the icon and the button here they're quite close together here so I'm going to zoom in to do just that ass signing the macro this is our previous month so they start with schedule so schedule previous month is the one that we would on this month both the icon and the button itself that's also going to starts with schedule and it's going to be called this this month which is right here lastly we want to make sure in the schedule again I want to make sure to get the button and the icon here and then we have this one for next month's schedule and the next month here okay lastly is the print button and I believe that's it for our assigned macros and button so I'm going to hold down the control I've got some additional buttons that were kind of hidden just and I'll show you those in a minute assign the macro and then what we have is schedule and then print for that so clicking okay if you do want to see all the shapes even the hidden shapes I'm going to show you how to do that inside our selection pane we can go inside I've got it available here or you can also go into page layout here and selection pane I want to show all there a few things that I have noticed I've got a pop-up calendar here we'll be showing you that and also notice I've got some icons here these were created before if I select on a specific document this will open up and I can delete these individuals and they've already been assigned macros and also this Del leading equipment so these three are the ones that I wanted to show you right here those were created previously those were hidden because when I make a selection on anything the selection change events going to hide those this shape here is an idea that I had and so I'll be playing with this a little bit in the patreon but for now we will just hide it and so that's going to kind of show today which is kind of nice all right so let's take a look so we've assigned the macros and now let's take a look at some of the selections so these are all shapes that we're going to be adding in each individual one of these is a shape that gets hidden so once we save it task or once we save a project or once we save a job these are Fade Out messages that are going to show up and then fade out so these are all on top of each other here of course as we save it they'll show and then they'll disappear so that's fine so we've shown all the shapes that are available to us on this training all right so what we're going to do now is I'm going to go over some of the selection change events that happen and that means when I make a selection what do I want to happen well let's start out when I make a selection on any of these job start or job date I want this calendar to show up now this date picker here that I've created we're not going to go into how we created it because that's beyond this training but let's go in some of the selection change events so what we're going to do is we're going to go into the developer if you don't have the developer you can customize the ribbon here and make sure the developer is selected inside that developer we're going to jump into VBA it's going to launch this and as you can see I've got two workbooks open in fact I'm going to close one individual workbook so we don't get confused or when I say we I mean me I don't want to get confused because that's pretty easy to do so I'm just going to close that other workbook on my other screen so now it's closed so now we just see that one and of course my amazing tool pack all right so here's what we're going to do we're going to focus on this sheet construction project this is the main sheet where everything's happening and as you can see everything has been commented out because while we're creating it of course I don't want anything to happen I'm going to uncomment it out Simply by using this block here it's going to uncommon out now let take a look at some of the selection change now the selection change event is right here I'm also going to uncomment this out here now selection change a few things going to happen when the user makes a selection change meaning they've selected on any cell we want something to happen I've got some merged cells but if the user selects any large number of cells I want to exit the sub out I do have some cells that emerged so there's three cells in one so I want to make sure that I want something to happen only if it's under four so there's certain buttons that I want to hide no matter what if they're visible the open dock button the delete doc button and the delete equipment button if any of those are visible equals true I want to hide them so those are the three buttons that you saw right here so we see that we have the open doc button we have the delete doc button and we have our delete so that means when I make a selection change on anything I want those to hide also the calendar itself we're going to make sure that we're going to hide the calendar so if the calendar we're going to check to make sure it's available this code is just going to hide it if it's visible in fact I'm just going to put it right up here with the rest to make sure actually in case it doesn't exist on your sheet what I'm going to do is I'm going to keep it down here that's fine if it's visible we want to hide the calendar that means all four of those things as soon as I make a selection change are going to hide so watch this I make a selection change and they're all hidden and that's exactly what I want to happen so now what I want to do is if the user makes a selection on e13 e14 I13 i14 M or all those six cells and that those are the ones right here project n or start I want to show that calendar so there's code that's going to show here or here on any one of these cells I want the date picker to show and so that's a Mac that's going to run it's going to check for the sheet to make sure that the calendar sheet is there that calendar sheet is right here to make sure that it exists we can hide this we don't need to see it if you want to hide it you can just right click and then hide the cell sorry it's off the screen but right click and hide it we don't need to display that oh I do want to show you one more thing actually on it if you have any problems with this like if you've imported Ed it and you had an issue with it sometimes there's an issue so select on this cell right here often times what I see is this thing's way out here all you need to do is just drag this to the previous day like that and then just double check and then just drag this one all the way over so keep that in mind that just double click on this one make sure it's the previous day one of these days I've got to get to fixing that but this is really cool that's the only issue otherwise so just drag those and make sure all the dates are set up correct and also when you copy this over what I usually do is I'll copy this and I'll copy this also into this there's an existing one here but I usually delete it and that means it's like a backup so the reason is if the user deletes this it copy this and it brings it over into whatever the active page is so it's kind of a nice feature how does it happen if I let's say I delete it okay it's gone now right so if I make a selection the code is going to actually copy it over so you see it's back what it does is it copies it from this sheet and brings it into the current so it's kind of nice so basically we want that calendar to show if they make a selection on any date it's going to automatically put that date in here very very cool on that so that's the date picker now we've got on document selection meaning if they make a selection on h26 to i99 and that's going to be here but there's no value in here so I want to go over this code with you when there's actually something here to go over cuz right now there's nothing here so what we want to do is we start with project when do we actually load meaning I want all the project details to come in here we've got several to select from so when user makes a change they change whatever's located in E I want want that to load up here we know if it's a correct project and so that's going to happen on change event when user makes a change to E4 something's going to happen so if we focus on our worksheet change event E4 is what we're making I want to make sure that E4 is not empty if I want to make a change I want to make sure that B5 is not empty remember B5 is critical that's the project ID that's based on whatever the project they've selected so if they make a change I want to make sure that B5 is not empty if it is empty we're going to let the user know to please please make sure to select a project from the dropdown list if it's not empty what we're going to do is I'm going to take that project ID and I'm going to put it directly inside B2 so B2 is simply equal to what is in B5 then we're going to run a macro called project load that's the macro that I'm going to go with you now so if I double click on here which is going to act as a change event and I hit enter it's going to automatically load that project so it loads all the details and that's the first maer that we're going to go over called project load inside our modules I've got modules for projects I've got a module for Popup count job macros job documents admin which is almost nothing and I've got tasks and a schedule so let's take a look inside the project macros and the first one I want to go over through is called project load this is the one that we tied to the add new button we'll go over that second but this is the one that you saw it just happened right so we made a change on here let's show it again so I made a change to Kitchen update we'll select the other one which is the new house and we saw all the details load so how did that happen so first of all let's zoom in a little bit back back to normal which is 100% we can bring this up here so we can see that and now what we have is the details loaded in so let's take a look the first thing what we want to do is we want to clear out a bunch of sales right all the sales that are associated with any jobs or tasks we must clear out if I select a brand new project all the jobs that was here I want to clear all the task details with here I want to clear out any job ID that's here I want to clear out any task ID that's here so I want to make sure that all that gets cleared out so B6 must be cleared out B 10 much the only thing I don't want to clear out is B2 cuz that's critical for the one that's updated so B6 B10 I also want to clear out any selected document row or equipment row we want to clear that out and then all the cells that are associated with projects except for the formulas all the cells that associated with jobs and all sales associated with Tas so we're simply clearing all those out so that's the first three lines of code clearing out all those cells I also want to make sure that all those buttons like Open document delete document and delete equipment remember those three buttons I want to make sure that they're hidden too next up what I want to do is I want to make sure that we actually have a database row it's very critical that once we change this project ID that database row has to be available there in B3 that database row is very important because when we go into projects I need to know what row to load all this information in so we need to take all the information and I need to bring it into these cells right here so to do that I must have a project database row if for some reason that is empty I want to let the user know to please select a project to load we're going to select on e4 and we're going to turn on application screen updating very important because I've turned it off up here and we're going to exit the sub as long as there is a row associated with B3 I'm going to put that into a long variable called project row then I'm going to use another long variable from 2 to 11 we're going to run a loop why is that important where does it come from well if we take a look at all of the data here and we're going to be loading it in so when I bring it from the database and I bring bring it in here obviously I don't want to bring it into these CES cuz these are all calculated right I don't want to disturb that so only here I'm only bringing this data in here so if I take a look in projects and I look at all of this information is exactly the same all the way to project end and if we take a look at the column here we see column this is going to be column 10 so the last one is sales price so sales price is the last one the user is going to edit so we see that it's going to start at two which is COL two all the way to 11 so we can see that it's 11 so what I want to do it's always in the same order which is nice so I'm going to run a loop from 2 to 11 whatever's in the associated row and the column here I'm going to put directly inside this row then this then this because it's all in the same order everything's in the same order so the order here is exactly the same as the order all the way here and so that's really really important because I can use data mapping to do just that so if we notice this is in column two right we notice that this is is in row six so to get the difference between column 2 and row six is we simply need to add four and that's exactly what we're going to do inside here so what I'm going to do is I'm going to take the information inside our project database inside our project row and our project column now our project column is going to change from 2 to 11 so now our information is going to come always into column e except for the notes field so that's last so we're going to start out at E6 E7 E8 go all the way down to E15 so if we know the column are going to start at in two but we need to get that E6 we add four so this is going to be E6 then E7 then E8 so simply using data mapping to last the only one that doesn't quite fit that is the notes because that's a larger field so that one will'll do manually and that's pretty simple all we need to do is we just need to take whatever's located inside column P which is our notes and bring it over into right in here inside c21 so we've done just that here c21 is equal to p and whatever the project row is then what I'm going to do is I'm going to run a macro called get unique jobs now that's very important why is that if I've selected a project here this select jobs drop- down list only must be the available jobs for the given project if I were to select a different project here oops let's do different project here kitchen update then the select jobs must be only those syn replacement ceramic flooring notice through there's two jobs associated with Fred's Kitchen inside Lisa's new house if I select here we see that Lisa is foundation and framing so there's two jobs for Lisas and so they're very different so what I need is I need a unique list so that list is based on the DAT of validation so what we're going to do is that data validation we briefly went over it's called project jobs and it's right here so here's what I want to do so that's going to come inside our jobs database so if I take a look back inside our jobs database right here what I want to do is I've created a range for whatever appears here what I'm going to do is run an advanced filter whatever project ID notice this is B2 so whatever project ID is located right here let's take a look back here B2 right here whatever that I'm going to run an advanced filter I want to know all of the jobs for the given project so if I know that means I want foundation and Framing and I want to know the job ID here and I want probably the job name and probably the row that's associated five and 7 and I want that information to come right here so I'm going to create an advanced filter so I only want to know only those jobs for the given project ID and I want the results to come right here then what I'm going to do is I'm going to create a named range based on the results here so if we take a look at that inside our formulas and name manager that's the one you just saw and it's called project jobs I believe project jobs here so if we take a look here under project jobs we see that I've got a name bring using the opposite formula based on whatever jobs are going to show up here here if I know which jobs are going to appear here creating that named range that means whatever shows up right here is also going to show up inside the drop down list so that means when I make a change to Lisa's house we're going to take a look back inside here and we see that it's completely different oops that's the same one nope Lisa's jobs so we have here and then let's go to Fred's Kitchen so now we go back into jobs and we see different so now there's very different however the named range is going to contain those two items right here here so all I need is a macro that runs an advanced filter based on the project ID and puts the result right here and that's this macro right here called project get unique jobs basically I want to know only those jobs for a given project and that's right here that's the next macro and it's very very simple we determine the last row based on our jobs database since our criteria is automatically link to B2 all I need to do is determine the last row based on column A we're going to run an advanced filter and the criteria is going to be W2 through W3 the results are going to be Y2 through AA so we going to bring that right over here determine the last row W2 through W3 is our Advanced filter based on the Range A3 through R our results are going to come Y2 through a A2 so our results are coming directly in here and that's all that's all we need to do we don't need to do anything else inside the macro now I want to run this macro every time we make an update to a job or make an update to a project so I want to make sure that it's not in the same that's why I didn't put it here even though it's a few lines of code because I may want to run this anytime we update that job if I update a job name I want to run this why is that because I want that new job name to be available in the drop- down list in fact let me just make sure I don't know if I did that or not but it is important so I'm going to copy this and when I go back into the jobs and I make an update and saving those jobs here job open document those are job documents sorry job macros here add new job save and update here I want to make sure job update job names yeah yeah it's here I want to make sure cuz that's very important that we update when we make a change to the jobs great so we know how we get the unique list let's go back into our projects here CU we had one more Macer that I wanted to go over as we load our projects here scrolling up here's the load went over everything else we went over this let's just put a note here get unique list of jobs for the given project because that's very much what it is next up just turn application screen updating on to tr that's all we need to do so it's going to load those jobs the one we did Skip was just creating a brand new project so when I click on our construction and add project that is a Macker that's tied to it that's the first Macer in this module called add new simply we're going to clear all of the job details all of the project details and all the task details so everything's going to be cleared here very very important I want everything cleared out including the project ID so we're simply clearing everything so when we click add project everything gets cleared out so it's very important next up probably can fix this too because it doesn't look so beautiful when we have it so loading the job happens on a worksheet to change so we see that next up what we'd like to do is I'd like to be able to save or update that that is the next Macer that we're going to go over and it is called project saver update so we want to make sure that there's some required fields that do get filled out E6 of course is our project name if the user has not entered a project name we want to make sure that they do so we're going to put in please make sure to add a project name before saving we're going to exit that sub I also need to know if this is a brand new project or is it existing B3 is going to tell us if it's an ad project B3 is going to be empty however if it's an existing project let's say we can just type in here Le say then B3 is going to have a value so if B3 is empty we're just going to put in new project and we assign a project row that first available row inside our project datab base we want a brand new project ID remember we've got the next available project ID I'm going to take this I'm going to place it in B2 that's going to get us our next project ID I also want to take that project ID only for new jobs and I want to put it directly inside column A so I'd put it right here so I want to make sure that we add that inside column A everything else all the current items are going to be whether we are adding or updating it's going to be the same now keep in mind when we're saving it we do want to save all the cost and profit we do want to save it so in this case I'm going to run a loop I'm going to run it all the way to column here you're going to see 15 so I want to make sure we're running all the way from 2 to 15 as we save the data whatever's located all the way down here and again we're going to be adding four because we're starting in row six and going all the way to row 19 we're starting in column two and going all the way to column 15 so we simply need to add four so to do that we're going to run a loop from two to 16 and we're going to then take whatever's in E and the project column plus 4 and we're going to put it directly inside the database lastly the notes is excluded on this so we're simply going to take whatever is in c21 and we're going to place it in column p in the project row we are going to update the project names list and I'll be going over that the project names I want to make sure that we have individual project names so I need a macro to do that remember we need a macro for our project I said I was going to go over that so need a macro that's going to update those names and place them here as our list of projects so to do that we are going to add it right here update project names I'm going to clear all the content located here clearing everything out here then what I want to do is determine the last row of our data then simply all I need to do is just take our project names here that are located in column B and place them directly inside column A so that's going to give us our unique list and it's going to include that show all so to do that aa3 through AA in the last row minus one why is it minus one because our first row starts here on Row three our first one starts on row four here so we need to subtract one so it's going to take all of that and it's going to bring over so let's just put a common in here bring over project names for let's say drop down list named range so that's good I would like to sort them so we can have them sorted alphabetically but if we only have one row of data if the last row is less than five that means we only have one row of data so we don't need to sort if we have more than one project we do want to sort them alphabetically based on aa3 and we're going to use as sending alphabetically and our range is going to be A3 through a A in the last row so simply we're going to sort names a to z all right so that's it so that's all we need to update the project names remember this was important we need to create this list because that is going to be used in our filter right here so we need that here very important that's for that filter right here okay continuing on we want to focus on here the only one I left out was this Fade Out message and that means when I save a project notice the Fade Out message when I click save it's going to show this project and then it's going to fade it out I've got more than one button there so if I were to save a task and save this both of those will be disappeared when I show them to you so our first one faded out which is our project which is exactly what I want I've got two more buttons one for job and one for task that'll Fade Out as we run the Macer to save it so that's our Fade Out it's just simply going to take a shape fade it out slowly over time pretty simple on that one project delete this is kind of important if I delete a project I need to delete all the jobs and all the tasks associated with that so to do that first want to give the user a message are you sure you want to delete this project along with all the jobs and tasks associated with it so focusing on the construction if B3 is empty that means it's never been saved before we can skip all of this and just go to not saved down here and all we're going to do is update the project names and add new so it's going to clear everything out however if it has been saved basically what I want to do is I want to determine the project row which is in B3 in a long variable I want to R an advanced filter based on jobs I want to know all of the jobs associated with this given project so what we're going to do is we're going to take this jobs going to run an advanced filter our criteria is here our results are going to come here and our database rows here so our database row is going to come from AA then I know what rows to clear out so I'm going to clear out row five and clear out row seven and then I'm going to Resort it so that's exactly what we're going to do we're going to run it our Advanced filter is going to be Y2 through A2 determine the last results row if it's less than four that means we have no jobs associated with this project we're going to run a loop from three to the last result row and then what we're I'm going to grab that job Row from AA as I mentioned and I'm going to clear the contents of it and then what I want to do is I want to Resort all the list and that's just simply going to clear out those rows that have no data in them so we don't want those inside our database so the sort will simply clear those out but if there's only one row of data we don't need to sort so we're going to check on that we're simply sort in all the data based on A4 so we have our job ID here we're going to Simply use A4 so as sending so it's going to basically sort all these getting rid of the any empty rows and so that's it we're going to use the exact same process for the task database we can move a little quicker task database I also want to determine all of the tasks associated with project ID that's our criteria here x23 X3 our results are going to come in right here AA through a I'm going to look through all the rows I'm going to clear them out and Resort it so that's exactly what we're doing in here running Advanced filter determining the last row based on AA we're going to take our task row based on what's an AC notice AC is it and then we're going to clear it out and then Resort our tasks and then last thing is we're going to take that Row from our project database here's our project database and we're going to clear the entire row out of that so we're simply entire row delete we're going to update project names and add new so that's it for projects we can move a little bit quicker cuz jobs is very very similar and it's kind of a nice feature that things are very similar so first thing what I want to do is load a job we saw how this job is populated if I make a change to I4 then I want to load the given job and I want to make sure that we actually have an entered job ID here if the job ID is correct because that's going to be based on the name I'm going to take that job ID I'm going put it inside B6 so remember that's all going to start when we make a change to I4 so let's take a look at that so we're going to go back inside our construction project and we're focused on on job selection excuse me I your name change since we're really adding oops name change so let's change that to name change because it's a name change that will really focus and last thing is of course task name change so now that we have that and we're going to make a change I for it's almost the same we're going to check to make sure that B9 this is our enter jav ID make sure that it's not empty if it's not empty we're going to take whatever's in B9 and place it directly inside B6 so B6 is going to take on whatever's in B9 and we're going to run the macro called job load so when I make a change it's going to load that job up just like that great so let's take a look inside our job macros and the first one we're going to go over is job load we can move a little faster because there's so many similarities we are going to turn off application screen updating we're going to clear the job and the task details we're not clearing out the project details but we are clearing out any job details and any task details we're going to make sure that all these buttons down here these ones we want to make sure that they're all hidden and then what we want to do is I want to make sure if B7 is empty B7 is our database Row for the given job we want to make sure that there's a row if it's empty there's something wrong with the job and we need to let the user know next up turning on application screen updating exit the sub we're going to determine the job row as a long variable this time we're running loop from 3 to 12 we take a look inside our jobs database we've already Associated our project and our job ID I'm going to start it out right now on job name all the way from three and we want to load all the way up to the sales price so we equal column we see that that is column 12 so we're going from 3 to 12 we're going to take the values in here and we're loading them directly in so we see that the job name is in column 3 so if we take a look inside our project we see that the job name here is located on row six so we need to add three this time we're adding three previously we added four now we're adding three and so let's take a look at that so we're simply taking the job column we're adding three and it's going to be column I we're taking the information from the job database we're simply adding it in here also we need to add the notes so we're going to do that separately now what we need to do is get unique tasks I've mentioned that before every time we load it I want to know all the tasks Associated for this given job so that's Foundation cement Po and Foundation however if I select a different job such as framing we want the tasks only associated with the framing here so once again this time we're basing it on our job ID our job ID is located here so that means inside our tasks I want to know all the tasks associated with a given project ID so that means take a look back in here if the job ID is four I want to know all the tasks associated with that so looking back in here we see that our job ID is four I only want those given tasks for that so we have our job ID here it's linked to B6 so if I run an advanced filter only for this job ID and I have the results come directly inside here we can have that so I know the drop down list for that is right here let's familiarize ourselves with that all we need to do is go in here take a look at the task if we go into the data and we go into the data validation here and we see that it's job tasks using offset I've got a named range just for those job tasks so it's that advanced filter that we're going to run it's very very similar it's called get unique task inside our task database we're going to run an advanced filter based on our job ID why Y 2 through Y3 and the results are going to come AA through a so that's exactly what we had so we see that actually should be y it's not necessary in other words what I've done is I've used both the project and job ID however job ID is enough in other words there's only one job ID job ID number four here's job ID number four is always associated with project id2 so we don't necessarily need to create both Advanced filters this is enough here here meaning I only really need the job ID so that's fine so we can keep it minimized so it's really why that's why I said Y2 is okay both are okay but it's really not necessary I want the results to come right here AA through AC that's it so once they come our named range or using offset takes over and it's going to populate that drop down list and that's what we have exactly right here very good so that's under our job unique task job save and update very much the same I want to make sure that there's some required field if B3 is empty we want to make sure that the project is safe the only one thing I want to make sure is if I save a job I want to make sure that I've already saved a project I can't save a job without making sure that there's a selected project if I add a new project and I try to save a job before saving the project it will create an issue for sure so we really want to make sure that B3 has a number that means that project has already been saved so that's the first thing we want to do if B3 is empty we're going to let these you know please make sure to add and save a project before saving a job we're going to exit out I want to make sure that the job has a name to that so I6 must also not be empty if B7 is empty we know it is a brand new job b7s our job database row so if it's new then we're simply going to add in job Row the first available job row we're going to get that next job ID from here we're going to place it inside B6 and then what we're going to be doing is we're going to put that Row in the last available column so inside our jobs I want that row to go here this is only for new jobs and we're going to put the job ID here and the project ID is going to go in column B so we want to make sure the project ID is going to come in column B so that's going to be regardless if it is a new or existing in case we decide to change the project so we can do that if it's an existing all we need to do is get the job Row from B7 then again we're going to do that reverse Loop this time we're taking everything inside inside this range I and we're putting it into our job database and then the notes are going to get done then we're going to update our unique jobs we're going to update our job name so we're updating our job list and we're running a job Fade Out message so that happens when we save a job so we're going in here and if I decide I want to make a change to here and just click save it's going to save that job and our Fade Out message is going to go and we see we only have available is the task saved and that's lastly the task saved now you get the idea of it so task is almost the same I'll move a little bit quicker I want to get to the scheduling so as we move inside here again our last name on task name change again we're making sure that they've entered something correctly we're going to run a macro called task load so all we're doing is taking the ID and we're putting it here and we're running a task so that means if I want to load a task on change of M4 I just need to enter that and it's going to load those task details right here okay so that's what happened that's going to be low task so low task is pretty much the same thing everything's pretty much the same adding the new task here clearing information out making sure that we have it loading the task clearing some information out hiding our button sets running our Loop making sure that we have a value inside b11 we want to make sure that we have a task load and we're going to select M4 and we're going to turn on application screen making sure that we have a task to load in case there's no value task row is in b11 I need to have that database row very important that we have that database row located in b 11 our data is going to come directly from our task database I need to know what row is associated in order to load all the data this time we're starting in column four we're going to load in all the way up to let's take a quick look here all the way up to our actual cost so this time when we load it in it's going to come all the way to our actual cost so equals column so our Loop's going to go all the way to column 15 and it's going to start in column four so 4 to 15 is where our Loop is coming from and and of course it's going to go directly into rows six all the way through 17 so we're simply adding two onto this one and it's exactly M and task column plus two because starts at four and then it's going to add in all the data from the task database and of course the notes then we're going to run a macro that's going to load all the equipment and we're going to turn on application screen updating saving our updating is just the opposite of what we've done so there's nothing new the only insurance I want to make sure when we save a task I want to make sure that the task has a start date it has an end date I want to make sure that it has a status because when I save a task it's automatically going to populate the schedule that's what we're going to be going over next so we want to ensure that it has a task name a status a start and end dat those are very important so we're just going to ensure that the task has all those values we're going to turn off application screen updating then what we're going to do is of course if it's a new task we're going to do a bunch of these things if it's an existing task we simply need to assign the row so that's kind of the same thing we're going to set the project ID and the job ID remember I need to set the project ID and the job ID inside our task task ID is going to go in a project ID is going to come in column B and the job ID is going to come in column C so that's very important for those tasks all right continuing on so we're going to do all of that the notes we're going to update our task names just what we have we want to make sure that we have update the task name very important that we update these list of all of our task names because it is going to be available inside this drop- down list here so we want to make sure that we have all of that available okay so I just ran a macro our change event automatically populated that that's exactly what we're going to go now so how did that happen well that happened on change event of S2 or S3 so we've got a lot of change event it also happens on task save that means there's a macro that's running called schedule refresh that schedule refresh is every time we save a task it's going to run every time we make a change to any of the fil it's going to run so let's look inside here and we see inside a construction project here yeah that's what I want and then what I want to do is I want to show you our change event based on the schedule change so if the user makes any changes to any of these cells S2 through S4 W2 82 or at2 those are any one of these cells that schedule is going to automatically refresh and that you saw that happened there so how did that happen let's go into schedule that'll be the last marker that we go we're running out of time but I want to go over this with you so to see how we made that all right let's take a look inside this we're going to focus primarily on the task database basically we're going to run an advanced filter based on some criteria and we're going to use the data so this is our criteria here this task ID is going to be based on what's located in B20 remember that we ran some filters based on 18 19 and 20 the project ID the filter job ID and the filter so we need Advanced filters based on this if I want to focus just on an individual project I would put that ID number here so that we need over here inside our task we have two rows because we're going to need two rows for the date so that's why we're using two rows because I want to know any task that falls in the given month so that's any task that has a start date within the month or any task that has an end date within the month so I want to make sure that both of those are over so their task ID the project ID and the job ID are all based on B18 b19 and B20 so we have that there the task status is going to be based on what's located in at2 is it all statuses if it's all status I want to show every task that contains a value so nothing that's empty and that's going to be based on at2 so if we take a look inside at2 we scroll on over here and we see here that's based on all statuses if it's a given status if I want to know only to do then we have the filter that's going to show only that and if we take a look inside here and back inside our task we see that only task statuses to do and we simply have a duplicate of the formula in both of these cells so that means we're only going to run Advanced filter based on only those that are to- do so likewise type if I only want to look at a specific type I can look at a specific type here so if I only want to show very specific type I can do that I can select Foundation if I want to show that I can do that as well okay we'll keep it at all types for now and also what I want to do is all statuses let me just check that uh let's see roof frame I don't think I have that under to do I'm going to make sure that criteria is correct so I have no roof framing on here but I do want to make sure so roof framing it does look good but we don't have anything for those dates but it is correct the type is roof frame very good just don't have any data on that but it is working correctly all right so we have that oh I see the problem 1 2 3 4 5 this involves five cells however if we remember correctly if we look at our project here anything that's greater than so be careful with this and if I make a change here worksheet selection change greater than than four but if I have a worksheet change event that's greater than five I want to make sure that we're automatically refreshing this based also on a L2 so I need to add al2 al2 so perfect just add that one in there those are change events based on that cell so let's double click this and it should show up all right very good so we see that that also changed okay so that looks good all right continuing on so we've added that in here so we also want to make sure that criteria once we have that the results are going to come through here then I want to sort them very specifically once I have the results come in I want to add some information I've got the task name inside here so we got the task name but I don't have the project name and I don't have the job name I really would like both of those now I guess I could have put those in the original database here but if the project name changes or the job name changes then I got to update it here also but the ID will never change so it's kind of a nice if I just bring the ID in here if I know the project ID or I know the job ID I can use a formula to extract the project name or I can for the job name so to do that I can use a formula so I've done just that I put a formula up here we're going to index that project name and I'm going to look for whatever is in as3 I'm going to index it based on the project ID so it's going to return the project name likewise I'm going to look for the job ID here and I'm going to index the job name based on that however notice there's a blank here right because that data is not in here so what I need to do if our results can a blank and it's important that it's blank because it's a formula all I need to do is determine the last row and then copy this formula and just bring it down here so we can say all the formulas get brought down but it's very important that when your results contain empty your original data must also contain empty how do we know that I just need to make sure my original data goes all the way to column t t is a blank header row and that's fine so that's what we're going to do inside our Advanced filter when we run it we're going to go all the way to con our results contain the blank I'm going to determine the last row I'm going to bring down these formulas and that's nice because I really need to have this information once I have the information I want to sort it based on three different sorts the first is based on Project name the second is based on job name and the third is based on the start date and why is that important because first I want the projects here then I want the jobs then I want to sort inside the individual tasks I want them by date so they graduate like this the first one the next one next one so it has this look and feel that's exactly what I want and to do that we're going to run three sorts so that's exactly what we're going to do then I'm going to go through the data here and I'm simply going to then duplicate our sample shape which is right here for each one and then I'm going to place it in the right row and in the right column and at the right width and the right height so let's go over that right now inside our scheduling module here which is here we're going to turn off application screen updating and we turn off events that's going to make things a little quicker as long as we make sure to set both of those true before the macro exits first thing what I want to do is I want to clear everything now I've stored something in a hidden row I haven't used it yet but I might use it eventually so if we unhide this if we look in column o I have the individual rows that are associated with the task those rows can come in handy in a little bit future right now we're not using them but I'm just storing them there so basically if we take a look inside the tasks we see that the associated database rows are also stored inside here I think that can come in handy next if I want to add some more features it's nice to have those so we're going to then hide those great so we want to clear out everything from o all the way through S and down so I just want to delete everything that's in here so we're going to do that here o06 through S on a large row clearing all the projects jobs and tasks here then what I want to do is I want to set the month start date I want to know the beginning day of the month remember we've got a named range called month start so if I know the month start here going to use that named range month start and we're going to set it to a variable called month start date this month start date is already a date variable just like month and task start and task and are all date variables so what we want to do is I also want to determine the month and date now there's a lot of ways to use it but one of the easiest ways is to use the function end of month it's an EO month function that we can use inside VBA as well it's a worksheet function we can use inside and all we need is the first day of the month and then a zero zero meaning no months forward W or no months back if I wanted one month back I would use minus one if I wanted one month forward I would use just one so this is the last day of the month I'm going to put that into a variable all right so next up what I want to do is any shapes that are here already I want to clear them out every single shape contains the word task item and then I've got the task ID so if you see this is Task item 9 task item 13 and so on and so forth I want to make sure that we are going to delete every single shape that contains the string task item so to do that're we're going to run a loop this is the task shape this is the shape variable task shape we're going to check through every single task shape so for every task shape inside our sheet here we're going to check using the instring command if the name of the shape contains task item then we're going to delete it so we're simply going to run a loop next up I'm ready to run our Advanced filter so with inside our task database which we're already in we're going to determine the last row the last row is based on a so if the last row is less than three that means but actually I shouldn't do that I've already turned off application screen updating so I have a few ways then let's go to end macro so the reason is I don't want to exit this sub because I have application screen up didn't turn off that's not good so we're going to go to in macro so I'm going to go all the way down here I've already got no tasks here so let's do that we're just going to go all the way up here go to no tasks because I don't want to exit so that means it's going to go all the way down here then it's going to turn on application events and application screen upd that's very important I don't want to skip that so we don't want to have exit sub without doing that now that we've got that what I want to do is I want to continue we're going to run that advanced filter here so to do that we're going to determine the last row A3 all the way through T remember I said we had to go through T which has that blank column header which is what we need and of course our criteria is going to be right here from AI all the way through ao4 so ai2 all the way through ao4 and our results are going to come here in a r all the way way to ba so that's where our results are going to come next up we're going to determine the last results Ro based on column a r based on that task ID which is located in 18 determine the last row if that last row is less than three we're going to go to no tasks that means there's no data what we're going to be doing is I want to then bring down our formulas notice we've got our project name and our job name formulas I want to bring down those formulas for all the rows Associated to do that we're simply going to take what's located inside right here au1 through av1 and we're bringing them down the formulas all the way through au3 through AV and the last results row so bring down our project and job name formulas okay great so now that we've done that I also want to sort remember I said we're going to sort by three times we're sorting by project name we're sorting by job and start date so the first one is project so projects are located on Au our jobs are located on AV and our work start day is located on Ax so we're going to run sorts based on those three so those are the three keys au3 av3 and ax3 and it's always going to be ascending now what we're going to be doing is we are going to set that range is going to be AR3 through ba in the last results row and we're going to apply that and run that sort once that sort is run we want to set that schedule row now we're ready to bring over the information into our schedule I need to know that initial row is six that's going to where we're going to put put our first project name then we're going to put our job name then we're going to put the tasks associated with that data so to do that we are going to set that initial row schedule row six I also want to set the icon to 125 and that's going to be this little triangle how do we know that that's number 125 if we insert a symbol here and I look for that triangle somewhere around here I don't know where it is so I'm not going to spend too much time looking for it but if we find it we're going to see if we select anything we're going to see that let's just choose this one we're going to see the winging number the number is going to be here that character number is here and that's how we're going to know what character it is so it's character 125 that is our icon so once we have the icon I want to set it here maybe eventually I'll add expand or Shrink but I kind of like the way that looks uh it doesn't have any purpose now other than the looks we're going to run a loop for all the results so I want to run a loop from three to the last results row because we need to add those as shapes we're going to grab all this information the task ID the project the job ID the name the project name all this information task in the status so all that's very very important now if we take a look inside our task row is correct located in B8 that's correct I want to know the row also I also want to know the status row notice we have a status here completed in progress to do but I want to grab the color the color is very important so inside our admin screen here I want to know the color that's associated with that because I need to know what color the color to shape the color is located in column C but I want to get the row so what I'll do is I'm going to find what row it's on and then I want to look in column C and it's going to tell me the color of the shape should be as long as it is found so what we're going to do is we've grabbed the status already here from AZ so we've grabbed the status once I know the status from our tasks I can then use the find to look it up so we're going to look it up inside our admin screen inside a named range called status and I'm looking it up status here usually I change it works okay but normally I like my named ranges to be different than my variable so normally I try to keep them different but it didn't affect it so what I want to do is I want to return the row I want to look for it I want to turn the row as long as it does not equal zero the status color is going to be looking in column C and under the status row and I'm going to grab the interior color so it's going to be our status color it's kind of obvious so now we know what color to color the shape now I want to know if it's a new project as we Loop through these if we're on row six if I'm on the first row I know it's going to be a project I know the first thing I'm going to start out with a project how else do I know if it's a new project in other words I only want to put the project name here if it's a brand new project like I've got two projects here so how do I know what I'm going to do is I'm going to look in tasks and I'm going to keep looking down here and let's say we get to this if we're on this row if we're on row 14 I'm going to look at the previous Row in column A you if it's different then I know we're on a new project and then I know for sure to do two things one I've got to take that project name and I need to put it directly inside column q and I want to add one more row so that's exactly what we're going to do if it's on the first row or the project ID oh we're using project ID sorry not project names so what I'm looking for is the same so in other words I'm looking for the project ID which is here if the project ID here doesn't equal the project ID here then I know it's different so project name or project ID both will work so we're going to check the project ID is not equal to what's in as and the result row minus one so we're looking at the row below so new project ID then what do we want to do I want to take that project inside column P I want to put the icon so if we take a look inside here column p is going to take on the icon column Q is going to take on the project name and I'm just going to confirm that the font is calib for Q and the reason why that is is because this icon is a different font it's wingdings and then I'm going to increment the scal of row one because why there's nothing that's going to go inside that so I want to make sure that we're adding a brand new row great so now what we're going to do is we're going to check for new job so if we look back in the tasks and here's our job ID so we see we've got job 2 to so if we get here if I get to row 14 and I look at the previous job I see the job is different if the job is different I want to take that job name and I want to put it directly inside column R here and I want to make sure that we put that icon in column Q so again we're going to do that if the schedule row seven obviously in seven we're going to have a project for sure or the job ID does not equal what's located in a in the result row so we're focused on column A now I'm looking inside here if I see that the job ID is different than the one before I know to take this job name located right here and place it directly inside column R here so that's exactly what we're going to do we're going to place that here's where we set the wingdings font so here what we're going to do in column Q we're going to set the icon we're going to set the Q to wingdings 3 in case you're wondering insert just in case you're wondering symbol it's a wingdings font so wingdings three is where we found it so wingdings three and that's where we found that triangle here so we know that I think it's one of these here so that's what we're using I think it's a smaller one but it gives you an idea of where we got it from this one here 125 so that's where you would find it we know it's a winging so I want to ensure that this is the wingdings font if I select on it we see that it's wingdings 3 so we're going to ensure that it's the right font so we're setting it to wingdings 3 and in column R is going to take on that job name and we're going to increment the schedule Row one okay so now that we know how we're adding our project names we're adding our job names and the icons now it's time to add in the tasks as we move on so we are going to set the task name we're putting that in column s and column o remember that hidden column is going to take on that task row so the row is going to go in that hidden column oh okay let's just put that as hidden great before my voice gives out all right continuing on now what we want to do is I want to focus back on this sheet here I want to know where's the starting column if the start date this start date here is less than the month start then I know that our column is going to be First Column so how do we know that if the task start is less than or equal the month start date then our start column is 20 why 20 where did I get 20 from if I just select inside here and I do equals column we see that this is column 20 so that's our starting column that means if the task starts on or before the first of the month we're going to set that start column as 20 next up else what is our start column let's say it starts here on the third if it starts on the third I want to Simply subtract our start date minus our month start date plus 20 so now else our start column simply equal to the task start minus the month start date plus 20 so that means if it starts on the 3 we are going to say okay our test starts on the third our month starts on the 1 we're going to subtract it that's going to be three plus 20 so we know it's going to be on column 23 what about our end column if our task end date is greater than the month date so let's say like for this one you see this one if we take a look at this I'll select on this we see that this task ends on the next month so that means the task end date is greater than our month date so that means our task end date is greater than the month end date then our end column is equal to the month end date the last day of the month minus the first day of the month that's the number of days in the month plus 20 so that's going to be set at our last column so we know our end column but what if it's not what if it ends somewhere inside the month all I need to do is take that end date subtract the month start date and add 20 so setting the end column now we know the start column now we know the end column now what we're do is we're ready to duplicate I'm going to take this sample shape called sample task shape and I'm going to duplicate it so the shape sample task we're going to duplicate it we're going to give it a unique name called task item and the task row so we're duplicating that shape and now we're going to work with it so with shapes I'm going to set the left position based on the schedule row we've already determine the schedule row because it's we're incrementing it and I want to know the start column so that's going to be that left position the top position is going to be based on the same location but the top position plus one so it's a little bit less than the top I also want to set the height is going to be the same height as the cell that we're located minus two so it's slightly less than the height of that row next up what is the width the width is basically the range so let's say we have our start column and we have our end column so whatever the width of all of these columns is the width of our shape so we use a range for that so it's the range of our starting point which is our starting column that's cell plus the range of our end column so we just set the width so whatever the width of all those columns is the same width of our shape what text do we want to put in that shape I'm just going to put the task name so task frame text range text equals the task name setting the task name and the color as long as the status color is not equal to empty this is a string variable then we're going to set the fill for color of that shape to the status color we're going to sign a macro called schedule task select that is the macro that we're going to go over next and we're going to increment the row so that's all we need to do and we're going to turn on application enable events and Screen updating now task select relatively simple when I select the task what do I want to do well I want to take the task ID which is Task ID 11 so I want to remove the words task item I want to take that task ID and I want to get some information so I need to know excuse me that's the task row not the ID yeah that's what I want sorry 11 is the row that's Associated very very important so let's go inside here 11 is the database row why is that database row so important that database row is going to give us a lot of information I could use the select row so let's say it's on row 11 so we know we just go look in row 11 I'm going to look right in here and that's going to be very important I want to know the task ID everything's here that I need the task ID the project ID and the job ID all that's very very important why is that important because I want to then take it I want to put the pro project ID here I want to put the job ID here and I want to put a task ID here then what we're going to do is we're simply going to load each one of them and it's going to load all of them so that's exactly what we're going to do so the first thing we want to do is I want to grab that row if I remove the word task item from the name of the shape that called it this is the name of the shape our name of the shape is called task item if I remove the string task item what's it going to leave us with it's going to leave us with the row if I have that row I can get everything else so I've got the task Row in this very variable if it's zero we can exit the sub going to focus on our construction project now B2 is going to take on what I need that project ID where's the project ID going to come from it's coming from our task database and it's located directly in column B and I want to put it directly inside B2 so that's the first thing we're going to do B2 is equal to the task database B in the task row it's going to put that project ID then we're going to run a macro called the load the project that's going to load that project we've been over that macro before next up is for the job so job ID of course that's going to come directly from column C and whatever the task is and I'm going to place that directly inside B6 so B6 is going to take that on so B6 is going to take on whatever's in C that's the job ID and then we're going to run the macro to load the job lastly of course in column A in our task database that is our task ID and we're going to put that directly inside B10 once it's in B10 we're going to run the macro to load that task so that means when I make a selection all of that happens as we can see this is going to change the project here we have a new project Lisa's house so we see that Lisa's house then the job details then the task details load up very quickly and we can make changes so when I make a change and I decide I want to make this one a little bit longer I select on that and I can set it to 15th we save it and it's going to refresh and we see that it is longer all right very very cool we went over most of the important macros there's a few left off the navigation relatively simple but we are way overtime today but I do appreciate your continued support if you want to support this channel what I'm doing here to bring you all this amazing content some great ways to do it I've got the ultimate developers VBA Library I've got over 500 macros in that library and that's going to help you create these types of applications extremely fast in fact I use all the macros that I put in that and allows me to develop these applications in just a few days and for you as well so you can not only create these applications for your work for your boss for your freelance career or you can sell them online so it's a great way to do that go ahead and grab the ultimate developers VBA Library that'll ensure that you can create these projects in no time at all thank you so much for your continued support and we'll see you next week thanks so much [Music]

Channel: Excel For Freelancers

Views: 6,862

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Construction Project Manager, construction, project management, construction project, construction projects, project manager, project management software, project management tools, projects, excel construction project, project plan in excel, excel construction, excel project

Id: iP6gkMXhTPo

Length: 121min 12sec (7272 seconds)

Published: Tue Apr 23 2024