Collaborate on Free Estimate Template Download for Engineering with Ease Using airSlate SignNow

Free estimate template download for Engineering

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   ingly 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 ingly 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