How To Create A Project Costing Application with Budget vs. Actual Costs In Excel [Free Download]

hello this is randy with excel for freelancers and welcome to the project costing budget versus actual in this week's training i'm going to show you how to create unlimited projects and compare the budgeted resources with the actual resources spent it's going to be an incredible training i'm going to show you how to create add new save and print them all in a single training we've got a lot to cover so let's get started all right thanks so much for joining me today i've got a fantastic training projects regardless big or small projects whether you're in construction whether you're doing office work you want to do project costing we need to understand how much projects cost we need to budget for them and then we also need to know if our actual costs are worth similar to our budget we would like to compare those on the same sheet we also want to create unlimited projects and quickly and easily load them in this training i'm going to show you how to do all that and more we got an incredible little gauge chart that i'm going to show you how to do and we're going to use conditional formatting to show just how our project budget versus our actual compare it's going to be a really great training i've got so much to cover i do create these trainings each and every tuesday for you the download is free you can check the links in the description we move rather quickly on these so feel free to slow down the video or pause it and re-watch it each every time this is for intermediate to advanced excel but all types are welcome regardless of your excel scale i welcome you here all i ask that you do is just go ahead and subscribe to our channel make sure you click the like button down below and of course i love to hear from you so add your comments down below i reply to each and every single comment every week and every day i'm there for you replying so i just want to let you know that i'm here for you if you have any questions that's what the comment section is for down below if you ever thought taking your excel skills to the next level turning it into passive income well the truth is you can in fact in my mentorship program i am showing you just how to do that we're going to take you every step through the defining phase then we're going to design development and deploy i'm going to show you the complete process of how to create your own excel applications and release them for passive income just as i've been doing since 2015. i'm going to show you that in the mentorship program i'm going to be creating an amazing accounting application and showing you every step of the process and that's going to include invoicing inventory purchasing full customer deployment a complete dashboard an amazing email notification center for over 50 triggers and a ton more if you would like to learn how and see how an amazing accounting application gets created the mentorship program just might be for you go to my excel mentor dot com and learn how if this is right for you all right let's get started we've got a lot to cover i want to make sure that we give this a name i've got a blank worksheet but i've got some things to help us move along we've got some formatting and what are we starting off with well i want to put our projects i want to create unlimited projects on here so i want to have a button set it's going to allow us to save projects add new projects and print projects it's going to be really simple and then what i want to do is i'm going to have a list of those projects so once they get saved they're going to go in here in the list of projects and then there's products projects that have specific items so inside each product will have items so we need to know a product project number and then have those items one of those items type of labor or materials and then what we also want to do is make sure that we have the budgeted cost the actual cost we want to save all that information in the table so we've got that i've also got a list of customers here just kind of a basic customer with not much going on there when i load a customer on to the projects i want to have their address one and two appear inside the project and i have an item list just a kind of basic item list with item types labor and materials i also have some named ranges it's gonna help us move along let's go over just a few of them so we can do that name manager and then i have just basically four different item numbers we have a customer name and that's just a dynamic uh named range using offset i've got a whole training on named range just so we're not going to focus on that it's dynamic using offset so as that our list increases so it is our named range i've got the same one for item name i want to know all the item names and that's in a drop down list called item underscore name and then i have a project number also it's gonna list be dynamic so that all of our project numbers go those are the three what you see here is criteria extract those are from advanced filters that have already been run on testing and we can delete those they're just going to be recreated each time that we create an advanced filter which is pretty normal so really we have these four that we're going to be focused on so let's get to it in the training i want to make sure that you get so what kind of name am i going to call this i want to call this project notice starting format just to make things easier costing budget let's try versus actual and i'm going to double check my spelling because you guys always correct me because i mess up my spelling all the time project costing budget but that's going to look good now okay so let's go with the green now often you ask me what color sets i try to use different colors sometimes i've also got some information here these two columns we're going to use for admin and they're going to be hidden and with you when you get this if you want to take this to another level and you want to release this to your clients you're more than welcome to do that just right click and hide these columns and make sure you lock your worksheet okay so i want the customer row here whatever the customer rows i want a project row and i want a new project we're going to write some formulas for those a project load whether it's true or false in your body so the new project is going to be true or false we're going to just set that up right now and then the project loads can also be true or false so we'll set that's false we need to know what the project and then i need the next project number what would the next project number me if i've got the project number one i want to know the next one what would it be we'd use the max formula so let me write a little formula for that i'm going to use if air just in case there's nothing if error in case there's no projects i want to use the max on the current numbers what is the max of what the current project numbers that's that named range but i don't want just the max i want the max plus one so plus one what if there's no project numbers i want to default to at least number one so we can do that why would we do that well just in case notice it's two it's gonna give us the next one that's what i want but if for some reason this got removed and there were no projects at all i'd want this to default to number one so that's gonna help us we do that okay i'm gonna put that number one back it'll help us for loading purposes i'm going to keep the data in here so project costing budget so basically what i want to do is i want to have a table down here i want to have some project information here i'd like to have some summary information here and maybe a small graph here and then just information so let's let's go ahead and bring this out a little bit i want to just take a look at this here we'll go all the way to acid i'm going to format those cells just to give it a kind of look we'll go with green on this one green is the color of money usually in some countries so why don't we go with that because i want you to use this to not only make money with your clients but i would like your clients to make money with that when they create this application and use this application they can then reduce their costs and increase their profits that would be nice so i'm going to fill effects and then i'm just going to give this slightly off the screen just a basically a green color and that's fine just a faded green okay so what do i want down here so basically i want to color let's just say column c i'm going to use this as a buffer column and that means we could increase or decrease this column width nothing will happen in that column and then probably around column oh let's just say i'm going to hold down the control and then i'm going to go here all the way to s so that way i'm just going to go down as far it doesn't really matter how far we go down i'm going to color this this standard green color that we're using okay so we're going to color that green so now i've got that so let's bring this up we don't need this extra we can color this white too we don't need this we'll go up okay so let's add in some company name here so let's go ahead and fretters construction and we'll go ahead and put an address in here that way your projects can always look professional main street anytown usa okay so we've got that so we've got a little bit of a company information i'm going to add a logo in here and then i also want to put in the project name that's going to be helpful too so let's go ahead and project name and then i want to store the customer of course so we're going to put the customer name name here right justify those are all ready so i'm going to put in a drop down list i want a data validation if we notice remember we have a data validation so let's put one in for the customer and it's going to be a list type and then what we're going to do is put in customer equals customer underscore name that's the drop down list we've given so now we've got a list of all of our customers which is what i want now when i select a customer what i want to do is i want to put the address 1 here and address 2 here so that's it we're not going to need these grid lines much longer we'll use them just a little bit to help us but we're going to undo those very very soon so i want to put the project number here let's i want to put a project number number and then i want to have the project number go here let's just say one or two or whatever it is there and then i want to also want to have a date here the project date and i want to have it created by who created this created by so we can keep track of that that's all the information i'm really going to store here let's say in j7 let's say i'll put some formats and i'm going to format those because i want to go i want a green part so let's go ahead and fill effects i'm going to put in a green i'm going to go from white to this light green here so the white is going to be our first color and then our second color is going to be that light green and i'm using vertical and then go left now why do i want to do that because i want to have this area right in here all for our admin so we can see our project costing summary kind of i want to put the summary in here it can look nice and then i'll put a border around here because i want to have it all the way around here so just on a big border here let's go we go all the way down about here and then i'll put in maybe a dark green border so it fits with our our theme go ahead and into the borders a thicker one we'll select a darker green color maybe this or maybe a little bit darker here just a little bit darker here and then click ok and then surround it okay that's what i want there that's going to set up our project we can now what i want to do is i want to put in some underlines here so we know which i'm going to hold down the control format the cell and i'm going to use maybe this double line here and put underneath because i want to know which lines are going to be the user to fill in i think that would be helpful there so the address is going to go here i'm going to put the header here what i want and then i want to put my table down here so what would be the first one i'm going to call that item name and i want that on a different row i want that on two rows so i'm going to hold down the alt and then click enter and then put in name that's going to put it on the second row once they have the item name what the item type is going to be labor materials so item type and then i also want item description and i also want i want a total i want three columns for budget and three columns for actual so let's do in i also want to total it up so total budgeted let's see the budget budgeted we'll go here and then i'll put in down here i want to put in the quantity of the budget so qty i'm going to put in the cost and then the total and then i want to do pretty much the same thing but slightly different but this is going to be actual so i'm going to copy this paste it here and this of course is going to be total actual so i'm going to change this to actual and then what i want to do is i want to have a formula here and here for the totals for the total budget so i'm going to merge and center this holding the control merge and center that and then i'm going to format these particular cells so i'm going to give it a format the cells and i'm going to give it that fill using the green theme fill effects but i'm using a little bit darker so i'm going to use the mid to the mid light here on this one and then we're going to click ok and on the bottom one here on all these bottom ones here i'm going to should have done it too here format the cells fill effects and then we're going to use just a slightly lighter one that we did previously which is this mid and then we're going to go to the light one that's the one i want okay perfect and then i'm going to do the same thing here and here format those cells because i missed those and but i want the totals oh we yeah we could keep those white but we're going to they're going to be formulas so we'll put the same effects that we want to here which is that mid green here and then we're going to go down and that's it okay good so for the rest of these what we're going to be doing in this one and i'm going to hold the control down for these i'm going to use the full from the mid to the light automatically that's going to give us that nice blend so again we're going to go from that mid green all the way to the lightest screen so that way because those are merged and centered that's what i want let's give it some borders format those cells i'm going to go for a top bottom and then inside so the borders in the middle of course will be this and then i'll put the top and the bottom here that's what i want we can actually add it here too as well for those ones that are not merging that's what i want so here so i'm going to right justify these two because i want the soda so this is going to be the total of the budget so equals the sum and basically we can just start here and then go down to whatever row we want 99999 that's fine that'll cover here and then i'm going to do the same thing here so i'm going to do is just going to copy and paste this here that's going to cover us for the total so we have the total actual it's going to be whatever here we have the quantity and the cost so this but i do want some lines that i can separate these so i'm going to put go all the way down here hold the control and then i'm going to do the same thing control and then go all the way down here so we've gone down to the bottom and then let's hold the control one more time now what i'm going to do is i'm going to put some right borders on there i'm going to right click and format the cell sorry it's off the screen bring it up here so i'm going to put this black in here and then on the dotted lines on the inside that's what i want so that we can differentiate between those that's what i like so so we can separate clearly separate between the budgeted and the actual cost here we're going to put something like i want to know the versus the budget so in this case i want to know what the actual is versus the budget so we can put in vs budget and then i'm going to put an amount here budget and then alt and then enter and then amount so we can put amount in parentheses this is going to be the amount differences and this is going to be the percentage difference so we can put in versus budget and then we can put in percentage here in parentheses so percentage because i want both of the percentage and we're going to use conditional formatting with some icons percentage all right so we've got amount and percentage that's good i want the item name here so the item name here we have it let's put this in a data validation too because i want the item name to go here so data data validation and then we're just going to click yes whatever is in i had it previously there on item name so that's okay all i want to do is click item name and that's going to provide i had it in some cells already that's why it appeared there because i hadn't cleared it out from a test that's fine so what i want to do is i want to select a specific item here and i want the item type and the item description to show up i want default the quantity of one the cost and then i want to set the quantity here and the cost here maybe maybe on this one but we definitely want the user to be able to change these obviously so we can get the total this is going to be formulas and this is going to be formulas and this is going to be so let's go ahead and differentiate between in which what the user will be changing which is you know all these informations and not the formulas so i'm going to hold down the initial shift go shift and then hold down the control and go all the way up i'm going to add some conditional formatting for this specific one so first i'm going to format the cells and i'm going to give them this back color here and then i'm going to also add some conditional formatting here new rule i'm going to use a formula and i'm going to use the mod formula i want every even row so mod it automatically comes up using auto hotkey i'm going to format those i'm just going to go a little bit darker on this color here that's it that's so i'm going to do something similar on the other one so that's what i want a little bit darker cells now what i'm going to do is i'm going to add conditional formatting to the rest of them which is here holding down the control scrolling down here and then holding down the control a third time here and then we're going to go down and now i'm going to add some conditional formatting on these so basically it's going to be very light a new rule using the same rule in fact equals mod also the even rows here and then what i want to do is format but just a very light color so i'm going to pick this color but i want even lighter so i'm going to go a little bit lighter here right here that's what i want okay so that's going to give it a very light color that's what i want so something nice like that okay let's go ahead and save our work and we now we don't need the grid light so we can remove the gridlines it's going to give us a cleaner look so what do we want now we want a date in here we want created by we've got a lot of information now we're almost ready to write some macros but i want some information in the summary here what do i want to know well i want to know what the total budget is that's important i want to know what the budget is and basically it's going to be i want to kind of a summary so this is going to be equal to whatever this is here that's our budget and then i want to know what would what would 20 below our budget be so let's just put in some number here just for the fun of it and then i'm going to put in let's just say fifty dollars let's just say a hundred dollars to be so it's easy and then i'm just going to put in a temporary formula here i'm going to update that formula in a moment once we get them all but i just want to get that total okay so we know our total budgets it's a 100 so let's format this so if our budget is forma i want to know what is 20 below the budget so we're going to put in some text here 20 below okay and that's enough and i also want our 20 above what's 20 above the budget if we go over the budget i want to know above the budget and then i want to know what the actual costs are and then i want to know the dollar difference here what is the dollar difference and i want to also want another percentage difference of that percentage difference all right okay i'm going to right justify all of these because they're going to be based on that so i'm going to right justify them here and now what i want to know is what's 20 below well 20 below is going to be equal to 100 times 0.8 and it's going to be that's going to go so our 20 below if it's 20 below would be 80 above would be 120 so obviously equals here 100 times 1.2 that's going to give us our so that's going to give us let's format these not the percentage one let's format those uh with a currency here we'll just go ahead and currency that's fine so we can get some dollars all right so now what are the actual costs of course the actual costs are going to be right here so there we go so what if we have a quantity of what if it took a little bit longer 1.2 and the cost was 100 so it took a little bit longer and then we'll just put in a temporary formula here i'm going to add some more onto that equals this times this okay so now we have our so now we have our information 20 above we know this is job now let's put in some dotted lines i want to add to this a little bit more i'm going to put some double lines so format the cells i wanted this to stand out so i'm going to put the double lines on the top and bottom so it stands out a little bit more these actual costs will stand out all right and this one's going to be a percentage so i'm going to format it's already formatted from some tests that i did in here what i want to do is i want to put some graph or a needle graph which is needle gauge we call it the color-coded gadget that's going to be really cool and here i'm going to i need some information for that needle gauge so what i want to do is i want to show in basically a graph format you know are we above are we out or we below the budget so let's add some information in need of gauge here and what do i want to know i want to know the actual cost in a percentage format but differently basically if it's at if it's actual cost if it's above the cost i want to know that it's it's going to be different it's going to be the actual cost is going to be either from 0 to 100 in this case i want a 0 to 100 percentage-wise based on if we're below the cost so basically if we are at 20 below i want it to show zero if we're at 20 above i want to show it 100 so that's going to be help us just for our gauge you know it's really for a gauge and then what i also want to know i want to know the needle that's going i need to know the needle thickness needle it's just a thickness so that's going to show us for our gates really cool and what is the limit the limit is going to be 100 on that so the limits 100 and also i want another one view i want to be able to insert and search for additional projects by entering a project number so do project and then what we'll do is we'll make this white so the user will actually enter a project number here and then it will automatically load that's what we're going to do that's it that's all i really need to do so let's go ahead and put some borders around that format those cells i'm going to use that same green go all the way around and then i'll just add in some border here okay good that's pretty much what i want here now we're going to put some formulas in here and this formula basically if they're exactly on budget i want to show fifty percent if they are below budget by let's say twenty percent i wanted to show zero and if they are above budget by twenty percent i wanna show one hundred so that's exactly how i want so how do we do that we can do that with the formula first let's just if there's an arrow i don't want to show anything so if there's an error and then what i want to do is i want to make a subtraction so it's going to be l5 i want the actual cost minus l three minus the twenty percent below minus l three that's going to get us the twenty percent below and i wanna divide that by the difference between the twenty percent above and the twenty percent below so that's what i want to do i want to divide it so how do i do that well again we divided by 20 above minus 20 below just like that minus l3 and then i want to multiply that times 100 because i don't want the decimal value on 100 or 50 or zero so how do i get that just multiply it times 100 and then of course if there's an error comma and then we're going to put double quotations if there's an error because i don't want anything show there we go that's what i want so i wanted to work 20 above in this case we're 20 percent above i want it 100 that's exactly now what if we're on budget which we're exactly on budget i want to show 50 and if we're on 20 less so 0.8 then i want to show zero that's exactly what this wouldn't be a this wouldn't be let's change this to a number here so we're going to change that to a number and then the same thing with this is already numbered so okay we're good to go let's change this i'm going to format it as a number in case we want to use decimals and not general because especially in a number field so number that's good all right so now that we have our gauge and we'll set our needle that's a thickness that we're going to use and i'm going to show you that just in a moment okay great so now that we have all that information we're ready to write our graphs so how do we do that i want to create this really cool gauge graph so we can gauge it so let's start out it's really going to combination of two graphs so how do we do that well first we're going to insert and then we're going to insert okay and then we're going to insert other charts and we're going to insert a donut we don't necessarily need to select any data that's fine so i'm going to right click i'm going to click select data and then i'm going to add it and basically what we want to do is we don't have we don't necessarily i just want the background in other words that we're not going to be going on any data in this case so i really want five parts above and one part below so i'm going to use a comma i'm going to put five ones in i want five equal which is exactly right and then i want one that's large on the bottom and i'll show you how to do that so that's all i really want i don't need a series name also so perfect that's all i want so notice basically i've got five equal parts on one side and i've got one equal part on the left so okay so now what we can do is just click here on it and then we're going to format the data series and the first thing what i want to do is let's move this over so you can see both and the first thing what i want to do is i'm going to rotate this because i want this big section on the bottom the best way to do that is rotate it 270 degrees that's going to place this part on the bottom now what we can do is we can format those so let's continue and format this data point or all the data points this particular one i'm going to give it a fill i don't want any fill on this so no fill on that one these i want to basically go from green to red and kind of a blend this we don't need also so how do we do that well we can do a fill effect so i'm going to select on this one specifically i'm going to go into the fill and i'm going to go into gradient fill here i'm going to set it up so this particular and what do i want i want a linear which is correct and i'm going to start i want this one on the bottom so this linear down here is the one i want to focus on but the colors i don't want so this color here i'm going to focus on the dark green i want dark green here like this color green here then i'm going to just focus on this dark green here and then the next color i'll select that and i'll go to just a bit of a lighter green like this that's what i want to go from darker green to lighter green now what about the next one all i need to select on the next one go back into fill gradient fill in this case all we're going to be doing is switching this time i want the lighter from the darker i'm going to use this one right here this one the bottom left to top right but in this case we're not going to be the dark green so we need to change this color we want this to more of a yellow color so i'm going to click on this yellow and that's what i want so i want to go from dark green to yellow good that's it now what i want to do is i'm going to select on this one i'm going to go back into the fill gradient fill here again we want the yellow but we don't want the green so we're going to change this to maybe an orange like something like this orange here so now that we have that orange but now we just have to get the fill right so now what i want to go from left to right and orange so it's going to be this one here that's what i want selecting on this one here you're going to do pretty much the same thing gradient fill but this time we're going to go from yellow to more of a darker orange so let's change this to more of a darker orange something like this now we've got the darkness the only thing we need to do is just change it around from left to right so we can do the direction in this direction is going to be like this that's what i want and the last one we're going to go from orange to red so let's click on the last one again go into the fill gradient fill we want this darker one correct but we're going to change this to the red so this one we're going to go to just a red right here that's what and now all we have to do is change it so in this case we're going to go from upper left this orange is a little bit probably this one could go a little bit lighter i guess probably probably something like that make sure we've got it right okay so this one we want to go from upper left just so we can see it upper left this is what we want upper just like that now we can change it back to our original orange so we can see it now we can change it back to original orange just so we can see it better okay good that's what i like that looks nice okay so now we're good so now what i want to do is click on the shape itself and i don't want to fill on that so no fill on the shape there and i also want no outline so no click no outline that's what i want and then we can just square it up we don't want a square feel so let's put it a little bit in the square all right now i just want to make it a little bit smaller so i want to fit it in our area so we're going to put it in a box and i'm going to make it equal to this border here so i'm just going to bring it up here so it's equal to the border that way it gives us some guidance there perfect okay so what else do i want before i move that into place i want to put a few more things inside here i want to know again in this case the actual costs and i want to have them here and i also want to know the labor costs and i also want to know the material cost we can abbreviate that material costs so i want to know all those things so what is the actual cost we have those of course right here i'm gonna repeat it again we have it in a few different places but i want the actual cost here i wanted to show up here because i wanted to show up under the gauge okay so we've got that we're going to put in these two formulas so let's go ahead and sum out our labor cost i want to actual cost so equals sum if right we're summing it if and what is the range and the range is going to be basically on this item type here so we're going to go all the way down we can go down to let's say 999 it doesn't matter it's just a large row and then what is the criteria the criteria in this case of course it's labor so we'll focus on labor types and what is the sum range we're going to be this total so we're going to use the same thing and just go to 99999 gonna be no data right now which is fine okay so let's copy that the only thing i want to do is change that sum if copy that and then in this case we're gonna go to material so i'm gonna copy this i'm gonna change this to material i'm gonna double check on those names those are the ones that are going to come from our material so material okay so we've got that and then i'm just going to left justify all of these that's what i want now let's go to the item list so we have materials i should add an s on to that and labor so that's it that's all we have to do put an s on the end of that so we're exactly right all right so we have material cost labor costs and as soon as we add our item zones so as soon as i put labor in here that's going to give us a number and as soon as i put material in here and give it a number of course give it the cost here let's just say 1 120 and then give it the formula here equals okay so let's write up that formula now because i want to make sure that there's actually a value here in here so let's write up the formula and bring it down to all of them so equal so we're going to start out with equals if and it's going to be and two conditions because i want two conditions this one should not equal empty does not equal empty which is the j column j the quantity column and what else i also want to make sure that we have a cost cost here that cannot be empty as well cannot be empty okay so once we have those two we know it then we can only then can we multiply j times k10 okay otherwise i want it empty in case if one of those are blank i want it empty that's the formula that i want now i can copy that down copy that ctrl shift down arrow and we'll actually go down a little bit all the way down here actually and then what we'll do is just paste in that formula and i want the same thing here so i'm going to just copy that all the way down and it's going to copy over correctly right click copy that and then bring that same one over here right click paste the formulas okay that's what i want and of course there that's all i need now we have our now let's go ahead and save our work now we have our material costs here we have our 80 let's just set the currency on those so we know that they're all currency perfect okay so now we're good now all we need to do is build out that gate so let's bring that back over here right where we want it up right there line it up accordingly now i just want to gauge so how we're going to build that gaze that's going to show it very easily all we need to do is just select on this information here that we added earlier and we're going to insert and this time we're going to insert a pie chart so we're going to enter the pie chart here that's going to give us what we need to have so we have the three data we're just going to hide it so let's take a list we have our basic pie chart we don't need this legend here so we can remove that delete that and we actually have three notice there's this little light blue that's the one i want so that one that everything else is going to be hidden so this one we're going to fill right click let's go into the format the data point that's what i want bring it over here let's go into the fill here i want no fill on that outside click on the data point here fill on that i want no fill on that one but this one i want to fill on so this data point right here that's the one i want to fill that small little one that's the one i want to solve there you go that's it that dot if we add increase this two it'll make it larger so i want to make that small one black the only thing we have to do is we need to rotate it just like we did the other one 270 degrees 270. that's going to give us our information we want now actually cost of 300 so it's not going to show up in the right area that's fine but as soon as we add some proper data in there it is going to show up so let's do that right now before we move forward i'm going to bring this down here so let's add some normal cost here so we notice we have a 200 100 we can't have that so let's get rid of that and we'll make it so now we have it exactly on the cost so it's slightly different which is what we want there we go so now we're going to bring it up and now we've got the needle now we just need to increase this make this smaller and then increase the entire one right here so it's going to be exactly the same now we have our gauge so now we notice we're under budget here notice how that moves here so what if we're on budget on budget there that's the one that's going to work what if we are over budget if we're over budget by let's say 1.2 here i want it all the way in for under budget 0.8 i want it there perfect that's what i want let's just say we are slightly under budget 0.90 good that's exactly what i want notice that that gauge now shows up as long as they're slightly not centered we can move it over a little bit there now it's entered so now we have the actual cost there now we have our cool gauge all right great so we've made a lot of progress all we need to do is let's save this project information and let's be able to load it so let's going to add some buttons to do that so we're going to insert some shapes and insert a square shape right here just some buttons here it's a nice big beautiful button here let's give it a nice color and a nice format so let's shape let's format this this and then i'll give it a different background color something that goes along with the theme like this that looks good and then i want to do let's call this save or update and then increase the font a little bit make it bold and white right justify it and center it and then we'll increase the font that's a bit too big so let's just go with 13 on that okay so we've got save and update i want add new so i'm going to hold down control delete control duplicate bring that down here and one more time i also want to do it so we're also going to have a print project under that print project i want to print and i want to add new add new so i want to do all those things okay good so we're looking really good it's looking really nice and beautiful we of course we need to add in some vba but let's finish it out let's add some picture to this to make it look a little bit beautiful i've got some ones saved up here that's going to help us inside my project budgeting folder i've got ones already saved up here so let's go ahead and add all these in this is going to help us here all right we just need to make them proper size so let's go 0.25 for now and then we can update them accordingly all right bringing them over here so the first thing i want to do is add a project icon and bring that over here make it a little bit bigger it's just a little bit of an icon for project costing we're going to use that i want a this is going to be the background for saving that looks good i'm going to duplicate that because i'm going to use that for add new 2. it's like a little green form that's going to use so for the add new and the save going to use these two icons but they're a little bit too big so we'll go to 0.18 on that and then great so now what we want to do is i want to put this plus right on the add i'm going to bring that to the front i've got it automatically here and i want to do the same thing for this this and this make sure that all brought to the front so i'm going to do this it's the same as bringing them to the front same as here so once they're at the front i'm going to bring this one right up here that's going to save it and then i want to print here and that looks nice and then what i'm going to do is use this as like a company icon like a construction icon here on the project so that looks nice all right let's make these italicize and bold here so it stands out a little bit perfect save our work it's coming together really nicely i'm going to line up all of our icons what i'm going to do is i'm just going to line this up make sure all those are lined up in the middle there and i'm going to do the same thing with our buttons and i'm going to line those up on the left actually and i'm going to do the same thing with the buttons lining those up on the left so the format align and then left alright great so now everything's lined up so now we just have to group our buttons so i'm going to use the selection tool i'm going to group it i'm going to call this save button something similar and then add new button and then group this again and then click add new button and the buttons are going to be really simple and then just print button keep it simple group it called print great all right good we're done with the screen now we need to do is just update this i do have to add some conditional formatting in here i want to put some conditional format to make it look really good what i want to do i want to know the version amount what is the amount difference here well that's pretty simple i just want to make sure that there is values and if there is values in both i 10 and i l 10 in that case what i want to do is just say the amount difference so if and then we can use and some there's two fields i'm checking this field does not equal blank that would be our total budget and what else and i want to make sure that our actual costs do not equal blank so do not equal blank if those two conditions are met then what do i want to do then what i want to do is i want to simply subtract in this case i want to subtract our actual cost minus our total and why is that because if our actual costs are higher i want to show that we are over budget so for actual cost so in this case l 10 minus i 10 that's it otherwise empty so that's all we're going to do so i want to show the negative in this case negative is good because that means we're under budget under budget so that's what i want so i want to show that in dollar amount i want to show that also in percentage amount 2. so how do we do that well in the percentage amount again we're going to check we're going to use the basically very similar so we're going to copy that but it's going to be very similar in this case in case they're not blank i basically want to divide l 10 divided by i 10 so this one's going to be just simply division here and that's going to give us and then i want to do subtract 1 in that case that's going to give us a percentage value so that's what i want we're minus 10 it's already formatted so in this case perfect so now i just want to add some conditional formatting to this to make it look right i want some icons and some things that are going to help us so how do we do that well that's pretty simple all right first what we'll do is we're going to highlight all the cells that we want to fix we'll go all the way down here this is the one we're going to affect we're going to use some icons for this first thing i want to do is i want to put a dotted line i want to put a little bit of separator here on this on the right side and i'm going to add some conditional formatting so we're going to go into conditional formatting new rule we're going to use an icon set here we're going to use an icon set so what kind of icon set do we want i want to use something maybe like some up and down arrows so perhaps something like we'll click let's go ahead and click this one this is the closest one to this and so for what i want in this case i want to show a down arrow in this case if it is greater than zero remember plus means we're above budget but above budget i don't want to be above budget i want to be below budget okay so if it's above and then what i want to do if it's less than zero or equal to in this case less than or equal to zero what do i want to show i want to show just maybe just this line this yellow line here and then i want to show the green arrow in this case this is based on numbers we're focused on numbers here so this is going to be numbers so when the value is greater than 0 i want to show the red arrow down because we're above budget we went over budget that's a bad thing otherwise if we're at zero it should be on that yellow line and then above and if we're greater than let's put greater than zero we want to be there greater than or equal to zero is fine and then everything else we're going to put the up error so let's take a look at that that's what i want because we're 10 below budget right our our budget our total was 100 we only spent 90 we're above it so we're 10 above budget and we got the green arrow let's try this for this one what if we go uh in this case we're going to go above budget by one in this case what i want to show so that's what i want so perfect so let's go ahead and copy this formula down and then we're going to paste it all the way down here paste the formula there we go now we've got the formula pasted now we've got conditional formatting that's what i want i want the down error when we are we here in this case we are 32 above budget we budgeted for 100 but we spent more than that you know and in this case work so if we change this to 100 we'll go back that's a little bit more simpler in this case we're simply 10 above we went beyond the budget and that's what i want okay so what about the percentages i want to add some additional conditional formatting for that how do we do that let's go ahead and hold down the control highlight the entire column here at least percentage and conditional formatting and this i want a new rule in this case i want to use a percentage so this time i'm going to use a three color scale so let's choose a three color scale and this is what i want but let's change it i want to go from green and i want to go all the way to red in this case let's use that red so from green to red in this case i'm going to use the money again i want to use a number on all of them number let's change the number and then the number and what is that number let's go the same ones that we chose here so we're gonna go a point two right if it's point two less than that point two if we're under if we're under budget i want to be negative 0.2 in this case or in this case if it's we're over budget i want to be positive 0.2 so it's 20 so that'll cover it so that's just what we have here that's the same percentages we used here so click ok and there we go minus 10 perfect so let's do conditional formatting and then all we need to do again is copy down the formula copy the formula bring it all the way down here and paste special paste the formula there we go so now we're 10 above it's going to show kind of an orange as we increase that it's going to show more red that's what i want red so we're way above budget on that one because we spent way too much money wasting time lunch all right so now we've got it really cool so i gotta remove the border on this one i don't like that border no outline on this one that looks better perfect so no outline all right good now we are ready to go all we need to do is just a little vba what's the vba going to do the first thing i want to do is when i add an item here i want the item type description the quantity to show up how do we do that well that is on worksheet change so let's get that done i'm going to go into the developers if you don't have the developers tab you can just go into the file options here depending upon your version and then customize ribbon and make sure the developer's selected you can use a shortcut of course alt f11 will get you there we're going to go into vba we're going to write in some on sheet code that's the code that's going to appear on the sheet well i just said we're going to make a change when we make a change so we're going to focus on worksheet change and it's going to be changed when we make a change what do we want to happen well when i make a change to a specific column i want that change i want to check i want that item so that's going to be the change to column d starting in d10 and going all the way down so that's the code that we're first going to write on worksheet change we're not going to write on selection change we can remove that so using my autohotkey i'm going to automate that if not intersection sorry we're going to focus again like i said on d column d d10 all the way through let's say d999 all right i want to make sure that was and also i want to make sure that we're going to be loading and i want to make sure that b6 is false and i only want this to happen when automatically when we're not loading it when we're loading it right this is going to be false i want to make sure this is false so how do we do that well we can just make sure and range d6 dot value equals value dot value equals false okay then what do i want to do then i want to do a few things then i want to check to make sure that if d is not empty i need to make sure if we delete something i want to make sure it's empty if range d and the target dot row dot value does not equal empty right then i want to do something then what do i want to do okay then i want to set the range we should dimension a few things before we get started let's do that dimension the customer row we're going to use that as long and the item row as long and also we're going to be looking for we need to find that item so we're going to dimension the found item as a range found item as range okay good so that's enough that's all we need there so basically this right this works you right here let's put notations on item name change okay we're gonna also change something for on customer name change too so we need to add that in on customer name change we'll do that next so when we change the customer i want to add in that just one and address two so we'll do that next okay so if we make a change of item what i want to do i want to look for that item i want to look for it in this list if it's found in this list i want to pull the row like row six once it's found i want to put in the materials i want to put in the item description the default quantity and the cost i want to put all that information in here and i'm going to put it right inside here so bring it all in here item description quantity and the cost so how do we do that what's relatively simple so let's go ahead and write a little bit of code the first thing what we're going to be doing is we're going to set the write them set the named range so set the found item equal to what's going to be equal to based on sheet 5 that's our item list sheet we know we have a sheet sheet sheet five dot range item underscore name that's the name of the named range we create dot we're gonna find and what are we looking for we're looking for whatever we just changed target value that's the item name if that is found then we need to do something so how do we know if it's found if not found item is nothing then it's found double negative that means not and nothing that means it's found found okay then we need to do something what do we need to do and then and if right because it's been found and if so if it's been found i want to get the row so let's extract the row the item row is equal to the found item dot row item row so now we have the row so now we can extract all the information so now it's pretty simple now i just want to say here e through h is equal to b through e that's all we need to do so we just need to write one letter code to do just that so range e and what is the row the target row and the target dot row and through all the way we need to focus all the way to the cost right we've got to go name type description quantity and cost so all the way through h we're going to go through and colon h and the target row dot value is equal to sheet 5 we know the row because we just heard the range what is the range focused on all the way through b in this case b and the item row and let's bring this over increase the space here a little bit so we get more room to work with and colon all the way through what are we going over all the way through e all the way okay so that's all we need to do so b through e and the item row dot value item details okay that's going to bring it all over there good that's it that's all we need to do let's test that out and see how it works save our code before doing anything else and then go ahead and go into the projects now we need to do is double click on this because we've already entered it double click on double click on this one double click on this one it's working just great now we can now theoretically you may want to enter a quantity but i'm thinking that we just enter the budget and let the user enter the quantity cost we could default we could probably yeah maybe we'll put the cost into we could put the cost in but not the quantity and then allow the user to put in the quantity that might be one because that means the cost will default we can do that let's go ahead and put in the cost too in column k just the cost so the cost from the e bring it over to k that's still not going to put our results we don't want any results here until the user actually puts in the actual results so let's do that and put that in one additional line of code here so range let's bring this down so we can see where we're going on that focused on in this case k so range k and the target dot row dot value equals focus on the cost again sheet five dot range and it was e e is our cost right these are our cost that's focused on so we're going to bring over in e and the item row right item row dot value bring over the cost for actual okay so let's just double click on those and see how it works double click on that that works that works when we don't want to total we don't want a total yet unless we put in the quantity so assuming we put in one then that's exactly right that's a one zero percent zero percent as soon as we change it okay fantastic we're doing really good here now we've got the information here we've got everything on the items i want to now if i change something let's say let's put in freder's sync put it in just a little bit okay so now if i change the customer i want the address to come in two or three address one and here two so how do we do that well that's a change on f5 so let's write some code if we make a change to f5 so again if not intersection f5 then what i want to do f5 and this could be on load as well in other words it can be even on project load we want the address to show up so that's fine so if we change in f5 and i want to make sure if f5 does not equal 7 so if range f5 dot value does not equal empty empty and i also want to get a custom row we're going to put that custom row in b3 so and range b 3 dot value does not equal empty then what that row is going to come in a formula we're going to add in just a moment so then we can define the customer a customer row is going to be equal to whatever is in b3 and put that formula into next so not to worry equals b3 and that's going to be the customer customer bro okay so now we have the customer know all we need to do is put in the address into just two lines so the customers we notice that our address line is in b address one is in b our address two is in column c so all i need to do is determine the customer row which we're going to do right now put it put one address one here and address two here so let's get that customer row equals if error in case there's an error we don't want it what i want to do is i want to get the customer row so based on what so we use the match formula for that we're going to be looking up we're looking at whatever customer name is in here what is the lookup array we're going to use customer name which we already have we want an exact match we're going to add 3 why are we adding 3 because our customer actually writing 2 because our customer rows start in row three and if there's an error double quotes why are we adding three right our first customer row duffy octavio our customer is going to start in row three that's what we want row three nothing here so our first match is going to end up in row 3. that's what i want now we have it if we enter anything else of course we're candid so just in case it won't let us that's good so all right excellent now also while we're at it we need a project row so let's put that in project row is going to be based on this and i notice we have the project list we're going to do the pretty much the exact same formula just a different name range again equals if air we're looking up we're going to use the mass we're looking up and we're looking up this project number and we're going to look up the right project number we're looking up we want the exact match so we're going to use 0 plus 2 because our first row starts on 3 comma double quotes in case there's an error 3. good that's what i want so 2 doesn't exist so it's going to be blank excellent that's what i want okay so let's save our work so far let's go back into the vba and let's get this address 1 and get address 2 done so let's type that in right now so we've got the custom row because we know there's a value in b3 so all we need to do is just write some code so in this case it's going to be f6 range f6 dot value is equal to remember our customers are based on sheet 4 sheet 4 dot range b and the customer row dot value that's going to be address 1 and all we need to do is copy that pretty much and then just change it a little bit to f7 and c so copy that paste it in here change this to f7 which is what i want for the one lower and then change this to c that's going to give us our address too so now as soon as we double click on here and change it it's going to add that information right perfect okay great so now we got that now we need to do is populate these buttons get them to work and we are good to go let's center this a bit here and then i'll add a few spaces on here to put this make it look a little bit better okay so now we need let's just write some macros anything else we need to do we need to load the project on while we're on our auntie when i when the user makes a change to here one what i want to do is i want to load project one here so how do we do that well that's a change it's going to be let's format those cells i'm kind of picky about these things out of some left border dotted line okay so if they make a change so that's a change so let's just write that code up right now while we're on this orange sheet that's going to be our last on sheet so we can clean this up a little bit here get rid of the extra spaces here we're done with that so this is going to be on project change on project number change okay because we're making sure so if not we're going to make a change we're focused on p6 this is what p6 we're going to focus on so in that case that's where we want to load but we do want to make sure of course that there is an actual role p6 is nothing then what do we want to do then i want to do something i want to check i want to first thing i want to do is i want to take whatever is here and i want to put it right here into i2 why do i want to do that because this project grows based on this value in i2 so the first thing i want to do is i2 equals p6 so all we can do is write that in so range i to that value equals range p6 p6 dot value okay that's going to bring over it's going to bring over project id okay so now now we need to do is run a macro i'm going to call a macro called project we're going to write that in just a moment project load and that's going to load the project so let's get a macro we're going to write okay good that's all the code that we need for the on the sheet that's pretty simple we've got that covered we're good to go on that we don't need to come back here unless there's an issue or bug okay so let's go ahead and write some code now we're going to write go into the project macros and we're just going to write four small macros one to save it one for new one to load it and one to print and just to help us move along i've dimensioned just some variables here so we got the project row project item row the last item row item bro and i'll walk you through these variables and then the results row and the last results row so let's first thing we want to do is we want to save it so we just added this information here we've got a budget we let's put in the date here 120 and then created by fred our favorite character okay so we've got the information and now what i want to do is i'm going to click this button here the save update and have it update okay i don't like this icon here let's move it over a little bit to the right and the same thing with this one make sure everything's consistent so we can clearly see the icons okay so i want to click save and update and have it update so let's write a mac or do that that same button is going to do both it's going to save it new or start updating existing so we're going to write some code to help us differentiate between the new ones and the existing ones so we've got our dimensions up here right now so let's save it up so we're going to focus on with sheet one so primarily that's it so we need to first determine if new or existing project project okay so how do we do that well we know that if this is empty for any reason or if or if it does let's say this is three we haven't created a project yet we know project row is going to be blank so we can use b4 to help us determine if b4 is empty that's going to mean it's going to be a brand new project so that's fine let's use that so if dot range b4 dot value equals empty then it's a new project else existing and if okay so now we can fill in some information based on whether it's new or whether it's an existing first thing what do we want to do if it is a new one we need to determine the project row our project's going to be saved on sheet2 so the first thing we want to do is get a project row and our first one is going to be four so we can write some code to do just that to get us that project grow so let's write some code that now project we've already determined about project row equal to and i'll use in this case sheet2 and i used again auto hotkey to automate that process and cheat 2. i don't type that fast okay sheet 2 that's going to guess but not our not the last one i want plus one that's going to give us the first available first available row okay so once we have that what do i want to do i want to do some things i want to add a project number i want to there's certain things like project number and that actually just that one that's the only thing i want to add that in because the only we only need to add project number for new projects so let's do that what is the project number we're going to be adding well we already use our max to let us know what our next number is so all we need to do is take whatever's here and place it right in here and i also want to put it here in our project i also want to place it here into i2 because that's going to be our next project number so we can do those two things with some line of code so dot range i2 dot value equals dot range b7 b7 is where the next one is located b7 dot value next project number or id is fine also i need to put it inside so f sheet two dot range now we know a and the project row dot value is equal to whatever is in i2 right so it's equal to whatever's in so we just have to copy this whatever is in i2 and then paste it in equals the fact needles now let's call that project number okay so now our project number is inside here and that's all we need to do for new what about existing if it's existing all i need to do is really get the project row if it's existing we know the project row like for example if this was project one we would know that it's in b4 so that project grows so if it's not right so we know b4 so let's go ahead and put that in assuming not blank project row is equal to what's in b4 so let's put that in b4 okay so now we have that that's our existing call that existing project row existing project bro great so now we have that so now everything else that we're going to do we're going to do it regardless if it is a new or an existing project so regardless everything else so let's do that sheet two now we're not using data mapping here because just a few but in other times we use data mapping but in this case it's only a few ones so this is what we do if it's not data mapping if you're not familiar with data mapping you can check some of my prior videos where we use it so sheet 2 b what i want to put it i want to put in the date there so what do we want to do and let's just take a quick look summary in b i want to put the date i want to put the name in c i want to put the customer name in d and i want to put who created by an e so that's we're going to do so let's write up some code to do just that and of course it's going to come from the individual fields up in here so we're going to write additional code to accommodate that so b and project row dot value equals dot range and in this case it's i4 that date's coming from i4 so let's put that in i for and then put in date now all we need to do is just copy and paste them and make a few updates so the first thing the next one of course is going to be c then d then e so all we need to do is make the update c d and then e okay so in after date we're going to be putting the project name and then we're going to be putting in the customer name and then we're going to be putting in the created by that's the last thing that's all we really need to do created by okay so great so but where is that going to come from well let's take a drop this down a little bit here and then bring this up so we can see the project name of course that's going to be coming from f4 so we can put that in right now f4 is our project name our customer name is going to be in f5 right that's the cell below so it's f5 and then the lastly are created by that's going to be in i and then five here so i5 is where are created by all right that's it that's all we need to do as far as the information the next step what i need to do is i need to put this information here now i need to add this information i want to put that in into a specific table so we're going to put the project number we're going to put all the information in the actual quantity going to put in the the budget quantity the budgeted cost the actual quantity the actual cost the project row what does this mean project bro that's the row of this so notice 10 right this is on row 10 and then the project this is 11 and this would go in project so that way when we load it up it goes back into the same row that we originally saved it in okay so we have that and also the row here this is the current row this is row 3 and 4. i want to save this because i want to bring this row over i'm going to bring it over and i want to put it somewhere else in this case i'm going to put it probably in s i want to put it here a little bit off we can hide this because when we make a change i need to know if s is empty if there's no fp then we need to add a brand new item if it's existing if s does contain a number then i need to just update this row so we'll show you i'll walk you through that process and see just how it goes so let's go ahead and focus on the items first thing i need to do is determine what is the last row let's go ahead and add some dotted lines of this we need to format those cells and put some borders around there we can use this color i think we used black before don't tell anybody okay it's just between you and me alright so now we've got that so all i want to do is really just update that materials and get those items into that either in their existing if they're previously saved put them in here if not add new ones so how do we do that well we're going to use s as our helper column there to determine that so let's write some code to do that so i'm going to focus continue down here and now we're going to use what's called as add or update project items and the first thing like i said we need to loop through all the items so we're going to loop through we're going to get the last one in this case the last one is 12. we're going to use column d to help us and determine which one is so last item row is going to be equal to in this case column d d comment that out last item row and then just in case there's no items there's nothing to loop through we need to to make sure that the last item row is not less than 10 if not we need to skip that so if last item row is less than 10 then go to no items and then what we'll do is we'll drop that down here and then put in no items so we can skip everything else just in case so assuming that there it is items we can move on so now let's go ahead and maybe we'll use i'll use q because s is too far over s is too far over i'm going to use q i'll put those numbers in q so let's just let's just type this in database row so that's going to be our database row so it's going to go right here you can hide this of course this will be hidden or you can change the font color to the same as the background because users shouldn't see this but it's important that it's there okay so continuing on so now we can start our loop so we're going to start out in 4 the item row is equal to 10 to the last item row and close our loop next item row now once our loop is closed we can focus on that so we're going to use q if dot range q and the item row.value does not equal empty that means it's existing then existing it means it's better let's just put previously saved previously saved because there's a row there then put out spring then okay then what else else we'll call it a new item else not previously saved okay so nana and if make sure we close our and if all right so now we got it so what if it's there now assuming that it that there is a value there it's not empty then we can put that into a variable so i want to put that in project item row is going to be basically equal to whatever is here so we can copy this and then equals okay that's our project item row but what if it's what if q is empty well then we have to get a project i don't know we can use the first available one project item row is going to be equal to the first available one in this case is we're focused on sheet three right that's where our project items are sheet three sheet three is where let's call that plus one it's gonna be our first available row first available row okay so now we've got the first available row again there's some things that i want to do only for new items here's our project items what do i want to do there's only really one thing i want to put that project id i want to put it in column a that's important and i also want to put in the row because that's not going to change and i'm also going to put in this so i'm going to put in this information here automatically so i want to put in the current row the row and this so these three items should only go for new ones let's put those in right now inside the code so the first thing i want to do is sheet three dot range a is going to where our project goes a and we've already got the row project item row dot value equals what it's going to equal our project id where is that located we know that that's located right here into i2 so we can go ahead and put that inside our code equals dot range i2 so that's going to be our project id prod project id okay so once we have the project id i also want to add those two other things that were very important so again what i also want to do one more thing actually so i want to put the row that project row right here too so we know what item that is going to be the row whatever is this row here so for example if the next one is 5 i want to take that 5 and i'll put that 5 right here so we can do that right now inside this code dot range q and we'll focus on the item row and the item row dot value is equal to in this case project item row set the let's call this database row that's fine okay so we've now set the database row on queue and now what i want to do is i want to set j i want to set two things inside the database i want to set column i the project row which is a which is an item row and this i want to set i and j so let's do that right now okay so we can just copy this here right here and then we can just update it so in this case it's not going to be item a we're going to focus on in this case i and what's i i is going to be our item row equals item row that's the row or otherwise known as project row project row okay project item row and then i want to know the database row so that's going to be the database row is simply going to be a formula we can use so it's going to be equals row we could also we bro formulas are good if you're going to be deleting lines in case we delete items so i'm going to put a formula in there okay so that formula will automatically generate the row notice that there's a formula here it says row that's what i want to do i want to generate that row okay so we've got the row in there and but of course that's going to go in column j missing quotation okay so that's it for new items what next so now everything else we want to do is going to be regardless again for new or existing items so we can skip out of that we've determined what we're doing for new item for existing items we're determining what we're doing for new items and the rest we're going to do regardless if it's new or existing items so i want to pretty much basically take b in this case whatever is located in let's pull it up in this case we're going to say basically b all the way through f is going to be equal to whatever is located in d through h and then of course we're also going to do j and k so let's write that into some code now just those two lines so sheet i think we got that okay so sheet three in this case d b through project item row and right it's not finished and all the way through let's pull that up so we know the row and pull project b through f that's what we're focusing on f and the project item row dot value is going to be equal to dot range d and the item row and all the way through i think it was h we'll check that and the item row okay good let's just take a look at that and bring it over here so it's all the way through h exactly so i want to bring all this over and then the next thing i want to do is i want to bring this information over in one more line of code so we can just copy it and then update the ranges so i'm going to this is going to be called let's just call this item details and then i'm going to copy this over and then we're just going to update it because we need to add in of course the actual this is going to be a different range so in this case we're going to focus on the actual the actuals are going to be here simply j and k j and k so it's from to j and then k here and it's coming from we're going to put that where are we putting that in putting that in two different ones we're putting the actual quantity in actual cost g and h so there g and h okay great so we've got g and h we've got everything covered and that we'll just call this the actual details all right that's it so that's it that's it for our loop and then we can close out our lips we're gonna do that for every single item and then all what i want to do is i want to make sure that we have saved it now it's no longer a new i want to make sure that b5 is set to false so we could just encase that so let's go ahead and write that in right now dot range b 5 dot value equals false existing let's just say set new project to false and then what else do i want to do i also want to make sure the project load is set to false so i want to show b6 but it shouldn't be so we don't need necessary i need that that's fine okay great let's go ahead and save that and let's go ahead and clear that out it's not a new project i want to make sure that project show shows new because i want to save it i'm going to take this button here that we created i'm going to right click it assign the macro and i'm going to put it project and then here save update that's the macro that i want to assign let's take a look at that inside the code go back up we're good to go on that let's just bring this down in case there's no items i want to just bring this up here so that we can automatically set the new one to false no matter what all right now we'll check for bugs we'll run our macro we've already saved it so always after running a macro check for any bugs and there's no bugs notice how that the rows automatically populated let's check our project we have our new project information here we have the information here already there project items got saved the project number two got created the rows 10 11 and 12 got saved brand new rows everything looks good wow good that worked out well all right great so we just have two small macros to write add new and then load we're going to write in the print so just three ones but this one's really tiny let's write in pose so now what i want to do now i've created project two when i hit project to enter here i want that project to load or when i click when i put in one i want project load to run so how to load up so how do we do that well it's relatively simple we're just going to write the macro called the project load that's the one this one let's move this up here so we can keep it in line and bring it up here so next up we're going to write then we're going to do project new which is just a few lines of code then we're going to do print which is just two lines of code so let's write in the project load right now so how do we do that well we're gonna focus on sheet one with sheet one we're gonna focus on sheet one and the first thing is we can't load a project right unless we actually have a row so we certainly need to make sure that b4 is not blank so that's the first thing we're going to check if dot range b 4 value equals empty then message box please enter a correct project number exit so okay assuming that b4 does contain the value you can then just put that into a variable so the project row is going to be equal b4 so we'll just copy and paste that and bring it down and now that's going to give us our project number obviously kind of self-explanatory okay so now we have a project row okay now that we have a project row what i want to do is i want to clear out all the fields i want to make sure that before we load anything i'm going to clear out everything so let's write a code to do that dot range and then what are the fields we're going to shoot f3 through f7 f3 that's going to get us through f7 i'm going to clear that out also i want to make sure that i4 through i5 going to clear that those details out i4 through i5 and also i want to make sure that i want to erase the formulas but i want to clear this out not the formulas and this out and not the formulas here so basically everything here and here so it's going to be d through h and j through k so let's write that d 10 through d h let's bring this down so we can see it a little bit 999 okay also again j10 through k and then 999 that'll cover it okay so we're going to clear that up not value but clear content dot clear contents okay that's going to clear the clear the fields okay so once the fields are cleared then what i can do is i want to set the project load to true so what that's going to do is keep those item names from automatically appearing in the item so i'm not going to look it up when it's project's loading so we're going to set b6 to true temporarily dot range b6 value is going to equal true and then before this macro edge we're going to set it back to false set project load to true i'm going to copy that i'm going to paste it down i'm going to set it to false now that way i don't forget so that's a good way to do it so i'm going to copy this change it to false and then write the code in between false here and then false here and then we're going to write all the code in between that's a good way to do it okay so now we're going to write all the code in between here so what's the next thing that i want to do when i want to load now we know the project grows so now we're ready to load the project information so basically all i need to do is just reverse what i did in the save so we're going to take this this is an easier way to do it i'm going to take all this we know the project row i'm going to bring it back in but i'm going to reverse it so i'm going to paste it down here but in this case it's going to be the completed opposite so i'm going to take this and i'm going to drag it here and then i'm just dragging equals i'm going to do that for each of the four and then we'll double check it so then going to bring it so this time we're ah it's opposite of sheet 2 so we're bringing the information from the database into our worksheet so all i could do is just it's pretty easy just to drag and drop because we know we've got this right so we just need to bring it over make sure that you've got the equals and the periods and everything in line and then we'll make it we'll pretty it up a little bit if we miss something we're going to let it that's going to let us know bring it over here to the beginning and then we'll do that with the last one after the equals here right in the middle and then okay good so we've got all of them here now and we just have to indent properly so that everything looks nice so highlight it what we'll do is we'll shift tab bring it over and there that's nice so that's going to bring all the information over a little bit easier than running it now we're ready so now we've loaded all this information above here but now i need to load in the items so we're going to bring it in from the item list so what do i want to do in our project item list i want to load in whatever one or two i want to run an advanced filter based on all of the items for a specific project project number one project number two whatever so we can use an advanced filter and we're gonna use a criteria for that advanced filter and all we need to do again i've got a formula here it's gonna link up our project number based on this whatever is here so whatever is here in itunes automatically so our criteria is automatically set what i want to do is i want the results to come here for a specific project then what i want to do is to loop through results check to make sure that there are results and then when i'm going to loop from three to four i'm going to look for the project row number once i get that number i know what row to place it in whether it's row 10 or row 11. then i can just basically copy in all the information here copy in all the information here and that's it that's all i need to do so we're going to write some macros that are going to do just that so let's go ahead and write that advanced filter but first thing we want to do is determine the last row if we're going to run our advanced filter on items i need to know the last row in this case it's 7. so let's pull that into a variable and do that now okay so our last project item row is equal to and this could be sheet3 and then excel fr okay that's gonna okay so that's going to get us our last row let's just using column a that's going to give the last item last item row once we have that we can then run our advanced filter because our criteria is already set just in case if the last project can't run our advanced filter if there's no data project item row is less than three then we're going to go to no items go to no items and that's going to what i want to do is i want to go to no items but i want to go right to here no items okay so everything else is going to be above that assuming that there are items so here's where we're going to write our information in now we can write it in so what do we want to do i want to loop here after running our advanced filter so sheet3 dot range where's our we're going to start out with a2 all the way through j that is our data a2 a2 through j and the last project item row dot advanced filter we're going to copy that we're going to use criteria range colon equals what's our criteria again sheet3 make sure we specify the sheet dot range it's going to be located here l2 through l3 l2 through l three that's our criteria comma and then copy two range where are we going to copy it equals two we're gonna copy that to n two let's take a look at that range right here copying it two and two all the way through w2 so we'll write that in copy 2 range sheet 3 dot range n and 2 through w 2. that's going to all right and the last thing is unique unique colon equals true okay that's our advanced filter let's just look at that real quick a2 through j in the last project item row advanced filter copy it the criteria range l2 through l3 copying it to range sheet three range and two through w and equal to perfect that's what i want so now what we're going to do is we're just going to run a check here i'm just going to copy this but i'm going to change the variable in this case what we want to do is we want to get the last row of the items so let's write in that code in this case we're going to use a variable called last item results row equal to and then we're going to use the results we're going to use n as the column so sheet 3 dot and we'll use column n it's going to help us and then get rid of that space we don't need that so that's going to give us our last if the last item results row is less than three then we have no items so i'm going to paste that here in this case the last items what does that mean it means that the there's no data the last item rows less than three it would be two so we need to go to node data okay so once we have items we can start to loop through them so let's set our loop for the result row is equal to three to the last item last item result row and then close our loop next result row okay so now we have our loop so now we're ready to bring our information over all we need to do is get our word so i need to know the project item row in this case it's in v so the project item rows equal to sheet 3 dot range v and result row dot value this is our project item row we need that that's very important once we have that we can bring the information over so dot range focused on again our project row d and and the project item row dot value and i guess all the way through in this case through h just as we did last time and again the project the long variables item row dot value what is that going to be equal it's going to equal basically everything from o here all the way to s so o through s we're going to focus on that equals sheet 3 dot range o and of course we'll focus on the results row this time and the result row and colon all the way through s and the result rule result row.value that's going to bring over all the item details item all right the next step we just need to bring the actual quantity and the actual cost so that's it we need to need to do that so that in that case we're going to bring in basically from u u and v is going to go all the way over to in this case j and k so let's write that up right now dot range j and again the project item row and k colon k and the project i don't know i could just copy and paste it item and update it dot value equals again let's copy and paste this here and we can just cut paste it over and then update the range here so in this case what's it going to equal these two items here we're just focused on t and u that's all we're going to focus on so we just update this to t and u accordingly okay so now we have the item dues will be the actual let's just call this actual details okay so we've got that and now what i want to do i want to do one more thing i want to bring over this row and i want to put it into q i want to know what row it is so i want to bring that over and put it right into q here right here so once it's loaded in so let's do that right now so dot range q and the project item row dot value equals sheet three dot range w and the result row dot value that's going to be our database row so right it's going to come all the way from w from here right here w bringing that over and placing it in q so that way we know what row it's been saved on when we update it okay great so that's pretty much it for load i just want to set a few things in here so we've got the project load let's fix this we need the and sign there can't do without that and okay let's take a look at that that looks good save our work one more thing i want to set obviously i set the project low to false i also want to set new project to false too it's no longer a new project new projects are stored in b5 so let's put that in set new [Music] project to false it's no longer new if we just load it up we need to make sure it's no new so here new project would go to false okay great let's check that save it now remember we're automatically running this code every time we make a change to p6 we've already written the code right here right let's right here in p6 we've already written we make a change we're going to look at i2 bringing in six and bring over the project id so now all we need to do is just click two and double check let's take a look at this bug range we need e at the end of that obviously continue on okay that looks good we've just loaded two let's switch back to one and two nice our project's loading good great two small macros to write and i'm going to let you go appreciate this a little bit longer but they all are done again all right so project new very very simple i'm going to copy this because we're going to clear the contents for project new so we're going to need that line of code there so how do we do that project new very very simple we're going to focus on with sheet one so with sheet one first thing i want to do is clear out the contents we're going to paste in that field obviously we're going to set b5 to true i want to notice that i want to make sure that b5 is true it's a new project so let's put that in dot again we can just copy this up here b5 we put it up here and set that to true equals true near project and i want to set the default date why don't we put in a date for new project let's put in i4 let's put in the current date on i4 so dot range dot range i4 let's go i4 equals date okay set the current current date that's going to be fine and then what i want to do i want to set the project number i2 b7 i want to set this project number whatever is here in the next project i want to put it up here into i2 so let's do that dot range i 2 in this case is going to be equal to b7 equals that range of b7 next project okay great so we've got the next project number we've got the date we've got everything set up we've cleared the contents that's all we need to do i'm going to save our code i'm going to assign the macro this one add new click assign macro and project new click ok all right that looks good and then just click add new nice everything cleared out the date focused on our name cleared out our information cleared out we do need to put in these two formulas here let's load it up again i want to load it okay let's go ahead and put in the formulas what's the dollar difference equals l5 minus l2 so i want to know the dollar difference in this case the difference of 650 i also want to know the percentage difference what is the percentage of it and simply it's going to be equal to l5 l5 divided by l2 in this case divided by l2 that's going to give us our percentage differences i want to know that so here we've got a 98 the percentage is 91 percent difference in this case we have a total budget of 80 our actual cost we are under budget 91 right we're we're not in 100 budget we're under budget that's what we want and we're difference of six dollars and fifty cents very good okay so one more thing to do print the project and that is it last macro we're gonna attend right here so sub project print that's the one we're gonna focus on very very easy the only thing i'll need to do is i need to determine i don't want to print this whole thing if there's no rows so let's do the last item row let's get that last item bro you know we got that already that last item wrote all the way when we were saving it actually so let's pull that up let's get that last item row and copy that down here and now i'm going to set the print range so we've got our print range so let's go project print but i didn't get a sheet number in here sheet one because i won't do sheet one so so our last item bro we have that there now what i want to do is i want to set our page print sheet1 dot page setup dot i want to do the print area is going to be equal to what are we going to print out we're probably going to start out with d2 up here in the right and i want to go over all the way to probably i would say n so why don't we write that up is going to equal 2 and in this case we'll use the absolute d and then the absolute two starting that colon all the way through and then n and then what do we want the last row and the last last item row that's going to set our printer right now we need to do is just print it out sheet one dot print out and then what are we going to print out in this case from 1 we don't need that from 2 we don't need that copies preview we don't need that active printer let's just put true i want the active printer and then this case print to file no and then i call it no print to file name no and then ignore printers no so there we go so let's do that all right i like that and let's go ahead and set our print that's going to set our print range and then let's go ahead save it assign the macro to this click assign macro print the project click ok and i've got snagit as my default printer so let's move over here print that out it's going to print it automatically out run the macro and here's my stack and let's zoom out here that looks pretty good we've covered everything all right we'll take a look at this we've covered the project costing budget versus actual we've created using icons in conditional formatting conditional formatting colors we've created this really cool gauge chart using that we've got the total budget above or below budget creating saving and adding new projects thanks very much i hope you enjoyed this if you do like this training i would love you to pick up the 150 workbook pack that's going to help us out that's just 56 or 37 cents per workbook that is going to help us out or perhaps check out our mentorship program if you want to learn how to create your own applications for passive income i'm teaching you all that inside our mentorship program that'll help us out a lot thanks so much appreciate it and we'll see you next week

Channel: Excel For Freelancers

Views: 61,801

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, Project Costing, Project Budget, Budget Vs Actual, Project Budgeting, Budget Excel, Excel Budgeting, Excel Project Costing, Excel Project Budgeting, Excel Project Budget, Budget Project In Excel, Projected Costs In Excel, Projection Costs, Budgeting Projects Excel, Excel Project

Id: 2H59thd0W2k

Length: 93min 21sec (5601 seconds)

Published: Tue Sep 15 2020