Available about that skill in the evaluation that's what we've done here we've got a list of employees a number of skills that we can add and then we can evaluate it and what i've done is i've taken this particular skills matrix and i've wrapped it around a project manager so that we can take those skills and we can wrap them inside a project manager creating projects so we can find out who are the best employees for a specific project what that would cost and what is you know and all the evaluation so there's a lot to go over of course in this training even if you don't want to create a skills matrix there's so many excel skills that you're going to learn i have so many.
Tips tricks and techniques to share with you including we're going to go way beyond the matrix we're going to be covering conditional formatting lots of great unique conditional formatting rules a brand new sum product formula i'm going to go over with you and we're also going to working with shapes and pictures so we can add them dynamically as you see here in the pictures for the employees we're going to be adding those i've also got sorting through vba advanced filters project database management data mapping and a ton more so you won't want to miss this training i'm going to go over every single line of code every formula every conditional formatting in every format so you don't want to miss a moment grab your coffee grab your tea or your beverage your choice and we're gonna get started i bring these to you each and every tuesday absolutely free even the.
Workbook is free all you need to do is click the link down below using either your email or your facebook messenger and we're gonna get that sent over to you right away i just asked a few things you can do to help us out that'll keep these trainings for free always just go ahead and click the subscribe button and don't forget to click the notification icon bell as well that'll ensure that you get these trainings to you each and every week and get you a little alert also smash the like button that really helps us out and comment below also i have 200 of my most amazing workbooks and i put them all in a single zip file and i'm going to make that available for just 77 to you that comes with complete a complete 200 workbook library and what that means is you can get a single click to open that workbook or single click to open the training video associated with.
That it's a great tool and that helps us out it's just 39 cents per workbook so if you pick it up that will help us out because i want to get into this training i've got so much to cover let's go over the overview of this application why it's important for companies um and how it can be used and of course all the things that went into this training in this workbook this application all right so basically what we have is a list of employees below here and we can we can display their hourly rate or we can display their staff position right whichever one's important to you if we choose the hourly rate we're gonna then be able to get a total estimated labor and that total estimated labor is going to be based on the project days and based on the number of hours we've got in our admin screen here i've got the work day hours.
Meaning we have dynamic levels right i have it on 1 through 10 but you can create whatever kind of levels you want in a description so the way we have it is 1 through 10 one being little or no skill all the way up to 10 highly skilled proficient you know so what we want to do is we want to rate our employees level based on that we want to know what skill now i mean in my sample here i'm going to use the construction industry so my admin screen i've got a list of skills these are all dynamic meaning when you get this workbook you can put whatever you want in there so make sure you pick it up so i used to be in the construction i used to run a construction company before my excel days so we're going to use that as a sample it's something that i'm familiar with so we've got all these trades let's say you're building new houses right you want to build a new house or you're remodeling houses you may have all of these trades that you want and you've got a.
List of maybe 50 employees each one has different skills so when you're building a house you need to know who to use for what job and that's why i've done this skills matrix skills matrix is great great because it maps out not only here the skill that you have here and your employee and then it adds a grid so you can see who and then i've had some conditional formatting a one of course would be a very low skill while 10 would be the highest skill available right so we know peter parker here he's got a skill of flooring a very high he's an expert flooring and he's expert on drainage i don't know why you'd have to play these numbers are random obviously but you get the point right so this way you can rate it now i've also done here this these two rows right here this is the minimum skill level so for example let's say.
You've got a job that requires appliances right a lot of appliances right and you know you need a very very high skill level eight and you need five employees five minimum five staff each with eight right so if you have that right so notice this is green that means we have that we have one here two here three here four here and five so we're covered that right we have eight we have five employees with at least eight right if we go down here we can see but here notice this is seven we need seven employees with a grade of seven or higher but we don't have.
It that's why it's in red notice we have one ten one nine one seven here a few nines but we don't have seven of them right we need seven of them and we don't quite have that we only have six of them right if we only have six one two three four five and six notice we only have six employees with a skill level of seven or above so that's why it's in red yellow would be exactly the same so not only do we understand how many of our technicians have the ability we see in the areas in these areas here in curtains drainage and drywall we have a lower skill level especially kurds so we need to know we to make some employees likewise if you've got a project let's pull a new project.
That doesn't have anything let's say we decide we're going to add some skills let's say we want to let's say we're going to be doing some framing so we want to do a kitchen let's see we're doing a new kitchen so we're going to add some cabinetry in there we are going to add some let's say some lighting right we need to put lights in our kitchen and we need to do a few other things maybe we're going to do a little bit of a plumbing right we need a new kitchen sink so we got these skills now i know that this project i know i'm gonna need about five staff on this project and i'm gonna save that project and i know the project let's just say five days right or whatever the days and then i'm gonna save it again so i've got these but now i've got a whole list here my employee list i've got all these employees i've got all these different skills how do i know which employees to.
Posts Related:
Get for the job right well with our skills matrix with this kind of all we need to do is just click generate and it's going to automatically generate the best staff for the job based on all those
Skills really incredible too really powerful so we know and now let's see we decide okay we've got great we've got uh five we've got our what if our budgets let's say six thousand dollars we're over the budget here right so our budget our budget six thousand our estimated labor is six thousand six hundred so we may need to make adjustments for that but we we realize here that leslie here she's got the highest skills at eight ten and ten so she's scoring a 28. she's got all.Those skills covered right we can also put in you know our skills here if we want to larry's done pretty good but we see that greg and tina doesn't have the skills but that's still the highest these are the highest available so we may want to make adjustments to that but this kind of tool gives us and it shows us the true power of excel and when you learn all the skills in creating these as opposed to just downloading this and start trying to use it what if you learn the skills that help you make these workbooks right you can then create these and put them as part of a larger project and sell them for passive income so not only do i want to teach you excel i want to teach you the tools tricks and techniques to be successful with excel so that's why these videos are so important.
Because not only learning these applications and downloading them using them but learning how to build them your scale itself so that's really critical so that's what we're going to do today so we get an idea right so what if we want to add a team let's say let's say i've got a new project here and i know i've got teams i want to add team one i can just click adding a team or maybe i want to add an individual employee maybe i want to add individual staff i can do that here simply adding different staff if they haven't already been added clicking adding right maybe i've got a maybe i got a bunch of employees and i like all these employees as a team and i want to create a set of teams so i've got this this list of employees and i like them i want to create let's just call this.
Electrical team right we can create an electrical team here right and automatically this electrical team will be safe so i call this electrical right if i spelled it right nope i didn't of course let's try that again electrical okay so we've got actually now i want to save this as a team it's automatically going to be saved as a team so the next time that i want to use this team on a new job all i need to do is simply click team and then add in electrical here and then click add and it's going to add all those employees based on that so we can create teams super powerful we want to list out projects by completed right maybe we only want to show those completed projects maybe.
We only want to show projects as a certain type so we can do that or we can list all of them that's going to be using an advanced filter so we've got advanced filters here i've got data mapping here i've got so much i want to get right to it so that kind of gives you an overview a rough overview of some of the skills now we're going to go in to this application in full detail and show you everything that you have you notice that so here we've got some project but notice there's nothing here there's only one here right so why would there not be any additional fields here because we haven't added some skills so as soon as we add it if we try to add the same skill of course it's going to say with the skills already been added if we tried a different skill it'll let us add it and.
So as we add the skills it'll add the skill grades according to for each employee automatically so we've got a lot to cover on this all right so let's get to it before we do first of all we've got project information here right we can save individual project information so certainly we need a basically a table to save that project information that main project information now for every single project here's the main information we've got the product description we've got the number of required staff on there we've got a budget that we can put in if we have a budget so we have a lot of information on it so we got to save that inside a database.
So once we click save we need to save that main information we're going to save that here in something called projects so project 14 we have all that information here's the one i just saved project 11. so that main information gets saved but as you'll notice we have other information that must get saved i've got to know the employees that are on that particular project i've also got to know the skills that are assigned to that specific project so we have two other databases or lists or tables however you want to call them that are associated with this.
Project so each project has its id for example this particular project is project id number 11. so inside opposed to our main one we have two sub trainings sub databases here for this training and that's going to be called project skills list so number 11 has two different skills landscape and masonry notice we have a project id of 11 landscape and then i've got some other information here so we have a minimum skill level a minimum staff the column and the database row.
Here so basically it's this so if we start adding i know i need a skill level in this case of let's say five and i need two staff to have that skill level and i've got another skill level let's say of eight and i need two staff to have that so i've got that covered we've got you know three at 8 or 48 so we're good if i change that to 10 it's going to be a problem we don't have enough skills for that but if i update that and save that project it's going to also save this information here inside the project skills down here we now have those numbers here inside the landscape so that's all saved inside here so that's covers our skills so these skills are tied to a specific project.
I also of course have those employees i've got to save those employees who is assigned to this project right whose skills do i want to show up and i've got that inside here our project
Employs so again going down here to project 11 we see here 11 we have all these employees assigned to this particular project each employee comes with a name a rate and there of course where they are located these are from formulas right so we've got a rate so let's go up here we've got here employee rate how much per hour and their position this information their rate and their position is.Going to come from another table now also another table in this side skills we have to know what skills each one is i need to know that fred is at level 9 on landscaping and mark is at level 1 on landscaping right so to know that that's going to be saved inside the employee information so this is our employee information we have an employee id an employee name their position their hourly rate if they've been assigned to a team right that what team they've been signed by their email this could help for some automation in case we have project we want to email them.
A picture associated with them notice we have the picture a picture file name and then of course we have all the up to 20 different skills here 20 different skills and then we have a total skill rating and i'll be going into that a little bit later on inside this training okay so we're going to go with that so we've got all the information so this particular skills matrix is going to pull information from all of those sources all four of those sources it's going to pull this information from our projects table it's going to pull this information from our project skills table it's going to pull this information from our project employees table it's going to pull this information from our employees table so also we'd be able to increase increment those if we want to.
Update the skills on any i forgot to show you this so we can update that in case we have a change or we can downgrade them we can't go beyond beyond one obviously but so we can change those skills levels just with this or just simply by entering the number that works as well so that's no problem so we've also got conditional formatting also we can show the different positions or we can show the rate just by this it's going to alternate between their position or their hourly rate in case you want to see both of them great so we kind of covered the overview let's take a quick look inside the admin screen and see what that is now we have our list of skills this is fully dynamic so as i change them here they're also going to be able to change inside the employees they're linked.
So if we take a look at this their link we see that this is indirect admin column c and column minus four what do you mean by column minus four okay so we this is a good way to do it so we've got appliances this is located on c4 right so basically whatever's in c4 here we want to put over here inside employees but i don't want to put c4 c5 c6 c7 because that's a you know a little bit long so how do i i know it's c so how do i get 4 5 6 7 right basically it's always going to be c.
So if i know this is column 8 right then what is column 8 minus 4 well that's 4 column nine minus four is five so that's a great way of getting four five six all the way along so if i drag that i know that the existing column is eight and i want to extract the number four i have to subtract four if i know this column is 9 and i subtract 4 i'm going to get 5. so we can use that along with an indirect formula to pull that so again indirect admin column c this won't change right column the.
Current column minus 4 will get us in this case 4 in the next case 2. and then of course it's true because it's going to be a1 style so once we have that all we need to do is just simply drag that all the way over to all of our skills and it's going to automatically update very very easily so basically we're turning from a multiple row multiple rows single column table here into multiple columns here inside employees so that way anytime we make a change here it is automatically going to be changed here on our employees so we've linked up that inside our admin screen i've also got the project statuses and now notice the project status of all stats.
This is going to include two named ranges why is that because when i assign a project staff excuse me when i pri when i assign a project status i want to choose from a drop down list a dynamic drop bonus based on these what do i mean by that here's a product status project status here completed estimated progress right i don't necessarily want to show all statuses though that's not going to be helpful i only want to show however inside this drop down list when i want to list specific projects based on their status i want to show that including all statuses so maybe.
I only want to show completed or maybe i want to show all of them right i can do that with this so if i create two named ranges based on this single column i can do that and the same thing for project types so how do we do that if we look in the formula name manager i'm going to drag this over here and we see that we have status here under the project status here and project status with all so project status is basically a named range a dynamic named range using.
Offset formula zooming in you see e5 then three commas counting a e5 through e23 so we're simply counting all those of the cells now we're gonna do the same thing for project with all except in this case we're gonna start out with row number four not number five what that's going to do is allow us to accomplish the all statuses and we've done the exact same thing for project types we've got two under one project type containing just the types and another one with the all right so we've got two of them here notice the dancing that's around that here it's going to allow us to do that so we have both of them here available to us so we can use a single column one that.
Contains just the data and one that contains all of them all we need is two different named range and then we have employee positions here and this allows us to employ and then we have our levels that we have right you can create your own levels and i've got a few other things here very simple i've got the workday hours this will allow us to calculate the actual skill amount total estimated labor amount right if i know this project's going to take five days and i know that they're working eight hours a day all i need to do is take this hourly rate here and multiply it times the number of hours times the number of days and that's going to get us our total estimated budget and that's just what i've done in here so i've taken this here and i'm giving it a name called workday.
Hours then once inside our skills matrix we take a look at the total estimated labor we see if b6 equals one and why is that important and i'll show you that now basically notice that i only want to show that total estimate if the hourly rates have been listed because i need these hour alerts they would not be listed if i click staff position right then we have the positions right in that case this particular option group notice we have an option group here and we click on the format control and we see that this tied to cell b6 so b6 is either going to display 2 or it's.
Going to display 1. right if i click hourly rate it's going to display 1. if i click staff position it's going to display 2. so i only want this total estimator to calculate when we have displayed the hourly rate if it's displayed stat position we're not going to show anything take a look in this labor's nothing because i'm just showing there i don't want to show that however if the i've clicked on hourly rate this is going to be one and then we can continue on with the formula if it equals one then i want to show that formula w3 which is the number of project days and i want to.
Multiply that times the workday hours and i want to multiply all that times the sum of f9 through f68 and that's basically all should probably make this 99 using enough here right so that is basically all the employee rates the hourly rate so that's all we need to do to simply calculate it if it's anything other than 1 right 2 in this case show nothing so this particular form is going to allow us to create the total estimated labor for a project this really helps us out if we know we've.
Got a budget of 10 000 and then we know we've got a little bit of room in our budget to add an employee so maybe we can do that all we need to just click greg perkins and add him here and that's going to increase it so now we're a little bit closer on the budget so adding them is very very easily but we see greg doesn't really have the skills necessary here so we may want to add more okay great so that's a really really helpful let's get into some of the conditional formatting and then we're going to move into the vba so i'm going to highlight that and go into the home i'm going to drop this down for a moment we're going to be working with conditional formatting and manage rules and we take a look we've got several rules here the first of which is this one here when i select a cell what i want to do is i want it to go green if you've seen that.
In the past here i want to select it and i want it to go green automatically right just like that so how are we going to do that well the best way to do that is just simply use conditional formatting now i need to know both the row and the column of this when i select a cell vba is going to tell us that this is row 10 and this is row and this gives me that and the level column is 9. so when i select that cell we see that our row is 11 and our column is 8. if i know both the row and the column.
We can use conditional formatting to apply that to only the cell that contains in that case that has a row of 11 and a column of eight so that is with conditional formatting so when we manage those rules we see here we've got those using the and inside our formula to determine which cells format we're going to do done b9 will be the row b9 must equal the row and b10 must equal the column and we're going to format with that green here and then when we go in here we see that it's going to be applied to 2 all the way from g9 through z99 that is how we automatically it is vba remember.
It's vba we'll get into that in a little bit vba that places whatever column you select into b9 vba that places whatever row that you selected into b9 and column into b10 okay great i've also got notice that we've got a really cool graded schedule this one here is going to let us know we've got a graded color scale so when we edit this rule we see that we've got a color white all the way to green so this is going to give us our gradient it is a two color scale that we're using our minimum is going to be one right we only want to color cells that are one and our maximum is.
Going to be 10. that is the same one we could just as easily link use a max formula here if we wanted to and we could use or link this to a specific cell here inside our a1 so we could link this to our you know 1 here or whatever we want to do we could link it and make it even more dynamic i've just set it to 1 to make it a little bit easier to see the less formulas so that is an option we also have 10 right so we know the maximum and i want this maximum color to this green if i were to change the colors here it would automatically change inside that so that is going to give us our color code so that way the more skills an employee has in a specific skill or trade the darker the.
Green is going to go and the lighter the white so notice that's how it works relatively simple on this one and then i've got another one here let's take a look at this one and this one is for conditional formatting notice how those rows have alternating colors so basically what i am doing is i'm going to color those even rows of color of white so e9 but i want to make sure that e 9 e 9 the first row in our applied to e 9 here 9 does not equal empty and then the mod of row 2 equals zero this is for even rows and i'm going to give it a format and we're just coloring it white.
I'm giving it a fill of white color that is it so that's all doing why is that because that way all of the other rows are colored this green i want this dark green and if we take a look back inside the conditional formatting here so we have this nice color so see this how it goes to white white but but not here because there's no employees here right so the one of the conditions was two conditions one i wanted it for only four even rows and two the other condition e needed to contain a.
Value that way those two in this condition these aren't met that's why it's given this color now we could make them all colored if we wanted to there's more conditional formatting that i could be doing on this i may do it in fact we may be doing that on our patreon if you like the screen you want to see me add something don't forget we've got a brand new patreon and that what i'm going to do is i'm going to be every single week i'm going to take this application i'm going to build on it but i'm going to build on it based on your suggestions if you want me to add a feature you want me to fix something or maybe you want me to focus on a specific app i'm doing that inside our patreon every week i'm creating a brand new video and a brand new download in five it starts with just a few dollars a month so check out the patreon i'll include the links down below okay so.
We understand the conditional formatting i've got a little bit more conditional formatting for the employees here so when we can go into conditional formatting and we manage rules we see a few of them i also want to know in this case let's take a look at this e9 does not equal empty the e9 is of course the first row in our table notice there's no dollar sign before the nine it's not absolute and that means that it could be for every row that it's applied to if we look under the format again we see that we have some borders here and some fonts here and so that's going to help us out right so let's take a look at this one what do we have i've got this colored border take a look at this like all i want is this dotted border but i only want to show this particular border that's.
It this border only for cells that are don't have there so what does that mean you see that's only for column e 9 through e 94. notice this dotted line here and there's no line here but only here that's how we do it we haven't applied but only for those with the call employees within column as soon as it ends there's no employee there's no dotted line here so we want to create this dynamic table looking using conditional formatting so we've done the same thing for this one here but this one here is going to be again e9 this one's going to give it this lower border lower border.
Here but i want this lower border to be applied for both e and f columns e and f therefore when we look in the applies to we see e9 through f94 so it's going to be applied to both columns this one's just a single column i also want to when i select an employee i want to highlight that row and it when i do that vba is going to take that particular row that we've selected it's going to place it in b8 so we click here we see b8 has that selected it's given that format basically it is a fill of this fill effects we've given it two colors here and also we've given it a font that.
Font is bold and that font is white and that way as soon as we select it it is vba that's going to take that and put that directly inside this row right here b8 b8 is going to take on that as soon as i select it ba is going to take on that it's also going to display this x and allows us to delete employees so if i want to delete employee i can do just that easily removing employees so many features on this so we can do that too and all i can also of course add and delete it but if we want to delete a skill we can delete a skill too very very easily so very very hence hence the word.
Dynamic because it is a very dynamic skills matrix meaning you can change any aspect of it you want of course when you get it you can even update it even more but i've made it pretty dynamic as it is so many features in this okay so we've gone over conditional formatting for both the table we have a similar conditional format here for odd rows here now take a look at this i've got some conditional formatting first of all what i want to do is i want to sum assuming that e9 is not blank i want to sum all the skills so let's go into a project with a lot more things so in this case what i want to do is i want to know who's got the highest skills right in.
This case i want to give them a green flag those have medium or average skills based on all the other skills in the project i want to give it a yellow flag and those with the lowest skills i want to give them a red flag so the first thing we have to do is sum up all the skills using sum and only if of course e9 contains a value that's going to sum them all up once i've summed them up i can then add a conditional formatting and if you'll see here we've got icon sets here so if i want to add an icon all i've done is simply added a conditional formatting using this icon set that's just what i've done inside the conditional formatting so i've added an icon set here and.
It's going to apply to a9 through all the way a99 that's it relatively simple so when we see that we see we can even judge it this is automatic right so it's based on percentages so we can change that if it's of course if it's greater than 67 based on all the weighted average of all those it is going to be higher now we can base them on numbers or we can base them we can use a formula but i've just used percentage those with greater than 33 is going to be inside the yellow flag and less than that is going to be a red flag so we're basing it on third percentages third and third and third so.